Prevent Excel from deleting reference in formula w

Question

I have an Excel workbook with worksheets Sheet1 and Sheet2, where Sheet2 has formulas that refer to values in Sheet1 as follows:

+-----------+------------+
|  address  |   value    |
+-----------+------------+
| Sheet1!A1 | 1          |
| Sheet1!B1 | 3          |
| Sheet1!C1 | 5          |
| Sheet2!A1 | =Sheet1!A1 |
| Sheet2!B1 | =Sheet1!B1 |
| Sheet2!C1 | =Sheet1!C1 |
+-----------+------------+

If I delete row 1 from Sheet1, using the following C# interop:

thisRange["a1"].EntireRow.Delete()

then all the formulas get set to errors, e.g. =Sheet1!#REF!.

Is there a way to preserve the existing references in the formulas once the row is deleted? I suppose I could copy all the formulas into an array and then recopy them into the sheet, but I'd ideally like to have them not disappear in the first place.

Answer 1

What you want to do is use INDIRECT references in Sheet2. These stay constant and do not complain if a cell or row is moved or deleted, they'll reliably stay pointing to the same reference.

So in your example, your Sheet2 values become:

+-----------+------------------------+
|  address  |          value         |
+-----------+------------------------+
| Sheet2!A1 | =INDIRECT("Sheet1!A1") |
| Sheet2!B1 | =INDIRECT("Sheet1!B1") |
| Sheet2!C1 | =INDIRECT("Sheet1!C1") |
+-----------+------------------------+

I'll leave it to the reader to play with making the reference text more dynamic (eg "Sheet1!A"&ROW()). See https://superuser.com/questions/316239/how-to-get-the-current-column-name-in-excel

Answer 2

I do not know of a setting that allows this to not happen.

With that said, the work around which I usually use for similar scenarios is to make the formulas no longer formulas during the delete command. If you add a text letter before the formulas in Sheet2, complete the delete function, remove text letter before formulas in Sheet2 then the functions will still work

=Sheet1!A1

a=Sheet1!A1

=Sheet1!A1

This could be done with a Find-Replace command if you are manually deleting the row or if programming a function I would look at a For Each Cell in range to concatenate an "a" before the formula then trim it back out by using length of string -1.