Excel - Offset to last non-blank cell

0
votes

From my research, when a bunch of cells are merged, you can only reference the first row and first column of the merged cells. EG. if A1:A3 are merged, then I can only access the data using A1 only, and A2 and A3 returns 0.

Now let's say I have a column B that has a formula that calculates based on values in column A. If I drag this formula down, then B2 and B3 will end up using value of 0, when they should be using value in A1.

Effectively, what i want to do is "if the cell in column A (of this row) is blank, then use the last non-blank value going upwards".

I know this will need to combine a couple of formulas, but I can't figure out how to create this. For a start, I can use the Offset function to "go up", but the difficult part here is how to find the previous non-blank cell?

I also tried combing OFFSET with COUNTA (see https://www.exceltip.com/other-qa-formulas/get-the-value-of-the-last-non-blank-cell-in-a-column-in-microsoft-excel.html), but this doesn't work if this occurs multiple times.

2
excelexcel-formula

2 Answers

2
votes

Easiest way is to use a helper column:

enter image description here

In B2 write

=IF(NOT(ISBLANK(A2)),0,B1+1)

and in C2 write

=OFFSET(A2,-B2,0)

Edit: actually... the solution without helper column is even easier! Write in B2:

=IF(ISBLANK(A2),B1,A2)

enter image description here

1
votes

To avoid the helper column, you can use the INDEX + AGGREGATE functions:

=INDEX($A$1:A1,AGGREGATE(14,6,($A$1:A1<>"")*ROW($A$1:A1),1))

enter image description here