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

koyama

New Member
Joined
Aug 9, 2008
Messages
5
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:

Datavalidering-08102008-040856AM.png


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

Microsoft_Excel_-_Mappe2-08102008-040918AM.png


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

Microsoft_Excel_-_pizzaxlsx-08102008-040954AM.png


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

Datavalidering-08102008-041003AM.png


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?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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" :mad: ... 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
 
Upvote 0
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.
 
Upvote 0
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:

Nyt_navn-08122008-010739AM.png


Datavalidering-08122008-010605AM.png


Microsoft_Excel_-_Mappe1-08122008-010545AM.png


Hurray! Named ranges is the way to go!
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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