Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Posts
    9
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Data Validation Function With An If Statement (2007)

    Is it possible to use the Data Validation function with an if statement; then if the if statement is false limit the input value based on a drop down list?

    Example:

    A1 can have any value

    B1 is where I want the Data Validation with an if statement to look at A1:

    If A1 has a particular value (e.g., APPLE), then I would like B1 populated with APPLE; however, if A1 does not equal APPLE, then I would like B1 limited to three values the user can select from a drop down menu (e.g., RED, BLUE, or GREEN).

    Any ideas would be greatly appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation Function With An If Statement (2007)

    Why not put the datavalidation in C1 for Red, Blue, green, then in B1 enter the formula:

    =IF(A1="APPLE",A1,C1)

    So you enter into A1 or C1 and B1 selects the appropriate one...

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Data Validation Function With An If Statement (2007)

    You can use a range which contains a list that you validate against, and that range can contain a formula that varies depending on the value in A1.

    For example the validation list for B1 could be G1:I1 and those cells can contain...

    <table border=1><td></td><td>G</td><td>H</td><td>I</td><td>1</td><td>=IF(A1="Apple", "Apple", "Red")</td><td>=IF(A1="Apple", "", "Green")</td><td>=IF(A1="Apple", "", "Blue")</td></table>

    This could be a bit of a pain if you have many rows, but you just need to copy the formula down and then hide these columns.

    StuartR

Posting Permissions

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