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.
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
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.
