Feb 14 2023 07:07 AM - edited Feb 14 2023 07:08 AM
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
Feb 14 2023 07:56 AM
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.
Feb 14 2023 08:00 AM
Feb 14 2023 08:09 AM
Good to know. It will be great if you share with the community how did you solve.
Feb 14 2023 08:24 AM
Feb 14 2023 08:26 AM
Feb 14 2023 08:27 AM
Feb 14 2023 08:39 AM
=INDIRECT("D2:D"&MATCH(TRUE,NOT(ISTEXT(D2:D500)),0))
An alternative could be this formula.