Using a Named table in Data Validation

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
377
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Bagsy

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Active Member
Joined
Feb 26, 2005
Messages
377
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The Sheet is called "Lists"
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Active Member
Joined
Feb 26, 2005
Messages
377
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

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
Top