Forum Discussion

Steve_Livi's avatar
Steve_Livi
Copper Contributor
Sep 14, 2022

Excel inserting unwanted rows

I have a work sheet (TABLE1) with a data connection to another workbook. Data in the table refreshes when the workbook is opened. I have a worksheet (Sheet1) in the same workbook that I use to extract specific data from each row in Table1. A typical formula in Sheet 1 is

in A14 =IF(ISBLANK(Table1!A2),"",Table1!D2) and

in A15 =IF(ISBLANK(Table1!A3),"",Table1!D3)

I have used the drag handle to fill the data in 100 cells (A15:A115) sequentially 

When a new row is added to Table 1 however, each of the cell references has 1 added. In A15 the formula thus reads =IF(ISBLANK(Table1!A4),"",Table1!D4) and the new row is not displayed.

 

I am sure there is a simple way to stop this from happening. I would love to know!

 

Excel
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 18, 2022

    Steve_Livi 

    You must not use direct references. These are moved with the insertion of cells. I would advise you to use the OFFSET() function.

    You only need one formula in cell A14:

    =IF(ISBLANK(OFFSET(Table1!A2,0,0,100)),"",OFFSET(Table1!A2,0,3,100))

     

    Good luck!

     

Share

Resources