Referring to first non-empty cell UP in the same column

[Sheen]

Apologies for creating yet another thread.

Is there a way to reference the first NON-EMPTY cell UP in the same column as the cell where we enter the formula?
Example:

A1
A2 2
A3
A4 formula referring to A2
A5

[acknak]

First, why make it more complicated than a simple reference to A2? A bare reference like that will be processed internally as "the cell two rows above in the same column".

If you need something more complicated, I've used something like this for a similar situation:

=MAX(ROW(A1:A3)*(A1:A3<>""))

entered as an array formula (finish with Ctrl+Alt+Enter)

This calculates the row of the non-empty cell that's farthest down, MAX(ROW(...)), in the range of cells above the formula. I think that amounts to the same cell as your description.

[Sheen]

Thank you, that's a nice trick.
How can I copy it down the column, so that A3 changes to A4, A5, A6 and so on? I tried INDIRECT("$A$1:$A" & row()-1) but to no avail

[Sheen]

First, why make it more complicated than a simple reference to A2? A bare reference like that will be processed internally as "the cell two rows above in the same column".

If you need something more complicated, I've used something like this for a similar situation:

=MAX(ROW(A1:A3)*(A1:A3<>""))

I can't get this formula to work with OFFSET function to specify the range. I have:
=MAX(ROW(OFFSET(A1;0;0;2;1) * (OFFSET(A1;0;0;2;1)<>"")))

and it returns error 504. What is wrong with this formula?

[Lupp]

-1- You try to multiply the range returned by OFFSET with something before you apply ROW(). Check the parentheses!
-2- I cannot see much sense in the constant height of 2 rows for your calculated ranges.
-3- The way you start offset at A1 will most likely not prove.
-3a- Copying the formula elsewhere may have a result you most likely don't want. Try! Better use absolute row address.
-3b- Deleting the first row will cause a #REF! error.
Also: -4- I still don't feel sure in what sense you wanted to use "empty cell": "blank cell", or "cell being either blank or containing a formula returning the empty text".

I also cannot see much sense in the proceeding at all. Would you mind to explain the task behind your attempts to solve it. An experienced user might be able to suggest a basically better solution then.
Also: Why didn't you answer the question in return by "acknak" instead of shifting to the next question? You should respect experienced contributors.

-5- For what reason do you want to use OFFSET at all? Ranges with absolute row address for the top position and relative address for the bottom position should do.

Assuming the OFFSET usage is unavoidable for some hiddeen reason, and
assuming the "empty" was expcted to be read in the second sense mentioned above, you should get your result (now in row 20) by:

Code: Select all

=MAX(ROW(OFFSET(INDIRECT("a1");0;0;ROW(A20)-1;1))*(OFFSET(INDIRECT("a1");0;0;ROW(A20)-1;1)<>""))

"empty" In the first sense:

Code: Select all

=MAX(ROW(OFFSET(INDIRECT("a1");0;0;ROW(A20)-1;1))*NOT(ISBLANK(OFFSET(INDIRECT("a1");0;0;ROW(A20)-1;1))))

The tiny complications are accepted to avoid some of the problems listed above under 1 througth 3.