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:



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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,118
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
 

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
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

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

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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

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

Forum statistics

Threads
1,081,793
Messages
5,361,316
Members
400,625
Latest member
Asraful Alam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top