Excel Links and Deleted and Inserted Rows

How to avoid #REF! errors

When linking to another sheet, a deleted row can often cause a #REF! error. You can use the INDEX function to avoid these errors.


Let’s says we have a sheet called Input, where a user pastes in a downloaded report that will be used by the system to produce a report. We want to link to column A to validate a Department code in the Input sheet and make sure it is in a table we have in the file. See image below.

Cell linking
If we use a normal cell link then when a row is deleted in the Input sheet it will cause a #REF error in our Validation sheet. The INDEX function provides a simply way to avoid #REF errors.
The formula to link to column A in the Input sheet is

=INDEX(Input!A:A,ROW())

This formula will extract the value from the same row from column A in the Input sheet. This formula has been entered in cell A1 of the Validation sheet and copied down. In column C of the Validation sheet there is a normal cell link. From column B we can see that code 1241 is not in the Tables sheet.

We can compare what happens when we delete row 5 in the Input sheet. See image below.

Cell linking

The normal link in column C displays the #REF! error, whilst the INDEX function in column A will handle the row deletion and still be linked to each cell in column A of  the Input sheet.

Cell linking

The other advantage with this technique is if a row is inserted in the Input sheet. The normal link will not include any inserted rows, but the INDEX function will – see image below where a row was inserted above row 8 in the Input sheet. The Dept code 1237 was entered with a value 9999.

Cell linking

If column A is deleted in the input sheet then a #REF! error will be displayed in column A of the Validation sheet. To avoid a column deletion you could use a formula like

=INDEX(Input!1:1048576,ROW(),COLUMN())

This formula will link to the same cell in the Input sheet and will never show the #REF! error as long as the Input sheet exists.

Please note: I reserve the right to delete comments that are offensive or off-topic.