Data Validation allows only part of list

chs4

New Member
Joined
Mar 15, 2009
Messages
23
Hi All,
I have a worksheet in which I have a data validation that allows values from the "names" list. Originally the "names" list was static and all was well. Then I had to make it dynamic and the data validation subsequently began to show only the first 17 names (out of ~500). When I add new names to the top of the list, it still only shows the first 17 names. The list is defined correctly because the complete list is highlighted when I click "names" from the dropdown box beside the formula bar (if that makes any sense). Does anyone know why it's doing that and how to fix it?
Thanks.
chs4
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Do you have the list referencing the range name or is it referencing the initial 17 cells?
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Hi and welcome,

If your name is defined dynamically then it won't be selectable from the dropdown beside the formula bar.

Have you checked the name of your dynamic range to ensure it is the same name used in the data validation list?

If so, you may have a Workbook level name and a Worksheet level name with the same name? You can see this in the Define Names box.
 

chs4

New Member
Joined
Mar 15, 2009
Messages
23
Hi Von Pookie and Yard. Sorry for the long delay in responding. I've been traveling a bit. The problem, as Yard pointed out was that there were two lists named "names," one at the workbook level and one at the worksheet level. I didn't realize there was a difference. Thanks to both of you for your responses.
-chs4
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

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