How to delete a sheet and insert the same sheet with the same name but prevent referencing errors in formula while doin so?

2
votes

I have a value in cell D8 of Sheet2.

Cell A4 from Sheet1 has a formula that contains the value of cell D8 from Sheet2.

Now, when I delete the entire Sheet2, I get a referencing error in the formula from cell A4 in Sheet1, which is fine.

But when I insert a new sheet with the same sheet name Sheet2 with another new value in cell D8, my formula in cell A4 of Sheet1 still displays a referencing error.

How do I solve this?

For formulas within the same sheet, I can use offset function but I'm stuck when it comes to deleting and re-inserting sheets.

Thanks!

2
excelexcel-formulaexcel-2010ms-officevba

2 Answers

2
votes

Use indirect function in cell A4 of Sheet1 to hard code the address, like this
=INDIRECT("Sheet2!D8")

1
votes

Either

  • Just copy and paste the contents of the new Sheet2 directly over the existing sheet.

or

  • Rename Sheet2 to a new name, for example fred
  • Insert your new Sheet2
  • Press Ctrl & F together..... Replace ....find what: fred, replace with: Sheet2 enter image description here