data validation allowing invalid values (list, source list have blank cells)

[davidpaiva]

Hello!
I am trying to use data validation using a named range (list validation).
The source range is a large range (5000 rows) and most of those rows are blank.
But, the validation is allowing invalid values (values outside the list). If I force a value on the whole range (like a - instead of empty cells) it will work as it should and block invalid values, so I know for sure it's related to the empty cells.
I tried to turn off ignore blank, but the issue persists.
I need the list with the empty cells because this is a list that will increase with time, so I can't fix the list size.

[#2]

@davidpaiva

Let's say the non-blank part of the source range is currently M2:M100, with a header in M1. So M101 etc. are blank.

Set the source of the data validation to =$M$2:$M$100

Select M1:M100.

On the Insert tab of the ribbon, click Table.

Make sure that the check box "My table has headers" is ticked, then click OK.

Converting the range to a table makes it dynamic: as you add new rows to the table, Excel will automatically expand the source range. So the source range will grow, but not include empty cells.

[#3]

I tried to do like this, but it's not auto updating the list.
it's also not accepting Table1 as the source for validation
But when I added a new line to the table, the table adjusted.
Also, the range itself is a SORT(UNIQUE()) range (it's based on another range

[#4]

@davidpaiva 

The Source box of the data validation dialog does not accept a table reference. Simply select the current range of the table.

Here is an example:

This is the data validation dialog:

After adding two rows to the table, the new items are displayed in the drop-down:

The source has automatically been expanded (I did not have to edit it myself):

[#5]

Maybe it's because the table is in other sheet?
this is what I have as the source:
=DESCRIÇÕES!$A$2:$A$366
But this is the table range after I added one line, the table in fact adjusted the range but Excel 365 it's not adjusting the range. (also, looking in your interface, possibly it's some issue related to 365?

(For the moment, I am doing a workaround, adding "-" to all blank lines in the range. It's not the best solution but while we are trying to understand what is happening)

[#6]

@davidpaiva 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?