+ Reply to Thread
Results 1 to 5 of 5

Data Validation - 2 tests, 2 different error messages

  1. #1
    Registered User
    Join Date
    06-03-2010
    Location
    Hertfordshire, England
    MS-Off Ver
    Excel 2010
    Posts
    51

    Data Validation - 2 tests, 2 different error messages

    I have a worksheet (Excel 2010) where membership numbers are being entered as people attend a meeting. I want the input validation to test:

    1) has the number already been entered - fail if it has
    2) is the number contained in the full list of membership numbers on another worksheet in the same workbook -fail if it isn't

    PLUS

    If the validation fails I want to display a different error message for each failure.

    I've worked out a custom validation formula for the validation tests:

    =AND((COUNTIF($B$6:$B$1405,B6)=1),(COUNTIF('Setup Sheet'!$A$5:$A$104,B6)=1))

    which seems to work ok, but I can't see how to get two different error messages. I don't want to use macros or VB as the finished spreadsheet will be used by other people, who may have them disabled.

    Can anyone help?

  2. #2
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: Data Validation - 2 tests, 2 different error messages

    Hi, try if this works:

    =IF(COUNTIF('Setup Sheet!$A$5:$A$104,B6)<>1,"Not member",IF(COUNTIF($B$6:$B$1405,B6)>1,"Already entered",""))

    Cheers
    <-- If you're happy & you know it...click the star.:-)

  3. #3
    Registered User
    Join Date
    06-03-2010
    Location
    Hertfordshire, England
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Data Validation - 2 tests, 2 different error messages

    Quote Originally Posted by amit.wilson View Post
    Hi, try if this works:

    =IF(COUNTIF('Setup Sheet!$A$5:$A$104,B6)<>1,"Not member",IF(COUNTIF($B$6:$B$1405,B6)>1,"Already entered",""))

    Cheers
    What, putting this as a custom validation formula in Data Validation? It just says there as error in the formula. Did you test it?

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Data Validation - 2 tests, 2 different error messages

    afik data validation only gives one error message
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: Data Validation - 2 tests, 2 different error messages

    Won't work in Data Validation. You need to put this in a cell in the sheet. Data Validation will give only one error message.

+ 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. Help With Data Validation and Creating Messages
    By panders in forum Excel General
    Replies: 1
    Last Post: 06-11-2013, 08:10 AM
  2. Excel 2007 : Conditional Data Validation Messages
    By Bob1980 in forum Excel General
    Replies: 1
    Last Post: 09-28-2011, 10:20 AM
  3. Data Validation & Messages
    By ibrahimaa in forum Excel General
    Replies: 2
    Last Post: 05-26-2011, 07:33 AM
  4. Data Validation and Error Messages
    By CathNWN in forum Excel General
    Replies: 2
    Last Post: 05-24-2011, 11:34 AM
  5. Disable validation error messages
    By jman0707 in forum Excel General
    Replies: 10
    Last Post: 10-12-2008, 01:43 PM

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