Excel Multiple Dropdown Lists

I have created multiple drop down lists in Excel for user to select the value by using the data validation function

However I have 5 dropdown lists and each drop down list depends on the value selected in the Dropdown list above.

I have more than 3000 values, it is hard for me to define Named Range.

It would be great if I can put the whole data table in Excel and then in the drop down list use formula as source.

It would be great if any experts can help me on this.

Thanks.
mawinghoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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...
0
mawinghoAuthor Commented:
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
0
Saurabh Singh TeotiaCommented:
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...
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

mawinghoAuthor Commented:
I am sorry, but no. my friend told me that MATCH, INDEX maybe able to help..but not that easy
0
Saurabh Singh TeotiaCommented:
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...
0
Martin LissOlder than dirtCommented:
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.
0
mawinghoAuthor Commented:
Ok, then would you mind to let me know how to use Marco to achieve the purpose?
0
Martin LissOlder than dirtCommented:
What exactly do you want the macro to do?
0
mawinghoAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
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?
Before
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".
AfterSo what isn't functional?
0
mawinghoAuthor Commented:
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.
0
Martin LissOlder than dirtCommented:
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.
0
mawinghoAuthor Commented:
I've requested that this question be deleted for the following reason:

No possible solution
0
mawinghoAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
That's all it does show!!
Sample-2.xls
0
mawinghoAuthor Commented:
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.
0
Saurabh Singh TeotiaCommented:
Their you go..try this workbook..just enable the macro's and watch then it does what you are looking for..

Saurabh...
Sample-1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Saurabh, there's a problem with the workbook.
problem
0
Saurabh Singh TeotiaCommented:
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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
+1 Saurabh!
0
Saurabh Singh TeotiaCommented:
Thanks Sktneer.. :-)

Saurabh...
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.