Excel Validation Ignore Blank Cells doesn't work

Gilberto Sanches
Gilberto Sanches used Ask the Experts™
on
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?
Validation dropdown list shows blank cells in Excel 2016
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tom FarrarConsultant

Commented:
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.
Consultant
Commented:
Here is an example.  You can add items to the table on the right and the dropdown will expand accordingly.
EE.xlsx
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
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.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Tom FarrarConsultant

Commented:
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...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial