Problem: For cells which have data validation: Users type a capital 'A' in a cell and Excel is changing the letter to lower case 'a' to match the validation cell value. This is a brand new behavior, affecting only some PCs.
Details: I created a spreadsheet 20 years ago which a group of staff have been using without problem all this time. The spreadsheet has cells with data validation - the type of validation with in-cell drop down options linked to a named range of cells in another sheet in the same workbook/file. The list of validation options are single letters: a, b, c (all lower case). However, in the main spreadsheet, staff enter either a capital letter or a lower case letter, depending on the need.
For example, staff may enter a lower case 'a' in some cells and an upper case 'A' in other cells. The different cases have different meanings. So, staff need to be able to enter both upper and lower cases, while the list of valid values in the other sheet just lists the lower case options.
Previously (ie, for the last 20 years), Excel kept the letter in whatever case the user typed in the main sheet, regardless of the case in the list of validation cells. This has worked very well for our business needs.
Starting last week, all of a sudden, Excel converts the letter the user enters to the case that is on the named range/sheet containing the list of validation cells. Ie, if the validation list has an lower case 'a', then if the user types an upper case 'A' on the main sheet, Excel converts the letter to a lower case 'A'. I tried changing the case on in the validation list to an upper case--but then Excel changes case in reverse. If the validation list contains an upper case letter, then when I enter a lower case letter in the main sheet, excel converts the letter to an upper case. Argh. In other words, Excel no longer keeps the case that the user entered. Excel only allows the case to be used in the main sheet which matches the case in the validation list.
Curious Inconsistency: This new problem only affects some PCs. We can open a file on one PC and the case problem occurs. Then we can open the same file on another PC and the case problem does not occur. When this problem was brought to my attention, I initially could not replicate the case problem. Then about an hour later, I did have the case problem and now I have the case problem all the time - even though I tried all the regular steps like rebooting my PC.
Ideas A sort-of work-around is to double-enter all the options in the list of validation cells. For example, the list might look like: a,A,b,B,c,C... However, doubling the options like that that makes the drop down list quite cumbersome for those people who select from the drop down list and who don't care about the case. Ie, some staff don't use different meanings for the case and don't want to be confused about the different case options. Also, it is a rather complicated workbook with lots of formulas pointing to various validation cells and ranges and I'm not sure how duplicating the same responses will affect the formulas.
One thought I had was that maybe this problem is caused by a Windows or Office update being rolled out to different PCs at different times. And maybe that is why different PCs are being affected differently? Just saying that I thought of this potential cause of the bug, but I don't know what I could do about it.
My questions are:
Does anyone know why Excel is changing behavior all of a sudden?
Is there a way to revert to the old behavior?
Any advice/thoughts you have would be welcome!
