Drop list affected by previous choice

[Gaëtan Mongeon]

Hello,

I am using Excel 2004 for Mac and need help on drop lists. I want to change
the drop list depending on the previous choice. Lets say in the first list I
give the choice between 3 options (jumper, pigtail or duplex). Depending on
the choice taken, there would be another drop list that will vary. For the
jumper I have options A, B or C. For the pigtail I have options D, E or F
and for the duplex, I have options G, H or J.

Can someone help me on this issue?

Thanks,

Gaetan

 

[Bernard Rey]

Gaëtan Mongeon a écrit :

I am using Excel 2004 for Mac and need help on drop lists. I want to change
the drop list depending on the previous choice. Lets say in the first list I
give the choice between 3 options (jumper, pigtail or duplex). Depending on
the choice taken, there would be another drop list that will vary. For the
jumper I have options A, B or C. For the pigtail I have options D, E or F
and for the duplex, I have options G, H or J.

If you drop list are made through the "Validation" option in the "Data"
menu, one way could be:

In cells C1, C2 and C3, type: jumper, pigtail and duplex.
In cell D1, type:
=IF(A1="jumper","A",IF(A1="pigtail","D",IF(A1="duplex","G","")))
In D2: =IF(A1="jumper","B",IF(A1="pigtail","E",IF(A1="duplex","H","")))
In D3: =IF(A1="jumper","C",IF(A1="pigtail","F",IF(A1="duplex","J","")))

Then select cell A1, in the Data > Validation... window, "Allow: List" and
set the "Source" to range "$C$1:$C$3"

Select cell B1 and, in the Data > Validation... window, "Allow: List" and
set the "Source" to range "$D$1:$D$3"

Now, when you select Pigtail from the drop list in A1, you'll have the drop
list in cell B1 offering D, E and F as options.


If this doesn't match your requirements or if you don't use "Validation" to
have your drop lists, could you be more specific about what you're doing?

 

[Gaëtan Mongeon]

Thanks Bernard,

It works. I am putting a matrix to figure out a part number and price of
certain fiber optic jumpers, pigtails or duplex. I want to limit the option
to the sales force to what is actually available from the vendor. There are
certain options that are not available on all 3 types of cordages. This way
I minimized the errors.

I have 3 types of cordages to work from. No matter what type is chosen, I
can go with either a singlemode or a multimode fiber. Then I give the option
of the connector and then the diameter of the jackets that are available
with the connectors chosen.

I also want to keep it as clean as possible, even if it means using VBA.