Excel Multiple Dropdown Lists

mawingho,

Can you please update your excel file with example about what you are looking for as it will be easy to provide a solution for you then..

Saurabh...

Let me give you an example.
I have obtained some sample data.
I need to create a worksheet to allow user to select the product.

What I want to do is
1st dropdown list, shows only the unique value of Province
2nd dropdown list, shows only unique values of Customer Segment relevant to the option selected in the dropdown list above.
3rd dropdown list, shows only unique values of Product Category relevant to the option selected in the dropdown lists above.
4th dropdown list, shows only unique values of Product Sub-Category relevant to the option selected in the dropdown lists above.
5th dropdown list, shows only unique values of Product Name relevant to the option selected in the dropdown lists above.

I searched some solution on the web, but I don't think create thousands of name ranged would be feasible as I have more than ten thousand rows in the real data.

You know if I create unique value of each, then I will have to spend so much time and when the data change, then I need to spend so much time to update...

I know new version of Excel can use the table concept and can be easier to achieve the objective above. I hope someone can help. Thanks.
Sample.xls

Quick question are you open for a macro/vba solution for this as i can write a macro for you to do this very easily and in a faster manner

Saurabh...

I am sorry, but no. my friend told me that MATCH, INDEX maybe able to help..but not that easy

Unfortunately..If you want to leave your data like the way it is you need to do it by macro only..You can't do it by formula as with formula you first need to create a unique list for everyone and then will have to create a validation over it which will be a lot of complex process..

Again if you want to do it with formulas you need to re-arrange your data in the manner as shown in below mentioned links:-

Link-1

Link-2

Link-3

Link-4

Now with VBA i can write a code for you to do this without you have to re-arranging your data otherwise what you are looking for you can't do it till the time you re-arrange your data..

Saurabh...

When I select one of the Provinces it only shows the rows with that province, and if I then select a Segment it further restricts the rows that are show, and selecting in the other columns further narrows down the rows. I have to admit that I don't understand what you want it to do differently.

Ok, then would you mind to let me know how to use Marco to achieve the purpose?

What exactly do you want the macro to do?

U see many drop down lists in my originally excel file
Those drop down lists are not functional now
I want to use Marco to make first one list all the unique values from the first column of the table as options
The second one list all the unique values from second column but depends on the selected value from the first drop down list

U see many drop down lists in my originally excel file

Here is the top portion of what I see when I open the workbook. Are the 5 drop down lists the ones you are referring to?

Those drop down lists are not functional now

Here is the top portion of what I see after I select I click the drop down in column A and select "Nunavut".
So what isn't functional?

Hi

You should go to option worksheet

The objective is

When you select Province option

The Customer Segment drop down list should shows only the options related to the option selected in the Province drop down list.

I'm sorry but I still don't understand. Please create a small workbook with about 20 records. Then please tell me exactly what to do, and what rows I should see after I do it. For example

1. Select "Product Category" from the Option sheet
2. Select "Alberta" from the filter list in column A of the Data sheet
3. After doing that, rows 2, 3, 8 and 14 should appear.

I've requested that this question be deleted for the following reason:

No possible solution

If you select  "Alberta" in the drop down list "Province",

The dropdown list "Customer Segment" should only shows
Consumer
Corporate
Home Office
Small Business
Sample.xls

That's all it does show!!
Sample-2.xls

my objective is to make the drop down list shows unique value depends on the option selected in the previous drop down list
When I say the drop down list is not functional means it does not work as intended.

Saurabh, there's a problem with the workbook.

Hey Martin,

Not sure what's wrong or What went wrong with that one..You can click on the message and edit anyway as i tested it and all looked good..Or here is the revised copy again as this looked good..

Saurabh...
Sample-1.xls

+1 Saurabh!

Thanks Sktneer.. :-)

Saurabh...

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.