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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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