Q: Excel Data Validation list of concrete percentages not working - bug? I have a cell that needs to hold a user-editable percentage, but it may only contain a multiple of 10% in the range 0%-100%.
My logical solution was to just apply data validation on the cell, and specify it as "List", with the list being "0%,10%,...,100%".  This way the users can both type in the number as well as select it from the in-cell drop down list.
However, when I consecutively save & close the workbook and then re-open it again, only the drop down entry works.  All attempts to manually enter a percentage lead to Excel disallowing it; it just doesn't recognize the input as valid anymore.  Changing the data validation definition a bit and back again makes it all work as expected, but only up to the next re-open of the workbook.
A workaround I found is to specify the valid percentages in a named cell range, and refer to that range in the validation rule; this keeps working even across re-opens of the workbook.
I wonder though if this is truly an Excel 2010 bug, or if it's something particular to my Excel installation?  If anyone could test this (especially on other Excel versions), I'd be very grateful.
The steps to reproduce it:


*

*Start a new workbook

*Select cell A1 and format it as a percentage

*Apply data validation, and

*Set 'allow' to "List"

*Set 'source' to "0%,10%,20%" (without the quotes)

*Keep 'ignore blanks' and 'in-cell dropdown' checked

*Verify you can both manually and via the drop-drown list enter one of these choices (you can enter it either as 10% and 0.1)

*Save the workbook, close it, and re-open it

*Try to enter another percentage, both via the drop-down list and manual entry


Result: manual entry triggers the data validation error prompt...
If this is a genuine bug, I'll post the above mentioned solution as a proper solution.  If you have a better one, that is of course welcome as well!
Addition
The workaround I found comes down to:


*

*In the cells C1 to C3 enter the values 0%, 10% and 20% respectively.

*Select the range C1 to C3, and name it e.g. "allowedPercentages"

*In the data validation for cell A1, set the 'source' to "=allowedPercentages" (without the quotes)

