Delete Row causes #Ref w/absolute cell ref

[dgarris]

I have two worksheets. One contains data and the other uses formulas to perform certain actions based on what is in the cells on the other sheet. However, even though the formulas use absolute cell references, i.e. Sheet1!$A$1, when a row is deleted, the formula becomes invalid with a #REF error.

Example:
Sheet 1
a1 = 1
a2 = 2
a3 = 3

Sheet 2
a1 contains =Sheet1!$A$1
a2 contains =Sheet1!$A$2
a3 contains =Sheet1!$A$3

If I delete row 2 of Sheet 1, a #REF error occurs with the cell a2 on Sheet 2.

I would expect Sheet 2 to have the following values after deletion:
a1 = 1
a2 = 3
a3 = ""

but instead, a2 gets a reference error and a3 gets changed to =Sheet1!$A$2.

How can I get Excel to leave Sheet 2 cell content alone?

[Bryan Hessey]

Hi,
instead of =Sheet1!$A$3 use

=Offset(Sheet1!$A$1,2,0)

etc

hth
---

[dgarris]

Thanks for the help. The Offset function worked for me. I had to use the ROW() function as the row offset as well to get a complete solution. Again, thanks!

[Bryan Hessey]

Thanks for the help. The Offset function worked for me. I had to use the ROW() function as the row offset as well to get a complete solution. Again, thanks!

good to see a success, the Column() is also useful if you are using the Offset across the row as well as down the columns.

Thanks for the feedback.
---

[frits2012]

I have a formula that adds the values of two cells of the previous ROw, unfortunately, when trying offset, or Indirect, we always have a REF error when deleting 1 or more rows. Is there any way to fix this issue?

here is the code...

=SUM(A1:B1)

After deleting i get the error #REF!

[Cutter]

Hello frits2012, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.