Dual dropdown list, second list is filtered based on selections from first dropdown

i8abit

New Member
Joined
Aug 16, 2005
Messages
5
I’m wondering if anyone has a solution for the following without having to resort to VB code, as I’ve been racking my brain for the past week, researching, reading, testing, etc.

Here’s the scenario (example data below).

I have two worksheets.
----------------------------------------------------------
The first worksheet has the following
A1 and B1 are titles (see following example)

Column A Column B
A1 Dept B1 Product
A2 Sales B2 gloves
A3 Sales B3 hats
A4 Sales B4 shoes
A5 Finance B5 gloves
A6 Finance B6 shirts
A7 Finance B7 socks
…etc.
----------------------------------------------------------
The second worksheet has the following
A1, B1 and C1 are titles (see following example)

Column A Column B Column C
A1 Dept B1 Product C1 Bin location
A2 ? B2? C2 Bin 3
A3 ? B3? C3 Bin 4
A4 ? B4? C4 Bin 12
A5 ? B5? C5 Bin 11
----------------------------------------------------------

On the second worksheet I want to generate dropdowns on column A and column B (shown in ?), from the values on the first spreadsheet under column A and B.

I can easily create a range/name definition in the first worksheet for Dept, and use that as the data validation/dropdown list in the second worksheet under column A. '=indirect' functions dont seem to allow multiple columns or filtering, unless someone knows a way.

So, the big question is … on the second spreadsheet, can Column B generate a dependent dropdown list from Column A, whereby filtering out only those values from Column B shown in the first spreadsheet?

i.e.
On the second spreadsheet (column A and B). If you select Sales in A2, then the dropdown on B2 should only show products for sales (gloves, hats, shoes, etc). Though on the first spreadsheet Finance shows values of shirts, socks (including gloves) all of those values should be filtered out in the dropdown list, because I only want to see the products linked to Sales.

This took a bit to formulate this question, so hopefully it’s clear what I’m trying to do; otherwise, let me know.

using Excel 2010

Thank you in advance.
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Unfortunately, no... I've been to that link earlier this week, and tried setting up a formula of sorts. What I really need is a filter on the second dropdown list, that is actually generated from a list. I would have to create multiple columns for each dept, which would be hundreds of columns with a few product values for each which is what that link proposes. Thank you for the link though. :)
 
Upvote 0
It seems like such an easy function to have in excel, but yet it doesn't exist? I'm thinking it could be some sort of vlookup/filter of sorts. Any other thoughts suggestions are welcome.
 
Upvote 0
No such feature exists in Excel: you need to roll your own. I once tried to build something which ended up with 120 different lists in a series of cascades, which is why I built the code-based approach instead.

Denis
 
Upvote 0
This may be overkill but it uses VBA to create a new list from a master, filtering on criteria from a validation drop-down.
Using VBA to simplify maintenance of complex cascading lists

Denis


I get an error when running the Refresh Main List macro. The debugger highlights .Properties("Extended Properties").Value = "Excel 8.0", any idea what needs to be changed?

Would this work for a larger list. I have 10 to 15 columns and each one needs to be dependent on the other.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top