Using a Named table in Data Validation

[Bagsy]

Hi all, hope you can help
I have created a named table called Customer_List
I select List in data validation
But when I type =Customer_List into the source box I get the error
“The formula you typed contains an error”
But the named table has dotted lines around it so it looks like its selected it.
Can anybody offer any advice please

 

[ClaireS]

I always use Tables as sources for Data Validation - the advantages of Tables far outweigh the faff required to create the Data Validation.

The solution is simple - just apply a traditional range name to the range you want to use as the source for your Data Validation. Basically, Data Validation does not know about Tables. But it does know about good old-fashioned Range Names. Select the column in the Table that contains the data you want to use in your DV list (not the heading); if you only have a single column, just select the data. Then in the Name box, type a (different) name. I usually start Table names with "tbl" so I would perhaps have a Table tblCustomers, with a column headed "Customers". I could use that name as my range name - or call it DVCustomers to make it clear that it is being used in Data Validation.

Once you have named your range, have a look in the Formulas, Name Manager. You should see that your new range name points to the table and column e.g. =tblCustomers[Customer]. Use this range name as the source for your Data Validation. So (my example)

Data Validation Source: =DVCustomers (normal range name)
Named Range DVCustomers: =tblCustomers[Customer] i.e. the Customer column of a table called tblCustomers.

 

[Bagsy]

Aladin
Apologies for not getting back to you, something came up.
I have created the lists as named ranges, then I converted them to tables. They are now shown as both in the “Name Manager”. They are working both as a dynamic named range and as a dynamic table all seems to be working OK with Vlookup and data validation drop down list.
Thanks for all your assistance much appreciated
Bagsy

 

[ClaireS]

Glad you managed to sort it out, Gary, and thanks for letting us know!