Three-Tiered Dependent Dropdown

[James_DK]

Good Day Everyone,

I am a relative novice with Excel and I am looking for some help and advice in creating a three-tiered dependent dropdown in Excel 365. Over the last few days I have searched Google and YouTube extensively but have so far been unable to find what I'm looking for

A friend of mine recommended this forum to me so I am hoping that somebody here may be able to help and point me in the right direction

The below table contains the data that I want to include in the dropdown lists:

Column A contains 8 unique values for Landscape Component; a number of these values are repeated due to the subsequent values in columns B and C.

Column B contains 21 unique values for Issue Category; a number of these values are repeated due to the subsequent values in column C.

Column C contains 42 unique values for Issue Type.

Below is an example, outlining the required setup for this three-tiered dropdown:

a) Select value Click and Collect in the first dropdown field (Landscape Component)
b) 1 value available for selection in the second dropdown field (Issue Category) for Order Hold Issue
c) 2 values available for selection in the third dropdown field (Issue Type) for Fraud Hold and SFCC Configuration Issue

Can anybody suggest the best way for setting this up? ???

Thanks in advance for any help or guidance!

	A	B	C
1	Landscape Component	Issue Category	Issue Type
2	Click and Collect	Order Hold Issue	Fraud Hold
3	Click and Collect	Order Hold Issue	SFCC Configuration Issue
4	DHL ESB	Integration Issue	DHL-ESB - Release Missing
5	DHL SFO	Integration Issue	DHL-SFO - Ship Confirm Timeout
6	IBM MQ	Monitoring	Queue Depth Threshold
7	IBM OMS Sterling	GWP Issue	GWP Shipped with no Product Line
8	IBM OMS Sterling	Integration Issue	DHL-ESB - Missing Partial Lines
9	IBM OMS Sterling	Integration Issue	Exception Console Max Limit Exceeded
10	IBM OMS Sterling	Integration Issue	XML Error
11	IBM OMS Sterling	Inventory Issue	Product / Item Issues
12	IBM OMS Sterling	Monitoring	Job Monitoring
13	IBM OMS Sterling	Monitoring	Memory Usage Alarm
14	IBM OMS Sterling	Monitoring	Worker Unresponsive
15	IBM OMS Sterling	Open Orders	Orders Stuck in Released Status
16	IBM OMS Sterling	Order Exception	Address Issue
17	IBM OMS Sterling	Order Exception	Daily Order Exception Check
18	IBM OMS Sterling	Order Exception	Inventory Issue
19	IBM OMS Sterling	Order Exception	Negative Charge Amount Issue
20	IBM OMS Sterling	Order Exception	PAN CREATE ORDER ERRORS
21	IBM OMS Sterling	Order Exception	PAN GENERAL ERRORS
22	IBM OMS Sterling	Order Exception	PAN ORDER AUDIT CHECK
23	IBM OMS Sterling	Order Exception	Refund Issue
24	IBM OMS Sterling	Order History	NARVAR Link Not Working
25	IBM OMS Sterling	Order Hold Issue	Cancel Order
26	IBM OMS Sterling	Order Hold Issue	Invalid Address
27	IBM OMS Sterling	Order Invoice Reprocess	IBM OMS Data Missing
28	IBM OMS Sterling	Order Release	Order Released / Cancelled
29	IBM OMS Sterling	Order Release Retrigger	Missing Attribute
30	IBM OMS Sterling	Return Order	Tax Calculation
31	IBM OMS Sterling	Sales Data Report Discrepancy	Mismatch in Sales Data
32	IBM OMS Sterling	SFTP CAD CRM EOD File Check	XML Error
33	IBM OMS Sterling	SFTP Catalog Feed File Check	XML Error
34	IBM OMS Sterling	SFTP Sales Order Audit File Check	XML Error
35	IBM OMS Sterling	Ship Confirmation Reprocess	Custom Application Notification
36	IBM OMS Sterling	Ship Exception	Duplicate Shipment Number
37	IBM OMS Sterling	User Admin	Login Issue
38	Mulesoft	CRM Connectivity	Connectivity Issue
39	Mulesoft	Monitoring	CPU Usage Alarm
40	Mulesoft	Monitoring	Memory Usage Alarm
41	Mulesoft	Monitoring	Worker Unresponsive
42	SFCC	Incorrect GST Calculation	Line GST Calculation Issue
43	WMS	Order Exception	Orders Missing in WMS

 

