Drop list affected by previous choice

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gaëtan Mongeon - 25 Jun 2005 19:28 GMT

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

Reply to this Message

Bernard Rey - 25 Jun 2005 20:02 GMT

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?

---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

Signature

Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr

Reply to this Message

Gaëtan Mongeon - 25 Jun 2005 21:23 GMT

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.

On 6/25/05 3:02 PM, in article BEE376F3.1269F%b.rey@DontSpamMe.mvps.org,

> Gaëtan Mongeon a écrit :
>
[quoted text clipped - 29 lines]
> Please reply to the newsgroup, and within the same thread.
> Merci de répondre au groupe, et dans l'enfilade.

Reply to this Message

Mac Forum / Applications / Excel / June 2005

Drop list affected by previous choice