Last text value in a range

[CESAR V. ARROYO]

Hello averyone, my dependant validation works as follows:

1. On Cell D1, I can choose an optional-value.
2. Depending on the value of Cell D1, I can choose an optional-value for E2
3. Depending on the value of Cell E2, I can choose an optional-value for F3.

So far so good.

Now, how do I make F4, F5, F6... to reference to the last non-empty cell in Column E.

[JBeaucaire]

This formula will display the text of the last cell in the range with text in it:

=INDEX($E$3:$E$18, MATCH("", $E$3:$E$18, -1))

Perhaps you can use that in your INDIRECT() function.

========
NOTE: That formula will ignore numeric values in the same range. It cannot be used to return the last numeric value. Use this for numbers:

=LOOKUP(9.9E+307, $E$3:$E$18)

========
Though your exercise is Dependent Validation, your question is not. Thus this thread is mis-named in my opinion. "Last text value in a range" perhaps.

[CESAR V. ARROYO]

It works, thanks very much rocket scientist. I'll be more careful when naming my threads.

[JBeaucaire]

Glad to help.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

You actually need to change the thread title to keep the forum database accurate for searches, so you can change that, too, while you're in there.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)