[Fluff]

How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Landscape Component	Issue Category	Issue Type			LC	IC	IT
2	Click and Collect	Order Hold Issue	Fraud Hold			Click and Collect	GWP Issue	Address Issue		IBM OMS Sterling
3	Click and Collect	Order Hold Issue	SFCC Configuration Issue			DHL ESB	Integration Issue	Daily Order Exception Check		Order Exception
4	DHL ESB	Integration Issue	DHL-ESB - Release Missing			DHL SFO	Inventory Issue	Inventory Issue		Address Issue
5	DHL SFO	Integration Issue	DHL-SFO - Ship Confirm Timeout			IBM MQ	Monitoring	Negative Charge Amount Issue
6	IBM MQ	Monitoring	Queue Depth Threshold			IBM OMS Sterling	Open Orders	PAN CREATE ORDER ERRORS
7	IBM OMS Sterling	GWP Issue	GWP Shipped with no Product Line			Mulesoft	Order Exception	PAN GENERAL ERRORS
8	IBM OMS Sterling	Integration Issue	DHL-ESB - Missing Partial Lines			SFCC	Order History	PAN ORDER AUDIT CHECK
9	IBM OMS Sterling	Integration Issue	Exception Console Max Limit Exceeded			WMS	Order Hold Issue	Refund Issue
10	IBM OMS Sterling	Integration Issue	XML Error				Order Invoice Reprocess
11	IBM OMS Sterling	Inventory Issue	Product / Item Issues				Order Release
12	IBM OMS Sterling	Monitoring	Job Monitoring				Order Release Retrigger
13	IBM OMS Sterling	Monitoring	Memory Usage Alarm				Return Order
14	IBM OMS Sterling	Monitoring	Worker Unresponsive				Sales Data Report Discrepancy
15	IBM OMS Sterling	Open Orders	Orders Stuck in Released Status				SFTP CAD CRM EOD File Check
16	IBM OMS Sterling	Order Exception	Address Issue				SFTP Catalog Feed File Check
17	IBM OMS Sterling	Order Exception	Daily Order Exception Check				SFTP Sales Order Audit File Check
18	IBM OMS Sterling	Order Exception	Inventory Issue				Ship Confirmation Reprocess
19	IBM OMS Sterling	Order Exception	Negative Charge Amount Issue				Ship Exception
20	IBM OMS Sterling	Order Exception	PAN CREATE ORDER ERRORS				User Admin
21	IBM OMS Sterling	Order Exception	PAN GENERAL ERRORS
22	IBM OMS Sterling	Order Exception	PAN ORDER AUDIT CHECK
23	IBM OMS Sterling	Order Exception	Refund Issue
24	IBM OMS Sterling	Order History	NARVAR Link Not Working
25	IBM OMS Sterling	Order Hold Issue	Cancel Order
26	IBM OMS Sterling	Order Hold Issue	Invalid Address
27	IBM OMS Sterling	Order Invoice Reprocess	IBM OMS Data Missing
28	IBM OMS Sterling	Order Release	Order Released / Cancelled
29	IBM OMS Sterling	Order Release Retrigger	Missing Attribute
30	IBM OMS Sterling	Return Order	Tax Calculation
31	IBM OMS Sterling	Sales Data Report Discrepancy	Mismatch in Sales Data
32	IBM OMS Sterling	SFTP CAD CRM EOD File Check	XML Error
33	IBM OMS Sterling	SFTP Catalog Feed File Check	XML Error
34	IBM OMS Sterling	SFTP Sales Order Audit File Check	XML Error
35	IBM OMS Sterling	Ship Confirmation Reprocess	Custom Application Notification
36	IBM OMS Sterling	Ship Exception	Duplicate Shipment Number
37	IBM OMS Sterling	User Admin	Login Issue
38	Mulesoft	CRM Connectivity	Connectivity Issue
39	Mulesoft	Monitoring	CPU Usage Alarm
40	Mulesoft	Monitoring	Memory Usage Alarm
41	Mulesoft	Monitoring	Worker Unresponsive
42	SFCC	Incorrect GST Calculation	Line GST Calculation Issue
43	WMS	Order Exception	Orders Missing in WMS
44
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=UNIQUE(FILTER(A2:A1000,A2:A1000<>""))
G2:G20	G2	=UNIQUE(FILTER(B2:B1000,(A2:A1000=J2)*(B2:B1000<>""),"Select J2"))
H2:H9	H2	=UNIQUE(FILTER(C2:C1000,(A2:A1000=J2)*(B2:B1000=J3)*(C2:C1000<>""),"Select J3"))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=F2#
J3	List	=G2#
J4	List	=H2#

 

