Don't display empty values in Data Validation

[Luxio1997]

Hello everyone.

I have attached the sheet where I have the problem I am about to present to you.

In cell "G1" I entered a "Data Validation" list of the range "$D$2:$D$500".

However, from cell "D9" to cell "D500" the value of these is empty text value (that is: ""); therefore, when I click on the arrow of the data validation drop-down menu, I also see the cells with the value "", and therefore empty (see blue line in the drop-down menu).

What can I do, without using Macros and VBA, to not include all those cells with value "" in the data validation?

Thank you!

Luciano

[#2]

@Luxio1997 

That's only to wait for the update. Excel for web ignores empty cells in data validation list, hope it comes to desktop version in a while.

[#3]

Thanks for the reply.
I solved.

[#4]

@Luxio1997 

Good to know. It will be great if you share with the community how did you solve.

[#5]

I solved the problem by inserting the following formula in the Data Validation:

=OFFSET($D$2;;;COUNTIF($D$2:$D$500;"?*"))

[#6]

Thanks for the reply, your type was great. Have fun Tmas

[#7]

You could also have just used a named list to only include the values for the validation list and this can be extended easily when new values are to be included for validation

[#8]

@Luxio1997 

=INDIRECT("D2:D"&MATCH(TRUE,NOT(ISTEXT(D2:D500)),0))

An alternative could be this formula.

[#9]

You're welcome

[#10]

Thanks for this alternative

[#11]

Nice formula, thank you!