Link to home
Start Free TrialLog in
Avatar of Gilberto Sanches
Gilberto SanchesFlag for Suriname

asked on

Excel Validation Ignore Blank Cells doesn't work

Hi experts,

In Google Sheets I use validations most of the time, based on a column in another sheet.
In Excel 2016 I want to achieve the same. It does give me a dropdown list to select a value from, for example: the validation source =DB_data!$AD2:$AD5000. It starts from row 2 because that is where the data starts. To make this list dynamic, meaning: if we add more records to this list, it is included automatically in the validation.

However, in Excel, it also shows blank cells in the dropdown validation field.
How can we remove the blank cells from the validation dropdown list?
User generated image
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

If you make the range for validation a table (not specify cells to 500 for adding new items), the table should expand as you add new items to your list.
ASKER CERTIFIED SOLUTION
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I believe that 'Ignore blank' refers to data entry. To create a data validation list you will need to create a new list or named range possibly on another page that doesn't include blanks. The attached workbook does that when any value in column 'D' is changed, added or deleted. The data validation is in the yellow cell.
29169314.xlsm
Avatar of Gilberto Sanches

ASKER

Hey Experts, thank you. It works.
I didn't know that any new records added in the validation source field is automatically included. So I only had to select the data that is currently in the source of the validation list.
In Tom's solution if you don't want "Stuff" to appear in the validation list then change the formula to =$E$3:$E$10
Martin was right, I was a bit sloppy.  Look at the attached.  I have used the Indirect function with the TableName[Column] referencre, or validation list as:

=Indirect("ValidationTable[Stuff]")

Better solution...