Drop-down list not updating from source list

Highlighted
Occasional Visitor

I have a list of date which is the source for my validation, this list is an excel table, when I add a new data to this list the table automatically expand until this new cell, that's ok; nevertheless in the column (other excel table in an different sheet of the source) where I am doing my data validation, the drop-down list does not update with the new data from the source list. I have made the selection of the source list manually (selecting) and by the upper arrow and none of them have worked out.

 

I figured out that it happens when my source data table is in a different sheet of the table where I have the drop-down list. Does it have to be with any configuration on excel? I hope you can help me. Thanks in advance.

1 Reply
Highlighted

@cataldotestani1990 , Data validation does not play nice with Table references. There are a couple of options to overcome this:

1) Named Range : Create a named range that references the Column - For example if your Table is named "Table1" and the column is "Values", create a Named Range called "DDList" that refers to "=Table1[Values]" and use DDList in the Data Validation

2) Use the INDIRECT function in the Data Validation dialog by typing in "=INDIRECT(
"Table1[Values]") in the dialog box when you select the list option.

 

See attached file with both options.