Creating a data validation list

[maacmaac]

I am trying to set the below table up as a validation list. I am having issues with putting it in as list as it creates a new line every time a “,” (comma) occurs. I know I can use the custom and pull the list from a table, but I can’t use this option (the reason I can’t use the table, if interested, is because the single sheet with the validation contains about ~35,000 line items that are being split up into ~200 different sheets. Each sheet is then sent as a separate email…the problem with using the table is that it would on a different spreadsheet to begin with. The email only sends one sheet. As such, it would no longer be able to reference the table).

Any help is appreciated. Thank you.

A - Less than $25,000
B - Between $25,000 and $49,999
C - Between $50,000 and $99,999
D - Between $100,000 and $249,999
E - Between $250,000 and $499,999
F - Between $500,000 and $999,999
G - Over $1,000,000

[martindwilson]

what are you trying to do?
is this in one cell?

[Palmetto]

How about using common abbreviations for monetary units?

A - Less than $25K
B - Greater than $25K and Less than $50K
C - Greater than $50K and less than $100K
D - Greater than $100K and less than $250K
E - Greater than $250K and less than $500K
F - Greater than $500K and less than $1M
G - Over $1M

[maacmaac]

The validation is not for a single cell. The validation is needed for all cells in column C. Also, I can't change the structure of the drop down list. The reason I can't change the structure is because I am splitting the spreadsheet into ~200 sheets and sending out to the sales force. They then fill in the missing info, using the validation choices, and send the data to IT. IT then takes the data and loads it into a database. IT needs the data to be in the same format, otherwise they cannot automate it. HTH. Thanks for the comments.

[martindwilson]

in say "Between $25,000 and $49,999"
replace each comma with char(0130) which looks like a , but isnt the same as the one used as list seperator
so where you have $25000 put cursor where you want the , hold down alt key and on the number pad key 0130
see attached cell a1 for example

[maacmaac]

Excellent. Exactly what I needed. Thanks