Dependent Drop Downs

[NVRensburg]

Hello

I'm not really sure what terminology I'm needing, but based on some google searches, I'm assuming they are dependent drop downs.

So in sheet 1 I have the data. At the moment, they are coping and pasting the vendors they want to use, but I want to create sheet 2, where they can click from a drop down list, which will filter based on the selections from the previous column if that makes sense?

Sheet 1 information

Book1.xlsx
	A	B	C	D
1	Trade	Subtrade	Subcontractors	Contact
2	Demolition	Demolition	Alaska Interiors Ltd - Demolition Tab	N/A
3	Demolition		iDemo	James Rickit
4	Demolition		McMahon	Brian Brown
5	Demolition		MAC Group Ltd	Angus Mcdonald
6	Demolition		Toptobottom	Pete Walker
7	Demolition		Green Gorilla	James Nguyen
8	Demolition		Junk Run	David Knight
9	Demolition		Kiwi Waste & Recycling	Matt Goddard
10	Demolition		Waste Management Ltd	Office
11	Demolition		GreenWay	Yeong Lee
12	Demolition	Hazardous Materials	Advanced Environmental Services	Kama Murray
13	Demolition		Glen Henderson Ltd	Rikki Jones
14	Demolition		The Agency Corp Ltd
15	Demolition	Asbestos removal	Excell Construction Limited	Raymond Excell
16	Demolition	Asbestos testing	Property Risk NZ
17	Demolition		Ward Demolition	Randal Owles
18	Demolition		Nikau Contractors	Michael Stil
19	Demolition		Yakka Contracting Limited	Bruce Levien
20	Demolition		Union Demolition	Joseph Capper
21	Demolition		TBS Farnsworth	David Leeson
22	Demolition		Safety 1st Removals	John Kerr
23	Demolition		ChemCare	Rob Beagle
24	Demolition		ATL Group	Vanessa Deeney
25	Demolition		Midland Environmental Ltd	Vanessa Deeney
26	Demolition		Burrel Demolition	Alex Burrell
27	Demolition		Midland Environmental Ltd	Vanessa Deeney
28	Demolition		At Pace Solutions	Fred Manuel
29	Excavation		Vuksich & Borich (NZ) Ltd	Bruce McLean
30	Excavation		Pink Baluga	Glenn Darrah
31	Excavation		K Benson Contracting Ltd	Clinton Okkers
32	Excavation		Contract Landscapes Ltd - CLL	Nick Rohilla
33	Excavation		Yakka Contracting Limited	Bruce Levien
34	Excavation		Auckland Civil	Brad Potier
35	Excavation		Earthco Civil	Bruce Whittaker
36	Excavation		Gleeson Civil	Nava Santharuban
37	Excavation		Active Contracting	Mathew Butterfield & Andy Thorn
38	Excavation		Civil & Build	Jorge Santana
39	Excavation		JWG Piling & Retaining Limited	Kevin Jiang
40	Excavation	Drainage	Scopic Drainage and Civil Ltd	Cam Ward
41	Excavation		Gleeson Civil	Derek Manning
42	Excavation		Hanlon Plumbing & Drainage	Jason Carr
43	Excavation	Excavation	Fixed Cost Civil Ltd	in liquidation
44	Excavation	Shoring / protection	Grouting Services	David Sharpe
45	Excavation		*	*
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B2,C2:C45,D2	Expression	=SEARCH(alaska_,$C2)	text	YES

Sheet 2

Book1.xlsx
	A	B	C	D
1	Trade	Subtrade	Subcontractors	Contact
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sheet2

 

[awoohaw]

to clarify for me please.
In column A you want (or already have) a drop down. And you want column B to be a drop of values filtered for the item select in column A for that row?
Do you have the list of all A and B? Can you share that or a representative sample?

 

[NVRensburg]

I don't have any dropdowns yet. I've tried a few things from some youtube videos but they aren't working for what I'm needing. So yes, I want column A to be a drop down, then Column b to be a dropdown based on what was selected in Column A, and column c to be a dropdown of selections based on what was selected from column B.....if that makes any sense?

 

[awoohaw]

Not sure why you could not find many useful videos on this. The MyOnlineTrainingHub, Leila Gharani, Mr. Excel and ExcelIsFun channels all have some great videos on validation lists.

