Using an If/Then Statement For Data Validation List

[OkayKid]

I've been reading through posts about dependent lists, but either they don't address my specific question or I don't understand.

I have several named ranges on one worksheet and corresponding drop down lists on another. On the list spreadsheet there's a drop down list in A1, and a drop down list in B1, which are not co-dependent. The drop down list in C1, however, will be dependent on the text in the previous two lists. In the data validation box for the list in C1, I need a formula that would accomplish something like this, however this formula doesn't work:

(IF((AND(A1="Choice One",B1="Choice Three")),UseListChoiceListOption

In other words, if A1 has the text "Choice 1" and B1 has the text "Choice 3" then use the list called ChoiceListOption.

I've been trying to use the "INDIRECT" function, but can make it fit.

Thanks!

 

[jeffreybrown]

This works for me...

=IF(AND($A$1="Choice One",$B$1="Choice Three"),UseListChoiceListOption,AnotherList)

 

[OkayKid]

This works for me...

=IF(AND($A$1="Choice One",$B$1="Choice Three"),UseListChoiceListOption,AnotherList)

Thanks! That helped me get to me next step, which is nesting other if/then statements in the Data Validation list. I have a couple of questions:

First: What's the function of adding AnotherList in the formula?

Second: How do I add the other conditions? I'm trying a couple variations of the following without success:

=IF(AND($A$1="Choice One",$B$1="ChoiceThree"),UseListChoiceListOption,AnotherList),IF(AND($A$1="Choice One",$B$1="Choice Four"),OtherListOption)

There will be about 4 different if/then statements. If I can get the first two right, I should be able to copy the formula and get the others.

 

[OkayKid]

I found the answer to my second question!

=(IF((AND($A$1="Choice One",$B$1="Choice Three")),UseListChoiceListOption,(IF((AND($A$1="Choice One",$B$1="Choice Four")),OtherListOption))))

This worked.

Thank you for your help!

 

[OkayKid]

Okay, here's another question:

(BTW, I'm using Excel 2007 -- I forgot to mention that).

I have successfully added the multiple condition formula for the drop down list in C1. Let's say I have made selections in all three drop down lists, in A1, B1, and C1. After the selections are in all three drop down lists, I go back and change the selection in B1. Currently, when I change B1, the selection in C1 remains the same until I click on the drop down list again, even though the the selection in C1 now still pertains to my first selection in B1, which no longer correlates.

My question is: Is it possible to make C1 to be blank once I've changed my selection in B1?

Thanks again!

 

[jeffreybrown]

Sorry for the delay, I have been away all day.

You'll have to look into using some worksheet code to clear C1 on change of B1.

Right click on the sheet >> view code and paste code below.

Change the sheet name to what your sheet is called.

Where to paste code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    [COLOR="Red"]Set ws = Sheets("Sheet1")[/COLOR]
     If Not Intersect(Target, Range("B1")) Is Nothing Then
        ws.Range("C1").Value = ""
    End If
End Sub

 

[OkayKid]

That worked! Thanks!

I have some other questions; let me know if you think I should create a separate thread.

The drop down lists in A1, B1, and C1 have selections, and now I'm creating a 4th drop down in D1 which is dependent on C1's response.

I've taken the range used in the C1 drop down list and created two separate named ranges. There are 14 items in the first separated named range, which I'll call "C1Range1" and 5 items in the second separated range, which I'll call "C1Range2" all of which are text, and depending on which range the selection in C1 belongs to, D1 should populate respective lists. Here's the formula I'm trying to use, but it's not working:

=(IF($C$1=C1Range1),C1Range1List,(IF($C$1=C1Range2),C1Range2List))

In other words, if the response in C1 is part of the C1Range1 range, use list C1Range1List, or if the response is in the C1Range2 range, use list C1Range2List. Is it not working because the items in each range are text? Or can ranges just not be used in this formula in this fashion?

Thanks!

 

[jeffreybrown]

Have a look here for some ideas to help you through this process. In the search box type in >> dependent lists.

Many links will show with good tutorials on drop down list strategies.

The link below may be closer to what you need.

Excel Data Validation -- Create Dependent Lists With INDEX

Let me know if this get's you any further

 

[OkayKid]

I tried to use INDEX to accomplish this, but I was unsuccessful. I ended up creating a range for each of the options and just substituting spaces with "_", and then using the formula:

=INDIRECT(SUBSTITUTE($C$1," ","_"))

I'm sure there are more economical ways to do it, but hey it works.

Thank you for you help!

 

[jeffreybrown]

Glad you got it working