Using a Named table in Data Validation

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
467
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top