My question though is what are you going to do with the blank values in column B. How do you want to create list for column C if B is empty?

 

[NVRensburg]

I've managed to get something going but still not exactly what I'm wanting/needing.

So this is the master sheet:

Tash Subctractor Form.xlsx
	N	O	P
5	Trade
6	*	Structural Steel	*
7	Appliances & Whiteware	0	2 Way Industries Ltd
8	Carpentry		4U Cabinet Makers
9	Ceilings		A P Hansen Engineering Limited
10	Commercial Cleaning		AB Electrical Ltd
11	Concrete Work & Selants		Able Metal Products (2007)
12	Demolition		Above Board
13	Doors - Non Timber		Absolute Resurfacing
14	Doors - Timber		Accessman
15	Electrical Services		Accurate Ceilings
16	Excavation		Accurate Painters
17	Exterior Works		Accurate Waterproofing
18	Fire Protection Services		ACMF
19	Flooring		Acoustic Design & Associated Works
20	Glazing		Acrow Ltd
21	Hardware		Activboard
22	HVAC Services		Active Contracting
23	Hydraulic Services		Adam Baker Electrical
24	Joinery		Adams Steelguard
25	Joinery		Aden Interiors
26	Kitchen Fitout		Advanced Automation
27	Lifts & Escalators		Advanced Security Ltd
28	Masonry		AE Smith
29	Materials		Aegion concrete
30	Metalwork		Affin Group
31	Operable Walls		Affordable Scaffolding
32	Painting		AFS Total
33	Plastering		Agape Group
34	Provisional Sums		AHI Carrier (NZ) Ltd
35	Roofing & Cladding		Air concepts Ltd
36	Security & Access Control Services		Airco Service
37	Signage & Film		AK Interiors
38	Soft Fitout		Akon Electrical
39	Special Finishes		Alex Distributors
40	Stopping		Aline Technology Group
41	Structural Steel		All Fire Passive Fire Protection
42	Tiling		Allendale Electrical
43	Timber Stairs		Alpha
44	Toilet Partitions		Alumat
45	Waterproofing		Amazing Tile Contractor
46	Window Treatments		Andrew Plastering Ltd
47			Andrew Property Services
Subs Contacts

Cell Formulas
Range	Formula
N6:N46	N6	=SORT(UNIQUE(Trade))
O6:O7	O6	=SORT(UNIQUE(FILTER(Subtrade, Trade='Quote Sheet'!A6)))
P6:P601	P6	=SORT(UNIQUE(FILTER(SUBCONTRACTOR,Subtrade='Quote Sheet'!B6)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Alaska_Interiors_Ltd___Demolition_Tab	='Subs Contacts'!$C$7:$C$926	P6
Demolition	='Subs Contacts'!$B$7:$B$926	O6:P6
SUBCONTRACTOR	='Subs Contacts'!$C$6:$C$926	P6
Subtrade	='Subs Contacts'!$B$6:$B$926	O6:P6
Trade	='Subs Contacts'!$A$6:$A$926	N6:O6

And this is the quote sheet. I'm wanting to use multiple rows to select the information, but I've only got the first row right. The others are all basing the information as per the first row and the filtered information on the master sheet

Tash Subctractor Form.xlsx
	A	B	C	D	E	F	G
1
2	DO NOT USE SUBS IN GREY
3	NEW SUB! - CHECK WITH SS/BH BEFORE LETTING ANYTHING SIGNIFICANT
4
5	Trade	Subtrade	Subcontractors	Contact	Mobile	Area	Email Address
6	Structural Steel	0	Urban Steel
7	*	*	*	N/A	N/A	N/A	N/A
8	*	*	*	N/A	N/A	N/A	N/A
9	*	*	*	N/A	N/A	N/A	N/A
10	*	*	*	N/A	N/A	N/A	N/A
11	*	*	*	N/A	N/A	N/A	N/A
12	*	*	*	N/A	N/A	N/A	N/A
13	*	*	*	N/A	N/A	N/A	N/A
14	*	*	*	N/A	N/A	N/A	N/A
15	*	*	*	N/A	N/A	N/A	N/A
Quote Sheet

Cell Formulas
Range	Formula
D7:D15	D7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],3,FALSE)
E7:E15	E7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],4,FALSE)
F7:F15	F7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],6,FALSE)
G7:G15	G7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],7,FALSE)

