Why does comma reverts to semicolon in data validation list on file reopen?

[koyama]

This is frustrating me! I want to create a data validation list where some of the entries contain a comma. So I do like this:

which gives me what I want, namely entries containing commas:

I save the file and everything is fine... until I reopen it again. Then I see this:

and when I go back and check in the data validation dialog I see that Excel 2007 has converted my commas to semicolons:

What on earth is going on? I searched the forums to find an answer, but without any luck. I don't know if this is also happening with Excel 2003?

 

[DonkeyOte]

Welcome to the Board.

why not use a named range -- ie instead of hardwiring the options into Validation itself create a named range called "Pizza" with your validation options in cells, then setup validation to Custom List with Source: =Pizza

XL07 is probably being "helpful" ... it shouldn't try to split your cell values when the file re-opens so the named range is a good way to go

(By using a named range you should find you can store your list values on any sheet and use the validation throughout your workbook... I don't have XL07 so can't test unfortunately).

HTH

 

[dafan]

It probably has something to do with your language version too. European versions use ; where English versions use , (ie in formulas).

I couldn't recreate your error (English version), but a Named range is always a better option when using Data validation as you are easy able to add or remove items from the list.

 

[koyama]

why not use a named range -- ie instead of hardwiring the options into Validation itself create a named range called "Pizza" with your validation options in cells, then setup validation to Custom List with Source: =Pizza

Thank you very much for the warm welcome and for your valuable feedback. This is extremely helpful to me as I am very new to Excel. I did what you said and I am now using a named range for the list options “Pizza, big”, “Pizza, small” and it works great.

Only drawback is that it now looks like I have to put the values “Pizza, big”, “Pizza, small” directly in the sheet somewhere. I think this may distract the users who have to fill in the spreadsheet, but I will try to put the items somewhere out of view e.g. Z1:Z2.

I posted the screen shots at the bottom of this post in case someone needs to solve a similar problem.

It probably has something to do with your language version too. European versions use ; where English versions use , (ie in formulas).

I couldn't recreate your error (English version), but a Named range is always a better option when using Data validation as you are easy able to add or remove items from the list.

Thank you very much for taking the time to check up on my problem. Yes, you are correct, I am using a Danish version of Excel 2007. In the future I will have to check the files in multiple language versions of Excel. This is scary!

Thank you once again, lasw10 and dafan, for your prompt replies. I am really happy I found this great forum!

Updated screen shots in case this may help others:

Hurray! Named ranges is the way to go!

 

[sous2817]

Koyama,

You can put the named ranges on a different sheet all together or change their font color to match the background (typically white), or even put them in an out of the way row and / or column and hide it. This should keep the user from being distracted.

 

[koyama]

You can put the named ranges on a different sheet all together or change their font color to match the background (typically white), or even put them in an out of the way row and / or column and hide it. This should keep the user from being distracted.

Thank you so much for your helpful advice. I would never have got such ideas myself. I played around with your suggestions, and they all seemed to work.

At the end, I used your first suggestion putting the named range on a separate sheet and then hiding it afterwards. For some reason I liked this the most because it seemed like cheating the least (I know this sounds crazy). But I am now very satisfied with the solution and everything is great.

I am so glad that you have helped me out with this problem and I want to thank you all once again. It really mean a lot to hear expert opinions. You all rock!!!