Data Validation lists problem

MrsLJCE

New Member
Joined
May 20, 2011
Messages
2
So, I am creating a calculator. I have a drop down list in one cell and the next cell gives me a drop down list based on the previous cell. My question is, why do the lists not recognize the names if there is a space in it?

EX:

State
Massachusetts
New York

Massachusetts
Boston
Andover

New York
Rochester
Buffalo

If I chose New York it won't read the NY list, if I put an underscore where the space is it will work, but I can't always have an underscore....Help...please?!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi, welcome to the board.

I can't replicate your problem, New York works fine for me.
How are you looking up the entries in the 2nd validation list ?
 
Upvote 0
Welcome to the MrExcel board!

If you are trying to use named ranges, then you cannot use spaces. From the Excel help
The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Note You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators; for example, Sales_Tax or First.Quarter.
Name length A name can contain up to 255 characters.
Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.
 
Upvote 0
Is there another way to do it so that there can be spaces? can you use if statements for data validation lists?

I appreciate the help! I am slowly learning all that excel can do!
 
Upvote 0
Well I did it by creating a list for the possible values in the second validation list as an area on the worksheet, and making the values within that area depend on the choice made in the first validation list.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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