Excel problem for beginners REF Error When referenced Row is deleted or Added

Question

I have data in column A which is copied in another random location. The copied data could also be in column B-Z, AA... Etc, Or alternatively could be on a new sheet by setting the column =Sheet1!A1 for example.

In the attached image I initially had a,b,c...through to f. In this example,data is copied in the same column just further down by entering at A10 =A1 and copying down as required.

When what was A5 is deleted as a row to remove 'e' you can see that A14 now has the #REF! Error. This is strange to me because although the row is deleted the data has shifted up one. so A14=A5 which previously returned 'e' should now return 'f' but instead returns the error. Initially I thought that this is because the data is in the same column but the same error is returned if on another sheet for example If I set A1=Sheet1!A1 and then delete a row on sheet1.

If you drag down the copying formula again the error is corrected But surely this can be made to happen automatically? Especially if dealing with large amounts of data.

Surprisingly this error doesn't seem to have an obvious solution posted online. I wanted to create this post for anyone to suggest feasible solutions for those starting out with excel (including myself).

Answer 1

You'll run into this problem if you employ a broad formula like =A5. Internally, Excel is not designed to manage this.

The OFFSET function is the easiest approach to avoid this mistake. Because this function is dynamic, it will update as new or deleted cells are added or removed.

I hope this solves your issue.