Find first empty cell with a formula that adjusts references when inserting columns?

Hi!

I hope somebody smart out there can help me solve this problem.

Column A contains a list of information; numbers and text, directly written into the cells or resulting from formulas. There are some blanks in between, but they stem from formulas that result in ="".

In a nearby cell, say B2, I want to have a formula that returns the row number of the first empty cell found at the bottom of the list in column A, not counting ="" as empty.

Furthermore, it is important that such a formula adjusts its references in a way that makes it possible to insert new columns before or in-between the column containing the list and the column containing the formula, e.g. column A and B, so that if the list of information now resides in column B and the formula resides in cell D2, it still gives the correct answer.

I have written a formula that returns the first empty cell. The problem is, I am not able to make it adjust the references when inserting new columns.

The formula is a MATCH/INDEX/INDIRECT combination. The INDIRECT function uses the R1C1 possibility, because I thought that would give an opportunity to make the formula adjust the references when inserting columns. I thought it was possible to replace the column numbers in a INDIRECT("R"&1&"C"&1&":"&"R"&10000&"C"&1;FALSE) range with the COLUMN($A:$A) function, but it doesn't seem to work.

Here are two expressions, the first of whom works with regards to returning the first empty cell, but fails with regards to being flexible. The second returns #N/A.

Please Login or Register  to view this content.

I really appreciate it if someone could help me with this. I just don't understand why the second expression fails.

PS 1: See attached file for examples.
PS 2: I am not interested in a VBA/macro solution to this problem.

Best regards,
Marbleking