+ Reply to Thread
Results 1 to 6 of 6

multiple data validation rules for one cell

  1. #1
    Registered User
    Join Date
    11-20-2014
    Location
    Madison, WI
    MS-Off Ver
    Excel for MAC 2011
    Posts
    50

    multiple data validation rules for one cell

    I need to have multiple data validation rules in a single cell. I've just watched a bunch of tutorials with examples but nothing that fits exactly what I am trying to accomplish. So, I've tried to create my own custom data validation formula...but nothing is working!

    Here's an explanation of what I need:

    If D52 equals "Small", I need the number the user enters in D55 to be greater than or equal to 220

    If D52 equals "Large", I need the number the user enters in D55 to be greater than or equal to 330

    So, if "Small" is entered in cell D52, I need an error message to appear if they then enter a number less than 220 in cell D55. At the same time, I need an error message to appear if "Large" is entered in cell D52 and then they enter a number less than 330 in cell D55.

    What custom data validation formula can I use in cell D55?

    Thank you.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: multiple data validation rules for one cell

    Try this as your rule...
    =OR(AND(D52="large",D55>=330),AND(D52="small",D55>=220))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: multiple data validation rules for one cell

    With small amendment to FDibbins formula to be more precise I feel

    =OR(AND(D52="large",D55>=330),AND(D52="small",D55>=220,D55<330))
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    11-20-2014
    Location
    Madison, WI
    MS-Off Ver
    Excel for MAC 2011
    Posts
    50

    Re: multiple data validation rules for one cell

    Thank you very much. I thought the solution was likely an OR statement but I couldn't get it quite right. You solution seems to work! Thanks again.

  5. #5
    Registered User
    Join Date
    11-20-2014
    Location
    Madison, WI
    MS-Off Ver
    Excel for MAC 2011
    Posts
    50

    Re: multiple data validation rules for one cell

    Making sure I understand the consequence of your amendment: does your formula allow an entry less than 330 if cell D52 is blank? Any other consequences or implications?

    Thanks!

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: multiple data validation rules for one cell

    To not allow any entry in D55 when D52 is blank, in the data validation box checkout Ignore Blank.

+ 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] Two Data Validation Rules for a Cell based on entry in Adjacent Cell
    By TINHAMMER in forum Excel General
    Replies: 13
    Last Post: 01-23-2021, 02:03 PM
  2. [SOLVED] Two Data Validation Rules In One Cell
    By Bosulli in forum Excel General
    Replies: 3
    Last Post: 03-08-2018, 12:53 PM
  3. [SOLVED] Data validation with multiple rules
    By Rockguts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2016, 08:47 AM
  4. [SOLVED] multiple validation rules
    By Mayrie in forum Excel General
    Replies: 8
    Last Post: 09-07-2012, 08:50 PM
  5. [SOLVED] Can Multiple Data Validation Rules be Defined for the Same Range
    By PosseJohn in forum Excel General
    Replies: 1
    Last Post: 08-16-2012, 01:19 PM
  6. validation rules not working when someone copy paste data on validation cell
    By jthakrar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2010, 03:36 AM
  7. Replies: 2
    Last Post: 01-03-2010, 05:21 PM

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