Trying To Reference The Last Cell In A Column That Contains Data

[gerryger]

I am trying to reference the last cell in a column that contains data. I am using the following formula:

=INDEX(N24:N227,MATCH(REPT("z",255),N24:N227))

The above formula works fine for column "N" because the entire column is empty to begin with. I eventually manually enter data it in.

The problem is when I apply the same formula to column "O" I get errors. The difference between column "N" and column "O" is even though there is no data in column "O" yet, there are formulas in every cell. The above formula does not seem to work if there formulas in the cells. It treats the entire column as being populated.

 

[T. Valko]

I am trying to reference the last cell in a column that contains data. I am using the following formula:

=INDEX(N24:N227,MATCH(REPT("z",255),N24:N227))

The above formula works fine for column "N" because the entire column is empty to begin with. I eventually manually enter data it in.

The problem is when I apply the same formula to column "O" I get errors. The difference between column "N" and column "O" is even though there is no data in column "O" yet, there are formulas in every cell. The above formula does not seem to work if there formulas in the cells. It treats the entire column as being populated.

This will do the same thing as your current formula:

=LOOKUP("zzzzz",N24:N227)

What kind of error are you getting when applying the formula to column O?

The formula is looking for TEXT entries. If none are found then you'll get a #N/A error.

What type of data does the formula in column O return?

 

[gerryger]

text

 

[gerryger]

I'm not seeing any error messages. Just empty cell data which it should not be because there is data in Column "O"

 

[gerryger]

It is the formulas in column "O" that are causing the problem. I put some data in Column "O" Row 228 which did not contain a formula and sure enough that value was properly referenced. How do I do it with formulas in the cells already.

 

[T. Valko]

I'm not seeing any error messages. Just empty cell data which it should not be because there is data in Column "O"

If you have formulas in the range that return formula blanks(""), these are empty TEXT strings and both of the formulas will "see" those formula blanks.

Assuming there is only TEXT in the range...

Try something like this:

=LOOKUP(2,1/(O24:O227<>""),O24:O227)

 

[gerryger]

Hey that worked!!

How do I change that formula to work with numbers?

 

[T. Valko]

Hey that worked!!

How do I change that formula to work with numbers?

That formula will return the last entry in the range, text or numeric, but will ignore formula blanks.

If you want the last numeric value if there might be both text and numbers in the range:

=LOOKUP(1E100,O24:O227)

 

[gerryger]

Nevermind...the formula does return numeric values.

You were correct....my formulas all do return formula blanks("") which what was causing me my problem.

Thanks

 

[T. Valko]

Nevermind...the formula does return numeric values.

You were correct....my formulas all do return formula blanks("") which what was causing me my problem.

Thanks

Good deal. Thanks for the feedback!