3 Tiers of cascading (dependent) filtering

[DaVixsta]

Hello Hello

I have a spreadsheet with a huge list of parts, It is broken down into Category, Subcategory, and Sales Description.

I have been trying to use data validation on a separate sheet to build a parts list of my choosing from the huge master list.
I can filter the Category and then Subcategory....but it doesn't want to filter past 2 tiers?

Am I going about this the wrong way??

Data looks as such...

[TABLE="width: 630"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Subcategory[/TD]
[TD]Sales Description[/TD]
[/TR]
[TR]
[TD]ABS[/TD]
[TD]ABS 1-1/2[/TD]
[TD]1-1/2 ABS PIPE PER FOOT[/TD]
[/TR]
[TR]
[TD]ABS[/TD]
[TD]ABS 1-1/2[/TD]
[TD]1-1/2 ABS FITTING CLEANOUT[/TD]
[/TR]
[TR]
[TD]BRASS[/TD]
[TD]BRASS 1[/TD]
[TD]1 BRASS CAP[/TD]
[/TR]
[TR]
[TD]BRASS[/TD]
[TD]BRASS 1[/TD]
[TD]1 BRASS 45 ELBOW[/TD]
[/TR]
[TR]
[TD]COPPER[/TD]
[TD]COPPER 1/2[/TD]
[TD]1/2 COPPER FIP ADAPTER[/TD]
[/TR]
[TR]
[TD]COPPER[/TD]
[TD]COPPER 1/2[/TD]
[TD]1/2 COPPER FITTING 45 ELBOW[/TD]
[/TR]
</tbody>[/TABLE]

 

[DaVixsta]

Marcelo that works great for me.

Is there a way to make it work for multiple rows. It works perfect for Row 1 of Sheet 1. Will I have to create multiple Category and Cat-Sub Unique columns to do so?

Thanks so much for your help.

Say your raw data in sheet WORK is something like this (columns A:C)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Category​

[/TD]
[TD]

Subcategory​

[/TD]
[TD]

Sales Description​

[/TD]
[TD][/TD]
[TD]

Category Unique​

[/TD]
[TD][/TD]
[TD]

Cat-Sub Unique​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

ABS​

[/TD]
[TD]

ABS 1-1/2​

[/TD]
[TD]

1-1/2 ABS PIPE PER FOOT​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

ABS​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

ABS​

[/TD]
[TD="bgcolor: #B8CCE4"]

ABS 1-1/2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

ABS​

[/TD]
[TD]

ABS 1-1/2​

[/TD]
[TD]

1-1/2 ABS FITTING CLEANOUT​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

BLACK​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

ABS​

[/TD]
[TD="bgcolor: #B8CCE4"]

ABS 1-1/4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

ABS
​

[/TD]
[TD]

ABS 1-1/4​

[/TD]
[TD]

1-1/4 ABS PIPE PER FOOT​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

BRASS​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

ABS​

[/TD]
[TD="bgcolor: #B8CCE4"]

ABS 2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

ABS
​

[/TD]
[TD]

ABS 1-1/4​

[/TD]
[TD]

1-1/4 ABS FITTING CLEANOUT​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

BUILDING MATERIALS​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

BLACK​

[/TD]
[TD="bgcolor: #B8CCE4"]

BLACK 1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

ABS​

[/TD]
[TD]

ABS 2​

[/TD]
[TD]

2 ABS PIPE PER FOOT​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

COPPER​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

BRASS​

[/TD]
[TD="bgcolor: #B8CCE4"]

BRASS 1/4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

ABS​

[/TD]
[TD]

ABS 2​

[/TD]
[TD]

2 ABS FITTING CLEANOUT​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

ELECTRICAL​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

BUILDING MATERIALS​

[/TD]
[TD="bgcolor: #B8CCE4"]

HANGARS​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

BLACK​

[/TD]
[TD]

BLACK 1​

[/TD]
[TD]

1 BLACK CAP​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

COPPER​

[/TD]
[TD="bgcolor: #B8CCE4"]

COPPER 1-1/2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

BLACK​

[/TD]
[TD]

BLACK 1​

[/TD]
[TD]

1 BLACK PIPE PER FT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

ELECTRICAL​

[/TD]
[TD="bgcolor: #B8CCE4"]

CONDUIT​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

BLACK​

[/TD]
[TD]

BLACK 1​

[/TD]
[TD]

1 BLACK 90 ELBOW​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

BLACK
​

[/TD]
[TD]

BLACK 1​

[/TD]
[TD]

1 BLACK COUPLING​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

BRASS
​

[/TD]
[TD]

BRASS 1/4​

[/TD]
[TD]

1/4 BRASS 45 ELBOW​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

BRASS​

[/TD]
[TD]

BRASS 1/4​

[/TD]
[TD]

1/4 BRASS UNION​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

BRASS​

[/TD]
[TD]

BRASS 1/4​

[/TD]
[TD]

1/4 BRASS COUPLING​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

BRASS​

[/TD]
[TD]

BRASS 1/4​

[/TD]
[TD]

1/4 X 1-1/2 BRASS NIPPLE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

BUILDING MATERIALS​

[/TD]
[TD]

HANGARS​

[/TD]
[TD]

1/2 COPPER SPLIT RING HANGER​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

BUILDING MATERIALS
​

[/TD]
[TD]

HANGARS​

[/TD]
[TD]

1/2 COPPER SWIVEL RING HANGER​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

BUILDING MATERIALS​

[/TD]
[TD]

HANGARS​

[/TD]
[TD]

1/2 UNISTRUT CLAMPS PAIR EI U931​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

COPPER​

[/TD]
[TD]

COPPER 1-1/2​

[/TD]
[TD]

1-1/2 X 1-1/4 X 3/4 COPPER TEE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

COPPER​

[/TD]
[TD]

COPPER 1-1/2​

[/TD]
[TD]

1-1/2 X 3/4 X 1-1/4 COPPER TEE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

COPPER
​

[/TD]
[TD]

COPPER 1-1/2​

[/TD]
[TD]

1-1/2 X 3/4 X 3/4 COPPER TEE​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

COPPER
​

[/TD]
[TD]

COPPER 1-1/2​

[/TD]
[TD]

1-1/2 COPPER PIPE TYPE M PER FOOT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

ELECTRICAL​

[/TD]
[TD]

CONDUIT​

[/TD]
[TD]

2 PVC CONDUIT COUPLING​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

ELECTRICAL​

[/TD]
[TD]

CONDUIT​

[/TD]
[TD]

1 PVC CONDUIT PIPE PER FOOT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

ELECTRICAL​

[/TD]
[TD]

CONDUIT​

[/TD]
[TD]

1-1/2 PVC CONDUIT PIPE PER FOOT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD]

