Forum Discussion
data validation allowing invalid values (list, source list have blank cells)
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.
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.
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 rangeThe 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):