Named Ranges
Name	Refers To	Cells
Alaska_Interiors_Ltd___Demolition_Tab	='Subs Contacts'!$C$7:$C$926	D7:G15
CONTACT	=Table2[Contact]	D7:G15
SUBCONTRACTOR	='Subs Contacts'!$C$6:$C$926	D7:G15

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:G42	Cell Value	contains "N/A"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A6:A42	List	='Subs Contacts'!$N$6#
B6:B42	List	='Subs Contacts'!$O$6#
C6:C42	List	='Subs Contacts'!$P$6#

Am I able to attach the sheet here maybe for all the formula's/formatting?

 

[NVRensburg]

I've managed to get something going but still not exactly what I'm wanting/needing.

So this is the master sheet:

Tash Subctractor Form.xlsx
	N	O	P
5	Trade
6	*	Structural Steel	*
7	Appliances & Whiteware	0	2 Way Industries Ltd
8	Carpentry		4U Cabinet Makers
9	Ceilings		A P Hansen Engineering Limited
10	Commercial Cleaning		AB Electrical Ltd
11	Concrete Work & Selants		Able Metal Products (2007)
12	Demolition		Above Board
13	Doors - Non Timber		Absolute Resurfacing
14	Doors - Timber		Accessman
15	Electrical Services		Accurate Ceilings
16	Excavation		Accurate Painters
17	Exterior Works		Accurate Waterproofing
18	Fire Protection Services		ACMF
19	Flooring		Acoustic Design & Associated Works
20	Glazing		Acrow Ltd
21	Hardware		Activboard
22	HVAC Services		Active Contracting
23	Hydraulic Services		Adam Baker Electrical
24	Joinery		Adams Steelguard
25	Joinery		Aden Interiors
26	Kitchen Fitout		Advanced Automation
27	Lifts & Escalators		Advanced Security Ltd
28	Masonry		AE Smith
29	Materials		Aegion concrete
30	Metalwork		Affin Group
31	Operable Walls		Affordable Scaffolding
32	Painting		AFS Total
33	Plastering		Agape Group
34	Provisional Sums		AHI Carrier (NZ) Ltd
35	Roofing & Cladding		Air concepts Ltd
36	Security & Access Control Services		Airco Service
37	Signage & Film		AK Interiors
38	Soft Fitout		Akon Electrical
39	Special Finishes		Alex Distributors
40	Stopping		Aline Technology Group
41	Structural Steel		All Fire Passive Fire Protection
42	Tiling		Allendale Electrical
43	Timber Stairs		Alpha
44	Toilet Partitions		Alumat
45	Waterproofing		Amazing Tile Contractor
46	Window Treatments		Andrew Plastering Ltd
47			Andrew Property Services
Subs Contacts

Cell Formulas
Range	Formula
N6:N46	N6	=SORT(UNIQUE(Trade))
O6:O7	O6	=SORT(UNIQUE(FILTER(Subtrade, Trade='Quote Sheet'!A6)))
P6:P601	P6	=SORT(UNIQUE(FILTER(SUBCONTRACTOR,Subtrade='Quote Sheet'!B6)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Alaska_Interiors_Ltd___Demolition_Tab	='Subs Contacts'!$C$7:$C$926	P6
Demolition	='Subs Contacts'!$B$7:$B$926	O6:P6
SUBCONTRACTOR	='Subs Contacts'!$C$6:$C$926	P6
Subtrade	='Subs Contacts'!$B$6:$B$926	O6:P6
Trade	='Subs Contacts'!$A$6:$A$926	N6:O6

And this is the quote sheet. I'm wanting to use multiple rows to select the information, but I've only got the first row right. The others are all basing the information as per the first row and the filtered information on the master sheet

Tash Subctractor Form.xlsx
	A	B	C	D	E	F	G
1
2	DO NOT USE SUBS IN GREY
3	NEW SUB! - CHECK WITH SS/BH BEFORE LETTING ANYTHING SIGNIFICANT
4
5	Trade	Subtrade	Subcontractors	Contact	Mobile	Area	Email Address
6	Structural Steel	0	Urban Steel
7	*	*	*	N/A	N/A	N/A	N/A
8	*	*	*	N/A	N/A	N/A	N/A
9	*	*	*	N/A	N/A	N/A	N/A
10	*	*	*	N/A	N/A	N/A	N/A
11	*	*	*	N/A	N/A	N/A	N/A
12	*	*	*	N/A	N/A	N/A	N/A
13	*	*	*	N/A	N/A	N/A	N/A
14	*	*	*	N/A	N/A	N/A	N/A
15	*	*	*	N/A	N/A	N/A	N/A
Quote Sheet

Cell Formulas
Range	Formula
D7:D15	D7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],3,FALSE)
E7:E15	E7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],4,FALSE)
F7:F15	F7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],6,FALSE)
G7:G15	G7	=VLOOKUP($C7,Table2[[Subcontractors]:[E-Mail Address]],7,FALSE)

