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

 

[NVRensburg]

I've managed to create a test sheet (had to remove some personal information) but perhaps this will help with what I'm trying to do?

https://docs.google.com/spreadsheet...ouid=108836213326186955199&rtpof=true&sd=true

So basically in the Quote Sheet, I want to select from the drop down lists, and as I move across, the other columns will only filter according to what was selected in the column before. I read that you can use "fakesheet" somewhere in the formula so that each column changes with the information as it changes.

I don't know how to get it to change by row....so in otherwords I want to create a quote with each row reflecting what I choose....if that makes sense?

 

[awoohaw]

I've managed to create a test sheet (had to remove some personal information) but perhaps this will help with what I'm trying to do?

https://docs.google.com/spreadsheet...ouid=108836213326186955199&rtpof=true&sd=true

So basically in the Quote Sheet, I want to select from the drop down lists, and as I move across, the other columns will only filter according to what was selected in the column before. I read that you can use "fakesheet" somewhere in the formula so that each column changes with the information as it changes.

I don't know how to get it to change by row....so in otherwords I want to create a quote with each row reflecting what I choose....if that makes sense?

you're file requires a request for permission. Please set to anybody can read. Or look at what I posted prior to your last message (Post #10)

I don't know how to get it to change by row....so in otherwords I want to create a quote with each row reflecting what I choose....if that makes sense?

^Yes, that makes your question more difficult.

 

[NVRensburg]

OK I've changed the permissions, please let me know if you are able to access it now?

 

[Burrgogi]

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.

The term you're looking for is conditional drop down list.

 

[awoohaw]

Okay, this has it. I developed this using my workbook because I'm not really sure how yours is set up. But, i think this has all you need to apply the concept to your work.

The one thing that may trip you up is something I reported to the forum over a year ago. For some reason my version of 365 the data validation automatically de-duplicates drop down validation lists. So I don't have to clean that up, which make me creating this MUCH easier, although it was still a chore.

Another thing I'd caution you on. And sorry if you are already aware. if you have already picked a value in B and/or C and change any of the "parent" drop downs (A or B) the data is wrong until you update the remaining columns. I'd suggest a worksheet change event to delete the data in the other cells so the user knows to re-enter the data.

Book2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Column A	Column B	Column C		CHOICE A		CHOICE B		CHOICE C
2	A	D	N		C		M		AW		CHOICE A DV FORMULA:
3	A	D	O		A		D		N		=OFFSET($A$1,1,0,COUNTA($A$1:$A$44)-1,1)
4	A	D	P		B		I		AG
5	A	E	Q		BA		BB		BC		CHOICE B DV FORMULA:
6	A	E	R		BA		BB		BC		=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2))/ROW($A$1:$A$44))-1,1, SUM(--($A$1:$A$44=$E2)),1)
7	A	E	S		BA		BB		BC
8	A	F	T		BA		BB		BC		CHOICE C DV FORMULA:
9	A	F	U		BD		BE		BF		=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2))/ROW($A$1:$A$44))-1,2, SUM((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2)),1)
10	A	F	V		BG		BH		BJ
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
42	BD	BE	BF
43	BG	BH	BI
44	BG	BH	BJ
45
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E2:E10	List	=OFFSET($A$1,1,0,COUNTA($A$1:$A$44)-1,1)
G2:G10	List	=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2))/ROW($A$1:$A$44))-1,1, SUM(--($A$1:$A$44=$E2)),1)
I2:I10	List	=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2))/ROW($A$1:$A$44))-1,2, SUM((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2)),1)

 

[awoohaw]

Okay, this has it. I developed this using my workbook because I'm not really sure how yours is set up. But, i think this has all you need to apply the concept to your work.

The one thing that may trip you up is something I reported to the forum over a year ago. For some reason my version of 365 the data validation automatically de-duplicates drop down validation lists. So I don't have to clean that up, which make me creating this MUCH easier, although it was still a chore.

Another thing I'd caution you on. And sorry if you are already aware. if you have already picked a value in B and/or C and change any of the "parent" drop downs (A or B) the data is wrong until you update the remaining columns. I'd suggest a worksheet change event to delete the data in the other cells so the user knows to re-enter the data.

Book2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Column A	Column B	Column C		CHOICE A		CHOICE B		CHOICE C
2	A	D	N		C		M		AW		CHOICE A DV FORMULA:
3	A	D	O		A		D		N		=OFFSET($A$1,1,0,COUNTA($A$1:$A$44)-1,1)
4	A	D	P		B		I		AG
5	A	E	Q		BA		BB		BC		CHOICE B DV FORMULA:
6	A	E	R		BA		BB		BC		=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2))/ROW($A$1:$A$44))-1,1, SUM(--($A$1:$A$44=$E2)),1)
7	A	E	S		BA		BB		BC
8	A	F	T		BA		BB		BC		CHOICE C DV FORMULA:
9	A	F	U		BD		BE		BF		=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2))/ROW($A$1:$A$44))-1,2, SUM((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2)),1)
10	A	F	V		BG		BH		BJ
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
42	BD	BE	BF
43	BG	BH	BI
44	BG	BH	BJ
45
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E2:E10	List	=OFFSET($A$1,1,0,COUNTA($A$1:$A$44)-1,1)
G2:G10	List	=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2))/ROW($A$1:$A$44))-1,1, SUM(--($A$1:$A$44=$E2)),1)
I2:I10	List	=OFFSET($A$1, 1/MAX(((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2))/ROW($A$1:$A$44))-1,2, SUM((($A$1:$A$44)=$E2)*(($B$1:$B$44)=$G2)),1)

I think I failed to mention, the drop down values will automatically expand as you add new rows to the table.

NOTE: This is mostly (99%) from Leila Gharani's youtube video found here. I suggest you watch it for more explanation:

 

[awoohaw]

Here are the formulas needed for your worksheets datavalidation. Must start in the DV in cell A6 on 'Quote Sheet' worksheet:
(Please read all of my comments in Post #15)

Dependent Drop Down Question.xlsx
	A	B	C
6	Excavation	Drainage	Sub 39
Quote Sheet

Cells with Data Validation
Cell	Allow	Criteria
A6:A12	List	=OFFSET('Master Info'!$A$5,1,0,COUNTA('Master Info'!$A$5:$A$55)-1,1)
B6:B12	List	=OFFSET('Master Info'!$A$5, 1/MAX((('Master Info'!$A$5:$A$55=$A6))/ROW('Master Info'!$A$5:$A$55))-5,1, SUM(--('Master Info'!$A$5:$A$55=$A6)),1)
C6:C12	List	=OFFSET('Master Info'!$A$5, 1/MAX(((('Master Info'!$A$5:$A$55)=$A6)*(('Master Info'!$B$5:$B$55)=$B6))/ROW('Master Info'!$A$5:$A$55))-5, 2, SUM((('Master Info'!$A$5:$A$55)=$A6)*(('Master Info'!$B$5:$B$55)=$B6)), 1)

 

[NVRensburg]

Thanks so much, I'll work on that and see how I go.

Really appreciate your time and help with this!