+ Reply to Thread
Results 1 to 9 of 9

Data Validation - Same Cell with 2 Different Criteria

  1. 07-07-2010, 10:34 AM #1
    No.Solutions.
    No.Solutions. is offline
    Registered User
    Join Date
    10-01-2008
    Location
    Canada
    Posts
    27

    Data Validation - Same Cell with 2 Different Criteria

    Hello All,

    What I'm trying to accomplish:
    If the cell B1 is populated with "No" then I want the cell A1 to remain as an Any Value option.
    If the cell B1 is populated with "Yes" then I want the cell A1 to be a Data Validation List option based on the range named "Apples".

    I can get the List option no problem but I cannot seem to get it to keep the Any Value status at all! I'm thinking I may need to delve into a macro to accomplish this goal but I'm not really sure and am hoping to avoid it if at all possible.

    Thanks for your assistance
    No.Solutions.
    Last edited by No.Solutions.; 07-07-2010 at 02:27 PM.
    Reply With QuoteRegister To Reply
  2. 07-07-2010, 10:48 AM #2
    No.Solutions.
    No.Solutions. is offline
    Registered User
    Join Date
    10-01-2008
    Location
    Canada
    Posts
    27

    Re: Data Validation - Same Cell with 2 Different Criteria

    I fixed the macro I've been working on to accomplish the desired result but I would still prefer a solution with no macro if at all possible.

    Here's the temporary fix until I can find if it's possible without the macro:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 07-07-2010, 11:01 AM #3
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Data Validation - Same Cell with 2 Different Criteria

    Set data validation to custom and enter this formula

    =IF(B1="No", True,IF(ISERROR(MATCH(A1,Apples,0))=FALSE,True,False))

    You could also use

    =IF(OR(B1="No",ISERROR(MATCH(A1,Apples,0))=FALSE),TRUE,FALSE)
    Last edited by Whizbang; 07-07-2010 at 11:06 AM.
    Reply With QuoteRegister To Reply
  4. 07-07-2010, 11:56 AM #4
    No.Solutions.
    No.Solutions. is offline
    Registered User
    Join Date
    10-01-2008
    Location
    Canada
    Posts
    27

    Re: Data Validation - Same Cell with 2 Different Criteria

    Thanks Whizbang, unfortunately when I entered your formulas into the custom section of Data Validation they did not return anything
    Reply With QuoteRegister To Reply
  5. 07-07-2010, 12:06 PM #5
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Data Validation - Same Cell with 2 Different Criteria

    Do you mind clarifying what you mean? What are you expecting to be "returned"? Try entering a value into cell A1. If B1 = "No" you will be able to enter any value. If B1 = "Yes" and the value in A1 does not match any value in "Apples" then you will receive an error message.

    Unless you deselected the "Show error alert after invalid data is entered" option.

    Another possibility is that your "Apples" range is not spelled the same or something.
    Reply With QuoteRegister To Reply
  6. 07-07-2010, 12:20 PM #6
    No.Solutions.
    No.Solutions. is offline
    Registered User
    Join Date
    10-01-2008
    Location
    Canada
    Posts
    27

    Re: Data Validation - Same Cell with 2 Different Criteria

    I looked at your formula again and it does work to your specifications; unfortunately the end result that I need would be that if B1 = "No" then any value could be entered (which you have achieved). If B1="Yes" then the cell turns into a dropdown list using the name range of Apples.
    Reply With QuoteRegister To Reply
  7. 07-07-2010, 12:40 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Data Validation - Same Cell with 2 Different Criteria

    In data validation for A1, tick Ignore blank and In-cell dropdown, Allow: List, Source: =IF(B1="No", Blank, Apples)

    ... where Blank is a single-cell named range with nothing in it.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  8. 07-07-2010, 01:14 PM #8
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Data Validation - Same Cell with 2 Different Criteria

    Ah. I misread the original post.

    Thank you, shg. I learned something new.
    Reply With QuoteRegister To Reply
  9. 07-07-2010, 02:27 PM #9
    No.Solutions.
    No.Solutions. is offline
    Registered User
    Join Date
    10-01-2008
    Location
    Canada
    Posts
    27

    Re: Data Validation - Same Cell with 2 Different Criteria

    Sweet! Thanks for both of your help! Works awesomely!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1