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:

i8abit

New Member
Joined
Aug 16, 2005
Messages
5
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. :)
 

i8abit

New Member
Joined
Aug 16, 2005
Messages
5
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Sep 25, 2015
Messages
1
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.
 

Forum statistics

Threads
1,082,323
Messages
5,364,590
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top