Forum Discussion
Data Validation Flagging List Items as Error when Typed But Accepts when Picked
I have a table containing a named range for the validation list for the cells in another table. Some of the items in the validation list have a tilde (~) in them (i.e.- shr~test). When selected from the dropdown, the value is accepted. When typed in Excel recognizes the entry, but the value is then flagged as an invalid selection. How can I fix this issue?
Thanks!
- Try entering a double tilde (~~) instead of a single tilde when typing the value in the cell. Excel should then interpret it as a single tilde in the actual entry.
- Well, that is problematic. These values are being dynamically pulled from the Active Directory group names. I created a new column in the validation list table that used SUBSTITUTE to replace "~" with "~~" and assigned on of the cells validation to that new column. It still didn't work. Guess, I may have to live with it! The validation actually works, it just flags it as an invalid selection.
