+ Reply to Thread
Results 1 to 6 of 6

Data validation based on another cells contents

  1. #1
    Registered User
    Join Date
    10-14-2011
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    9

    Data validation based on another cells contents

    Hi Forum,

    I need a cell to restrict the value entered based on a selection in a drop down box i have in another cell.

    Cell C3 has options a, b, c, d
    Cell C13 needs to be restricted to whole numbers based on the option chosen from drop down list in C3
    if c3=a then 1<C13<50
    if c3=b then 1<C13<10
    else 1<C13<3

    Im using Excel 2010
    I select C13 and under Data Validation > Settings > Allow > Custom and Formula:
    Please Login or Register  to view this content.
    The validation box accepts this but regardless of the value i enter it returns an error saying the value is not valid. What options and/or code should I change?

    Thank you in advance.
    Last edited by Baabaa; 10-14-2011 at 05:52 AM.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Data validation based on another cells contents

    Use

    =IF($C$3="a",AND($C$13>1,$C$13<50),IF($C$3="b",AND($C$13>1,$C$13<10),AND($C$13>1,$C$13<3)))

  3. #3
    Registered User
    Join Date
    10-14-2011
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    9

    Thumbs up Re: Data validation based on another cells contents

    Thanks Bob,

    I've never been able to figure out the AND. This is solved.

  4. #4
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Data validation based on another cells contents

    A better way without the IFs

    =AND($C$13>1,OR(AND($C$3="a",$C$13<50),AND($C$3="b",$C$13<10),$C$13<3))

  5. #5
    Registered User
    Join Date
    10-14-2011
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Data validation based on another cells contents

    Thank you bob,

    I'm not actually clued up on this sort of thing, trying to learn the logic as I go. I have copied that code and inserted it. Actually the code is for a formula in the cell validation. Is the code you suggested better because my code is cumbersome and in more complex scenarios would become very long?

  6. #6
    Registered User
    Join Date
    09-09-2014
    Location
    Mumbai
    MS-Off Ver
    2007
    Posts
    1

    Re: Data validation based on another cells contents

    Thanks. It was helpful

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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