Hi, I want a cell to generate an error based upon a validation list. HOWEVER, I want this to happen when the cell entry DOES NOT occur in the list. How do I do that ? Thanks.
Hi, I want a cell to generate an error based upon a validation list. HOWEVER, I want this to happen when the cell entry DOES NOT occur in the list. How do I do that ? Thanks.
Last edited by AliGW; 12-29-2017 at 08:20 AM. Reason: Title updated to properly reflect requirements and to avoid confusion.
What do you mean?
That is the point of validation: If cell value does not occur in the list then error is showed.
Welcome to the forum!
That is the way it works: set up a list with 'yes' and 'no' as the options, then try typing 'maybe' into the cell - you get an error message.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Forum Rules (updated September 2018): please read them here.
How to use the Power Query code you've been given: help here. More about the Power suite here.
Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.
welcome to the forum. inside the Data Validation window via DATA tab -> Data Validation, there is a Error Alert tab. click on that and you can customise the Title and the message. i have done some screenshots for you inside the file
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hi,
I wish to use the validation option in a specific way : I want excell to stop when the cell entry DOES match with the validation list.
To make it more tangible : the cell should contain a calendar date in a file, used to request for a day off. If the date in this cell is part of a list with bank holidays, the error code should stop and warn.
(as if I would make a list with all calender days, except the bank holidays...)
Last edited by jokkebal; 12-29-2017 at 08:13 AM.
There may be other ways to achieve this: have a look at the NETWORKDAYS function.
Hi Ali, thanks for that tip. Didn't know it and may be useful... however not in my case. I want to make the file "stupid-proof" by preventing people to choose a specific date when asking a day off. This specific date being a bank holiday, which is "off anyway.
data validation, custom
Formula =iserror(match(cell to be validated,range of bank holidays,0))
eg =iserror(match(a1,a2:a11,0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks