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

 

[Aladin Akyurek]

What is the reference of Customer_List, i.e. where does it point to?

 

[Bagsy]

Thanks for getting back to me Aladin
The list at present extends from “AY3” to “AY9” with the header in “AY3”, but this list will grow as I enter more names

 

[Aladin Akyurek]

Thanks for getting back to me Aladin
The list at present extends from “AY3” to “AY9” with the header in “AY3”, but this list will grow as I enter more names

What is the name of the sheet housing this list?

 

[Bagsy]

The Sheet is called "Lists"

 

[Aladin Akyurek]

The Sheet is called "Lists"

Define CusList in the Name Manager as referring to:

=Lists!$AY$2:INDEX(Lists!$AY:$AY,MATCH(REPT("z",255),Lists!$AY:$AY))

Now you should be able to use CusList in Source of Data Validation as List.

 

[Bagsy]

Thanks Aladin
When I go to name manager and edit, the “Refers to” Box is greyed out and will not alow me to enter anything, then if I cancel to get out it gives me an error “The Name you entered is not Valid”
I changed the name to “Custlist” as you suggested, the “refer to” box is still greyed out and if I cancel to get out I get the same name is not valid error.
Very Strange.
I have two named tables on one sheet, would that cause this problem?

 

[Aladin Akyurek]

Thanks Aladin
When I go to name manager and edit, the “Refers to” Box is greyed out and will not alow me to enter anything, then if I cancel to get out it gives me an error “The Name you entered is not Valid”
I changed the name to “Custlist” as you suggested, the “refer to” box is still greyed out and if I cancel to get out I get the same name is not valid error.
Very Strange.
I have two named tables on one sheet, would that cause this problem?

Try the suggestion in a clean workbook in order to verify that Excel itself is not the problem.

 

[Bagsy]

Thanks Aladin
I think the problem may have been because I created a named table rather than range, what I have tried is as follows.
1 Created a named range AY3 – AY9
I then converted it to a named table (insert table and then named it)
I then tried my data validation and entered =Customer_List into the source box and it worked perfectly.
Thanks very much for your time and effort
Gary

 

[Aladin Akyurek]

Gary,

You want the the customer list to be dynamic, right? If so, try the suggestion I made. By the way, while dynamic a Table does not work as list source in data validation.