Excel inserting unwanted rows

Copper Contributor

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!

 

1 Reply

@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!