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.

Leave a Reply

Your email address will not be published. Required fields are marked *

4 thoughts on “Excel Links and Deleted and Inserted Rows

  1. This was so very helpful for me today, thank you. INDEX is going to come in handy for me in the future as well. I look forward to exploring your website some more, looks like there will be some Excel gold in here!

  2. Would you be able to help me figure syntax for v-look-up & index code that I need to apply to 1 master workbook linked to 20 identical workbooks? I’m currently using the wrong code I think. Other than it works perfectly inserting rows to my linked workbook. Just when I deleted rows I end up with #ref error on the linked workbook. I also don’t know how to call all 20. I only know the syntax to connect 1 workbook.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Const wb1Name As String = “Workbook1” ‘change
    Const wb2Name As String = “Workbook2” ‘change
    Const ws1Name As String = “Sheet1” ‘on wbk1
    Const ws2Name As String = “Sheet1” ‘ on wbk2
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = Workbooks(wb1Name)
    Set wb2 = Workbooks(wb2Name)
    If Not Intersect(Target, wb1.Sheets(ws1Name).Columns(1)) Is Nothing Then
    x = Target.Row
    wb1.Sheets(ws1Name).Cells(x, 1).EntireRow.Insert , shift:=xlUp
    wb2.Sheets(ws2Name).Cells(x, 1).EntireRow.Insert , shift:=xlUp
    wb2.Sheets(ws2Name).Range(“A” & x – 1 & “:C” & x – 1).Copy
    wb2.Sheets(ws2Name).Cells(x, 1).PasteSpecial xlFormulas
    Application.CutCopyMode = False
    End If
    End Sub

    • The code above is about inserting rows and copying formula when you double click.

      It is nothing about VLOOKUP or INDEX

      Can you send me an example sheet?

      I will send you a separate email.

      Regards

      Neale