change group text with drop down choise

[masterAndCommander]

Hi,
I have a problem with an Excel spreadsheet that I can not set properly, I'll explain the problem.
I have the need to place inside a drop-down list the list of some stores, and for each of these, I have the need to show the list of available products.
In an Excel spreadsheet I set the drop-down but now I can not connect the values of this drop-down (eg. store_1, store_2, etc.) with the list of available products.
I thought I put into another excel sheet a table thus formed:

STORE_1	STORE_2
pen_1	pen_4
pen_2	pen_5
pen_3	pen_6

but then I do not know how to link the value of the drop-down (eg. store_1) with the display of products (eg. pen_1, pen_2, pen_3).

The idea is to have in the same sheet where it is called the drop-down a set of rows where to show the list of products according to your choice in the drop-down.

Can you help me?

[Glenn Kennedy]

is this the sort of thing you want? It does take some time to set up... but it really does make life easier. Enable macros when you open it.

[masterAndCommander]

First of all thanks for your answer.
That's not exactly what I'm looking because the list of products (in your example cities) must be as a list and not as a selectable drop-down.
Imagine you have a single drop-down on top of the sheet where you can choose the country (eg. Australia, Egypt, etc.) and a set of rows (not a drop-down) where the cities are displayed according to your selection.

[Glenn Kennedy]

Maybe you'd be better to post a sample sheet. Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[masterAndCommander]

I have attached a sample file of how I wish that you did the sheet. Obviously the list of products should dynamically change based on selection of the drop-down, in the example I did it for the store 1 but if I change my choice of drop-down to "store 2" instead of the store 1's products (pen 1, pen 2 and pen 3) should be shown the store 2's products (pen 4, pen 5 and pen 6).

[FlameRetired]

Try this formula in F5 and fill down until you get blanks. Then try the drop down.

Please Login or Register  to view this content.

[masterAndCommander]

Fantastic, you have solved my problem.
Thank you very much!!!!

[masterAndCommander]

I would like to take advantage, if I can, of your kindness and ask another for help.
I have in a column 5 drop down and I would like that the last 4 are selected ONLY if you select the second value of the first drop down (the first drop down has only two values). Can I do this thing?

[FlameRetired]

You are welcome, and thank you for the feedback.

I am having difficulty visualizing what you describe. Would you upload another sample file that demonstrates that?

Thank you, ahead of time.

[masterAndCommander]

I have attached an example.
I would that only when is selected from the first drop down the second value are enabled other drop down, but if the first value is selected the other drop down remain off.
Of course if you select the second value (the only permitted to activate other drop down) of the first drop down and then (after maybe already chosen the values of the other drop down) again select the first value automatically the other drop down must be emptied and disable itself.

[FlameRetired]

masterAndCommander,

Thank you for the additional upload. Unfortunately I have been unable to understand what you are attempting to do.

Let me see if we can get help.

[masterAndCommander]

I would that the second and the third drop down are selected ONLY if you select the second value of the first drop down.

[snb]

You need VBA:

Please Login or Register  to view this content.

[masterAndCommander]

ok, thanks.