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]

 

[Taul]

Hi & Welcome to the forum.

Take a look at the site link below
http://www.contextures.com/xlDataVal02.html

It is a tutorial for dependent drop down lists.
Also, there are downloadable example files you can play with at the bottom of the tutorial.

Paul.

 

[DaVixsta]

Hi Paul

That was one of the first ones I found. And it doesn't work with the data I use.....

Hi & Welcome to the forum.

Take a look at the site link below
http://www.contextures.com/xlDataVal02.html

It is a tutorial for dependent drop down lists.
Also, there are downloadable example files you can play with at the bottom of the tutorial.

Paul.

 

[Marcelo Branco]

A very similar question...
https://www.mrexcel.com/forum/excel-questions/997737-cascading-drop-down-lists-data-validation.html

See if the suggestion in post #2 helps

M.

 

[DaVixsta]

Marcelo that's great!!

I that example works perfect for me. But when I input my own data I get an error. Is it because there are numbers and symbols?
Is there a fix for this??

On another note...Can I not upload the spreadsheet i'm working on for others to view?

 

[Marcelo Branco]

Is not possible to upload a workbook in this forum.
Try to post a small data sample, as you did in post #1, showing your real data (with symbols, numbers,...)
Take a look at (section B)
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[DaVixsta]

Thanks for the link! Here is what I'm working with. There are 1000's of lines of parts...

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD="bgcolor: #5B9BD5"]

Category​

[/TD]
[TD="bgcolor: #5B9BD5"]

Subcategory​

[/TD]
[TD="bgcolor: #5B9BD5"]

Sales Description​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

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

ABS​

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

ABS 1-1/2​

[/TD]
[TD="bgcolor: #FFFFFF"]1-1/2 ABS PIPE PER FOOT[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

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

ABS​

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

ABS 1-1/2​

[/TD]
[TD="bgcolor: #FFFFFF"]1-1/2 ABS FITTING CLEANOUT[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

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

BLACK​

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

BLACK 1​

[/TD]
[TD="bgcolor: #FFFFFF"]1 BLACK CAP[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

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

BLACK​

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

BLACK 1​

[/TD]
[TD="bgcolor: #FFFFFF"]1 BLACK PIPE PER FT[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

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

BLACK​

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

BLACK 1​

[/TD]
[TD="bgcolor: #FFFFFF"]1 BLACK 90 ELBOW[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

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

BLACK​

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

BLACK 1​

[/TD]
[TD="bgcolor: #FFFFFF"]1 BLACK COUPLING[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

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

BRASS​

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

BRASS 1/4​

[/TD]
[TD="bgcolor: #FFFFFF"]1/4 BRASS 45 ELBOW[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

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

BRASS​

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

BRASS 1/4​

[/TD]
[TD="bgcolor: #FFFFFF"]1/4 BRASS UNION[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

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

BRASS​

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

BRASS 1/4​

[/TD]
[TD="bgcolor: #FFFFFF"]1/4 BRASS COUPLING[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

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

BRASS​

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

BRASS 1/4​

[/TD]
[TD="bgcolor: #FFFFFF"]1/4 X 1-1/2 BRASS NIPPLE[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

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

BUILDING MATERIALS​

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

HANGARS​

[/TD]
[TD="bgcolor: #FFFFFF"]1/2 COPPER SPLIT RING HANGER[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

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

BUILDING MATERIALS​

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

HANGARS​

[/TD]
[TD="bgcolor: #FFFFFF"]1/2 COPPER SWIVEL RING HANGER[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

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

BUILDING MATERIALS​

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

HANGARS​

[/TD]
[TD="bgcolor: #FFFFFF"]1/2 UNISTRUT CLAMPS PAIR EI U931[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

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

COPPER​

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

COPPER 1-1/2​

[/TD]
[TD="bgcolor: #FFFFFF"]1-1/2 X 1-1/4 X 3/4 COPPER TEE[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

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

COPPER​

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

COPPER 1-1/2​

[/TD]
[TD="bgcolor: #FFFFFF"]1-1/2 X 3/4 X 1-1/4 COPPER TEE[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

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

COPPER​

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

COPPER 1-1/2​

[/TD]
[TD="bgcolor: #FFFFFF"]1-1/2 X 3/4 X 3/4 COPPER TEE[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

18​

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

COPPER​

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

COPPER 1-1/2​

[/TD]
[TD="bgcolor: #FFFFFF"]1-1/2 COPPER PIPE TYPE M PER FOOT[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

19​

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

ELECTRICAL​

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

CONDUIT​

[/TD]
[TD="bgcolor: #FFFFFF"]2 PVC CONDUIT COUPLING[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

20​

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

ELECTRICAL​

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

CONDUIT​

[/TD]
[TD="bgcolor: #FFFFFF"]1 PVC CONDUIT PIPE PER FOOT[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

21​

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

ELECTRICAL​

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

CONDUIT​

[/TD]
[TD="bgcolor: #FFFFFF"]1-1/2 PVC CONDUIT PIPE PER FOOT[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

22​

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

ELECTRICAL​

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

CONDUIT​

[/TD]
[TD="bgcolor: #FFFFFF"]2 PVC CONDUIT PIPE PER FOOT[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: WORK[/TD]
[/TR]
</tbody>[/TABLE]

Is not possible to upload a workbook in this forum.
Try to post a small data sample, as you did in post #1, showing your real data (with symbols, numbers,...)
Take a look at (section B)
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[Marcelo Branco]

Question
Every category has just one Subcategory like in your data sample above?

M.

 

[DaVixsta]

No unfortunately....ABS for example has more then one Subcategory. Could be
[TABLE="width: 108"]
<tbody>[TR]
[TD]ABS 1-1/2[/TD]
[/TR]
[TR]
[TD]ABS 1-1/4
ABS 2
ABS 4[/TD]
[/TR]
</tbody>[/TABLE]

Question
Every category has just one Subcategory like in your data sample above?

M.

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 108"]
<tbody>[TR]
[TD]ABS 1-1/2[/TD]
[/TR]
[TR]
[TD]ABS 1-1/4
[TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]ABS 2.....
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

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.