Named Ranges
Name	Refers To	Cells
Alaska_Interiors_Ltd___Demolition_Tab	='Subs Contacts'!$C$7:$C$926	D7:G15
CONTACT	=Table2[Contact]	D7:G15
SUBCONTRACTOR	='Subs Contacts'!$C$6:$C$926	D7:G15

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:G42	Cell Value	contains "N/A"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A6:A42	List	='Subs Contacts'!$N$6#
B6:B42	List	='Subs Contacts'!$O$6#
C6:C42	List	='Subs Contacts'!$P$6#

Am I able to attach the sheet here maybe for all the formula's/formatting?

 

[awoohaw]

can you post a decent representation of complete records of data where all 3 columns are completely populated? I still don't see how you can have a drop down list with all those blanks in column B.

 

[NVRensburg]

Am I able to attach the whole workbook here with the 2 different sheets?

 

[awoohaw]

not here. You can place on a safe share drive like dropbox or your google drive or onedrive share files.
But, I think I only need three columns with about 20-40 rows, as long as there are 3-5 different items in A, 3-5 items associated with each A item in B, and 2-3 items associated with each B in C.

 

[awoohaw]

I just went and made up a bunch of values to use.
This is for Excel 365.
See if you can work with this.
1. Your Fact Data needs to be in a table (for this example COLUMNA-COLUMNC, with header names.
2. From that you need to have sorted unique values in 3 other ranges, (see columns K to M).
3. Then you build your drop downs based on columns K to M, using the "#" symbol to indicate that the range references a dynamic array.

Hope this helps.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Column A	Column B	Column C		COL A SELECTION		COL B SELECTION		COL C SELECTION			COLADROP	COLBDROP	COLCDROP
2	A	D	N		B		I					A	H	AE
3	A	D	O		^^for ease and reference here, these will be							B	I	AF
4	A	D	P		where the drop downs are placed on your processing							BA	J	AG
5	A	E	Q		worksheet							C		AH
6	A	E	R
7	A	E	S
8	A	F	T
9	A	F	U
10	A	F	V
11	A	G	W
12	A	G	X
13	A	G	Y
14	A	G	Z
15	B	H	AA
16	B	H	AB
17	B	H	AC
18	B	H	AD
19	B	I	AE
20	B	I	AF
21	B	I	AG
22	B	I	AH
23	B	J	AI
24	B	J	AJ
25	B	J	AK
26	B	J	AL
27	B	J	AM
28	C	K	AN
29	C	K	AO
30	C	K	AP
31	C	K	AQ
32	C	L	AR
33	C	L	AS
34	C	L	AT
35	C	L	AU
36	C	L	AV
37	C	M	AW
38	C	M	AX
39	C	M	AY
40	C	M	AZ
41	BA	BB	BC
Sheet1

Cell Formulas
Range	Formula
L2:L5	L2	=SORT(UNIQUE(TBL_LOOKUPS[Column A]))
M2:M4	M2	=SORT(UNIQUE(FILTER(TBL_LOOKUPS[Column B],TBL_LOOKUPS[Column A]=E2,"")))
N2:N5	N2	=FILTER(TBL_LOOKUPS[Column C],(TBL_LOOKUPS[Column A]=E2)*(TBL_LOOKUPS[Column B]=G2),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$L$2#
G2	List	=$M$2#
I2	List	=$N$2#

The drop downs don't copy over with the xl2bb, I think: