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

[i8abit]

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.

 

[circledchicken]

Hi,

Does this help:
Excel Data Validation -- Dependent Lists

 

[i8abit]

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.

 

[SydneyGeek]

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

 

[i8abit]

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.

 

[SydneyGeek]

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

 

[kmenz99]

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.