Forum Discussion
Don't display empty values in Data Validation
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 cel...
Thanks for the reply.
I solved.
I solved.
Good to know. It will be great if you share with the community how did you solve.
- I solved the problem by inserting the following formula in the Data Validation:
=OFFSET($D$2;;;COUNTIF($D$2:$D$500;"?*"))=INDIRECT("D2:D"&MATCH(TRUE,NOT(ISTEXT(D2:D500)),0))
An alternative could be this formula.
- Nice formula, thank you!
- 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
- Thanks for this alternative
