+ Reply to Thread
Results 1 to 8 of 8

reversed validation: how to get an error message if the item IS on the validation list?

  1. #1
    Registered User
    Join Date
    02-20-2014
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    35

    reversed validation: how to get an error message if the item IS on the validation list?

    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.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: reversed validation

    What do you mean?
    That is the point of validation: If cell value does not occur in the list then error is showed.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    Re: reversed validation

    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.

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,127

    Re: reversed validation

    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
    Attached Files Attached Files

    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

  5. #5
    Registered User
    Join Date
    02-20-2014
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: reversed validation

    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.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    Re: reversed validation: how to get an error message if the item IS on the validation list

    There may be other ways to achieve this: have a look at the NETWORKDAYS function.

  7. #7
    Registered User
    Join Date
    02-20-2014
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: reversed validation: how to get an error message if the item IS on the validation list

    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.

  8. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,062

    Re: reversed validation: how to get an error message if the item IS on the validation list

    data validation, custom

    Formula =iserror(match(cell to be validated,range of bank holidays,0))

    eg =iserror(match(a1,a2:a11,0))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Get dates reversed
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-11-2015, 06:04 AM
  2. [SOLVED] Vlookup reversed
    By telton in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-15-2014, 11:07 PM
  3. Excel 2007 : worksheet view reversed
    By pastbury in forum Excel General
    Replies: 4
    Last Post: 08-29-2011, 04:59 AM
  4. Operators reversed
    By jacksonville in forum Excel General
    Replies: 2
    Last Post: 12-14-2009, 06:57 PM
  5. vlookup reversed
    By market man in forum Excel General
    Replies: 2
    Last Post: 02-07-2007, 10:56 AM
  6. [SOLVED] columns reversed
    By lou in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2006, 12:45 PM
  7. reversed columns
    By Vulcan in forum Excel General
    Replies: 2
    Last Post: 06-29-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1