Solved I want to use an IF statement to select a drop-down list

February 15, 2012 at 12:56:09
Specs: Windows XP, 1.0Gb

I am trying to use an IF statement to select different drop-down lists but I could not get it to work.

If A1= "one, I want to use drop-down list #1;
If A1= "two", I want to use drop-down list #2;
If A1 = "three", I want to use drop-down list $3.

Thank you
Brian W


See More: I want to use an IF statement to select a drop-down list

Report •


✔ Best Answer
February 16, 2012 at 10:47:47

The "formulas needed for a dependent list" - there is only one formula required for a drop down in any given cell - is used in the Data Validation Source Field, not in a cell. The only way to see the formula is to open the Data Validation wizard.

Here are the steps to do what you want:

1 - Select the range of cells that contains the drop down choices that you want to see when A1 contains 1.
2 - Name the range List_1

3 - Select the range of cells that contains the drop down choices that you want to see when A1 contains 2.
4 - Name the range List_2

5 - Select the range of cells that contains the drop down choices that you want to see when A1 contains 3.
6 - Name the range List_3

7 - Select B1
8 - Choose Data...Validation...List
9 - In the Source Field enter =INDIRECT("List_" & A1)

If you don't yet have a value in A1, this may present an error when you click OK. Don't worry about, just click OK.

10 - Enter !, 2 or 3 in A1
11 - Check out the drop down in B1

What the INDIRECT function does is build a Name from the string List_ and the value in A1, giving you either List_1, List_2 or List_3.

The Data validation feature then uses the data in that Named Range to populate the Drop Down.

It is only because you asked to use 1, 2 and 3 that we have to "build" the Range Name inside the INDIRECT function since 1, 2 and 3 are not valid names for ranges.

If you wanted to use One, Two or Three, you would use the more simple =INDIRECT(A1) since One, Two and Three are valid names.

That, in a nutshell, is what is explained at the site that I provided the link to.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 15, 2012 at 13:06:20

Do you mean that you want to populate a specific drop down (say in B1) with different choices based on the value in A1?

Try the steps outlined here:

http://www.contextures.com/xldatava...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 15, 2012 at 14:30:52

DerbyDad03,

Thank you for your response; however, it wasn't dependent lists that I was looking for.

If I input "1" in A1, I want B1 to show drop-down list #1 so I can select the appropriate choice from that list;

If I input "2" in A1, I want B1 to show drop-down list #2 so I can select the appropriate choice from that list;

If I input "3" in A1, I want B1 to show drop-down List #3 so I can select the appropriate choice from that list;

Thank you.
Brian W


Report •

#3
February 15, 2012 at 18:32:12

That's exactly what a dependent drop down list is.

The drop down in a cell (e.g. B1) is dependent on the value in another cell (e.g. A1).

In other words, depending on what value is in A1, the drop down list in B1 will show different choices.

I suggest that you go back to that site and see if you can make it work for you.

You are going to have to be creative with your named ranges since "1", "2" and "3" are not valid names for ranges. However, "List_1", "List_2", etc. are. Consider that a hint.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 16, 2012 at 09:58:16

DerbyDad03,

Thanks for your suggestion; I am working on it. Is there a way to print-out the formulas needed for a dependent list? I tried "Formulas>Show Formula" but that did not work.

Thank you.
Brian W


Report •

#5
February 16, 2012 at 10:47:47
✔ Best Answer

The "formulas needed for a dependent list" - there is only one formula required for a drop down in any given cell - is used in the Data Validation Source Field, not in a cell. The only way to see the formula is to open the Data Validation wizard.

Here are the steps to do what you want:

1 - Select the range of cells that contains the drop down choices that you want to see when A1 contains 1.
2 - Name the range List_1

3 - Select the range of cells that contains the drop down choices that you want to see when A1 contains 2.
4 - Name the range List_2

5 - Select the range of cells that contains the drop down choices that you want to see when A1 contains 3.
6 - Name the range List_3

7 - Select B1
8 - Choose Data...Validation...List
9 - In the Source Field enter =INDIRECT("List_" & A1)

If you don't yet have a value in A1, this may present an error when you click OK. Don't worry about, just click OK.

10 - Enter !, 2 or 3 in A1
11 - Check out the drop down in B1

What the INDIRECT function does is build a Name from the string List_ and the value in A1, giving you either List_1, List_2 or List_3.

The Data validation feature then uses the data in that Named Range to populate the Drop Down.

It is only because you asked to use 1, 2 and 3 that we have to "build" the Range Name inside the INDIRECT function since 1, 2 and 3 are not valid names for ranges.

If you wanted to use One, Two or Three, you would use the more simple =INDIRECT(A1) since One, Two and Three are valid names.

That, in a nutshell, is what is explained at the site that I provided the link to.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question