ELECTRICAL​

[/TD]
[TD]

CONDUIT​

[/TD]
[TD]

2 PVC CONDUIT PIPE PER FOOT​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Try to use exactly the same data setup (sheets name, rows and columns) and perform the steps below

First of al:
Create a list of unique categories in column E (gray area) and a list of unique pairs Category - SubCategory in columns G:H (blue area)
You can easily achieve this using Data > Remove Duplicates after copy/paste the entire list of Category in column E and doing the same for the pairs Category-SubCategory,

This done and assuming that the drop-downs will be located in Sheet1
A2 -> Category
B2 -> SubCategory
C2 -> Description
let's create 3 dynamic named ranges and use them in data validation.

Named range CATEGORY
Formulas > Names Manager > New
Name:CATEGORY
Refers to: =OFFSET(WORK!$E$2,,,COUNTA(WORK!$E:$E)-1)

Select Sheet1 A2
Data Validation > List
=CATEGORY

Do the same for SUBCATEGORY
Name:SUBCATEGORY
Refers to: =OFFSET(INDEX(WORK!$H:$H,MATCH(Sheet1!$A2,WORK!$G:$G,0)),,,COUNTIF(WORK!$G:$G,Sheet1!$A2))

Select Sheet1 B2
Data Validation > List
=SUBCATEGORY

At last... (phew)
Do the same for DESCRIPTION
Name:DESCRIPTION
Refers to: =OFFSET(INDEX(WORK!$C:$C,MATCH(Sheet1!$B2,WORK!$B:$B,0)),,,COUNTIF(WORK!$B:$B,Sheet1!$B2))

Select Sheet1 C2
Data Validation > List
=DESCRIPTION

Hope this helps

M.

 

[Marcelo Branco]

Is there a way to make it work for multiple rows. It works perfect for Row 1 of Sheet 1.

Copy A1:C1 and Paste in A2:Cx

M.

 

[DaVixsta]

I wish it was that easy. Once a selection has been made in A1:C1 The following rows only see those selections. I cannot filter both ABS and BRASS at the same time.

Copy A1:C1 and Paste in A2:Cx

M.

 

[Marcelo Branco]

Copy/Paste worked perfectly for me. New drop-downs were created in the rows below.

M.

 

[Marcelo Branco]

As specified in message #10 the first drop-downs must be created in row 2 (assuming headings in row 1) in A2, B2, and C2.
Then try Copy and Paste

M.

 

[DaVixsta]

Hello Marcelo

I've double checked everything and and ensured the first drop downs were created in Row 2. Then I copied A2 to C2 down to Row 3. The filters work but the subcategory only shows filtered results from the selected filter in A2. Not my choice in B2...
Any ideas?

As specified in message #10 the first drop-downs must be created in row 2 (assuming headings in row 1) in A2, B2, and C2.
Then try Copy and Paste

M.

 

[Marcelo Branco]

hmmm...
i think i forgot to say you must select Sheet1 B2 before creating the named range SUBCATEGORY;
the same for range C2,i.e, select C2 before creating the named range DESCRIPTION
Sorry...

Also make sure the formulas use Sheet1!$A2 (not $A$2) and Sheet1!$B2 (not Sheet1!$B$2)

M.

 

[DaVixsta]

Oh of course. What a simple thing. Thank you so much for your help with this. It works great!

hmmm...
i think i forgot to say you must select Sheet1 B2 before creating the named range SUBCATEGORY;
the same for range C2,i.e, select C2 before creating the named range DESCRIPTION
Sorry...

Also make sure the formulas use Sheet1!$A2 (not $A$2) and Sheet1!$B2 (not Sheet1!$B$2)

M.

 

[Marcelo Branco]

You are welcome. Glad to help

M.