I've created several drop down lists based on tables (named ranges). However, there doesn't seem to be a way to restrict data entry to the items in the list. If I instead create a drop down list based on a range of cells, the data validation works as expected, and I get an error when I type in a value that's not on the list. But for drop down lists that are based on tables or named ranges, I can type in any value with no error. Thanks for your help.
Just a guess but I would say that your 'error alert' is unchecked in your data validation for the named range.
I figured it out. Apparently, For tables/named ranges, if there is a blank row in the drop down list (mine has one as a separator), the restriction and error message features are disabled. But for a simple range of cells they're not. I changed my separator to be dashes "--" and that works. I can't find any documentation on the web about this. Seems like a bug to me.
Glad you got it figured out.
I'm always a bit careful when saying there is a 'bug' in Excel. It nearly always comes back to bite you on the ****.
If you'd like, put up your workbook and I'll have a look at it.
PS: I just recreated your issue and if you uncheck ignore blank, you might get what you are after?
But you are right, the behaviour is different with a named range. Not sure why.
PPS Something I just found.
"If the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. This short video shows one possible solution to the problem, and there are written steps below the video.
Note: This problem with blank cells does not happen if the source list is a range address, e.g. $A$1:$A$10"
The video and "steps below", just tell you to uncheck 'ignore blank' as I said above.
Last edited by Croweater; 01-24-2023 at 10:18 PM.
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks