Excel 2007 : Data validation issue

**fastbiker** · 01-29-2010, 08:52 PM

I have a spreadsheet I use that has data validation for cells with currency amounts. I use the semicolon as a list separator and have it setup in control panel that way.

The spreadsheet is a template, when I open it, it functions fine the dropdown boxes work. However when I go to save it, and open it again it somehow has replaced all the commas with semicolons and the dropdown boxes do not work correctly.

This is a very recent occurrence, it used to work fine. Any ideas out there?

Running on windows server 2000 and excel 2007, also does the same thing on windows xp machine I have. Makes no difference if I save it as macro enabled, template, etc

**Ron Coderre** · 01-29-2010, 10:19 PM

I'm a little surprised it ever worked correctly for you.
How would Excel know which comma is a list delimiter and which is part of a number in
this list from one of your DV cells?:
Rejected,$15,000 person $30,000 accident, $25,000 person $50,000 accident, $50,000 person $100,000 accident, $100,000 person $300,000 accident,$250,000 person $500,000 accident, $35,000 CSL, $100,000 CSL, $300,000 CSL, $500,000 CSL

I think, for the least amount of confusion and maintenance:
• add a sheet for lists
• Put each list in a separate column on that sheet
• Assign a range name to each list (DV can't reference a range on other sheets, unless they're named)
• Edit each DV cell to reference the named lists

Example:
• add a sheet named: Lists
• Put this list in cells A1:A9 on that sheet:
$15,000 person/$30,000 accident
$25,000 person/$50,000 accident
$50,000 person/$100,000 accident
$100,000 person/ $300,000 accident
$250,000 person/$500,000 accident
$35,000 CSL
$100,000 CSL
$300,000 CSL
$500,000 CSL

• Name that range: listBodInjLiab

• On the Quote sheet, cell B73:
Data_Validation.Data_Validation...Allow: List...Source: =listBodInjLiab

Repeat for each of the other lists.
When done...hide the Lists sheet.

Does that help?

**fastbiker** · 02-01-2010, 07:10 PM

Yes I don't know how that happened- there used to be semicolons in the data validation field and somehow magically in the past few weeks it has changed!

Thanks for the tip on naming ranges- never knew I could use that for Data validation!