[James_DK]

Hi Fluff,

Thanks a lot for your help - this works perfectly!! You're an absolute legend!!

Also, thanks for introducing me to Dynamic Array Formula too ..... this opens up a whole new world!! ?

 

[Fluff]

You're welcome & thanks for the feedback

 

[James_DK]

You're welcome & thanks for the feedback

Hi Fluff,

Hoping that you can help me out again please

As per attached screenshots, I have added the cell formulas that you suggested in the tab named Categories; now I want to add the three-tiered dropdown fields to another tab within my spreadsheet, named Raw Data.

I want to hide the Categories tab and then send the spreadsheet to someone and have them populate the relevant cells in the Raw Data tab.

When I add the dropdown fields to the Raw Data tab, this is only working correctly for the first line. Is there a way to lock in the formulad so that individual dropdown combinations can be populated on each relevant row in the Raw Data tab?

 

[Fluff]

Use
=Categories!F$2#

 

[James_DK]

Use
=Categories!F$2#

Hi Fluff,

Thanks again for your reply

As per the attached screenshots, I have tried =Categories!F$2# (also =Categories!G$2# and =Categories!H$2# for the other field values too) as you have suggested but the values displayed in columns I and J on the subsequent rows relate to the first row only

Is there anything else that I am missing here?

 

[Fluff]

Ok, I misunderstood what you were saying.
I think the best way to handle this would be to setup lists for all possible selections. Have a look here

How to make dependent dropdown lists in Excel

One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. But how can you make one dropdown dynamically respond to another? In other words, how can you make the values in a dropdown list depend on another...

exceljet.net

How to make a dependent (cascading) drop-down list in Excel

See how to create multiple dependent drop down lists in Excel based on the value selected in another dropdown, how to prevent changes in the primary drop-down menu and make dynamic depending dropdowns.

www.ablebits.com

 

[James_DK]

Ok, I misunderstood what you were saying.
I think the best way to handle this would be to setup lists for all possible selections. Have a look here

How to make dependent dropdown lists in Excel

One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. But how can you make one dropdown dynamically respond to another? In other words, how can you make the values in a dropdown list depend on another...

exceljet.net

How to make a dependent (cascading) drop-down list in Excel

See how to create multiple dependent drop down lists in Excel based on the value selected in another dropdown, how to prevent changes in the primary drop-down menu and make dynamic depending dropdowns.

www.ablebits.com

Hi Fluff,

Thanks a lot for the info

I just followed this URL and it worked a treat! Your help is much appreciated!

How to make a dependent (cascading) drop-down list in Excel

See how to create multiple dependent drop down lists in Excel based on the value selected in another dropdown, how to prevent changes in the primary drop-down menu and make dynamic depending dropdowns.

www.ablebits.com

 

[Fluff]

Glad you got it working & thanks for the feedback