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