Maintain cell reference with OFFSET function when inserting blank rows in Excel

0
votes

I have the following formula, which is populating the values in a column in one worksheet (Day1), using the values in the previous worksheet (Data). The values are sequential; i.e I don;t need to skip any rows.

=OFFSET(Data!$F$6,(ROW(E2))-1,0)

I need to be able to insert numerous blank rows in the Data worksheet, starting at the top of the data, and for the formula to recognize this and populate the cells in Day1 with zeros if necessary. In other words I need the formula in worksheet 2 to always reference say cell F5 in Worksheet 1 even if I insert 100+ rows at the top of the data.

As I potentially have 1000's of rows of data, I also need to autofill the formula in Worksheet 2.

This is the bit I am stuck on. The formula above, will autofill the cells with the correct values, but if I insert say 50 blank rows, it keeps the cell reference (I.e the reference changes from $f$6 to $F$56 for a given cell).

However if I remove the $ absolute reference, the formula will recognize when blank rows are introduced, but is not returning the correct values. I think this is because the 'Zero' reference keeps moving when I drag to autofill, so I end up getting every other value.

Does anyone know if it's possible to combine these two requirements?

I am using Excel Office 365 Business (Version 15.0.5189.1000)

2
excelexcel-formula

2 Answers

1
votes

to always reference say cell F5 in Worksheet 1 

=INDIRECT("Data!F"&(5+ROW(E2)-2),TRUE)

this should do. Please have a try.. ( :

Idea : INDIRECT(<text>,TRUE) will point to where the is defined. for example putting =INDIRECT("A1",TRUE) in the field will be equal to =A1 . In this case we are referring to another sheet where the address is =Data!F5 , so =INDIRECT("Data!F5",TRUE) will do. Please take note that this is also equal to =INDIRECT("Data!"&"F5",TRUE)

but in this case.. you also need the formula to be drag-gable/extendable to the next row.. so "5" cannot be fixed. Thus, we separate the 'fixed' part (Data!F) and the sizable part (5). Since row(e2) returns 2, so (5+ROW(E2)-2) will return 5. so =INDIRECT("Data!F"&(5+ROW(E2)-2),TRUE), means =INDIRECT("Data!F"&(5+2-2),TRUE) or =INDIRECT("Data!F5",TRUE), and equivalent to =Data!F5.

And after dragging it downwards. the (5+ROW(E2)-2) part become (5+ROW(E3)-2), which returns 6 instead of 5. So it effectively refers to =Data!F6.

0
votes

Just a warning, Offset and Indirect are volatile functions. When Excel re-calcs, normal functions only re-calc when the data to which it refers changes. Volatile function re-calc with every change in Excel even if the change is on a different open workbook.

To avoid the unnecessary re-calc we can use INDEX:

=INDEX(Data!F:F,ROW(E2)+3)

It also will not change the reference on the creation of new lines.