Data Validation & Dependent Dropdown list

[Schturman]

Hi to all
I read here a few post about Dependent Dropdown list, but still confused and can't get correct solution.
In Sheet2 I have a all data. In the cell E2 I use unique formula to get the names of companies:

=UNIQUE(Table7[Company])

In the cell F2 I use filer formula to get the names of all products:

=UNIQUE(Table7[Product])

In the cell H2 I use filer & unique formula (just for test) to get the names of products related to cell B2 in the Sheet1:

=UNIQUE(FILTER(Table7[Product],Table7[Company]=Sheet1!$B3))

In the Sheet1 - in the cell B2 I use list and I copied this cell till B7:

=Sheet2!$E$2#

Now is the problem... I can't directly use Filter or Unique formula inside Data validation and for now I used in the cell C2 this formula for list and I copied this cell till C7:

=Sheet2!$F$2#

It give me All products in the list and I want only related to the name of Company in a cells B2:B7 in the Sheet1
If I use H2 from Sheet2 in data validation, it stay related only to B3 even if I drag it down

How I can get correct dropdown lists in cells C2:C7 in Sheet1 ?
Thanks

 

[CSmith]

Use named ranges on your spills. and then =INDIRECT("NamedRange") for the DV List

 

[Schturman]

Use named ranges on your spills. and then =INDIRECT("NamedRange") for the DV List

Please example ? I still confused...

 

[CSmith]

This seems to work . . .

Book1
	A	B	C	D	E	F
1	XLOOKUP Double formula
2	Group	Company	Product	Price	Quantity	Total
3	Tables	Barkan	Table 1	30	2	60
4	Delivery	Salomon	Handles 1	15	1	15
5	Glass	Salomon	Table 2	55	1	55
6	Handles	Jones	Plates 1	49	2	98
7	Plates	Burton	Glass 2	25	3	75
Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=E3*D3

Cells with Data Validation
Cell	Allow	Criteria
B3:B7	List	=INDIRECT(B$2&"List")
C3:C7	List	=OFFSET(Sheet2!$M$2,1,MATCH(OFFSET($C3,0,-1),Sheet2!$M$2:$P$2,0)-1,COUNTA(OFFSET(Sheet2!$M$2,1,MATCH(OFFSET($C3,0,-1),Sheet2!$M$2:$P$2,0)-1,15))-COUNTIFS(OFFSET(Sheet2!$M$2,1,MATCH(OFFSET($C3,0,-1),Sheet2!$M$2:$P$2,0)-1,15),""),1)

Book1
	L	M	N	O	P
1		Products	Products	Products	Products
2	Companies	Barkan	Salomon	Jones	Burton
3		Glass 1	Handles 1	Plates 1	Table 1
4		Glass 2	Handles 2	Delivery	Handles 1
5		Table 1	Table 1	Handles 1	Delivery
6		Delivery	Table 2	Table 1	Plates 1
7				Glass 1	Plates 2
Sheet2

Cell Formulas
Range	Formula
M2:P2	M2	=IF(ISBLANK(INDIRECT("$E"&COLUMN(M$2)-11)),"",INDIRECT("$E"&COLUMN(M$2)-11))
M3:M7	M3	=IF(ROWS($L$2:L2)<=COUNTIF(Table7[Company],M$2),INDEX(Table7[Product],AGGREGATE(15,3,(Table7[Company]=M$2)/(Table7[Company]=M$2)*(ROW(Table7[Company])-ROW(Table7[#Headers])),ROWS($L$2:L2))),"")
N3:N7	N3	=IF(ROWS($L$2:L2)<=COUNTIF(Table7[Company],N$2),INDEX(Table7[Product],AGGREGATE(15,3,(Table7[Company]=N$2)/(Table7[Company]=N$2)*(ROW(Table7[Company])-ROW(Table7[#Headers])),ROWS($L$2:L2))),"")
O3:O7	O3	=IF(ROWS($L$2:L2)<=COUNTIF(Table7[Company],O$2),INDEX(Table7[Product],AGGREGATE(15,3,(Table7[Company]=O$2)/(Table7[Company]=O$2)*(ROW(Table7[Company])-ROW(Table7[#Headers])),ROWS($L$2:L2))),"")
P3:P7	P3	=IF(ROWS($L$2:L2)<=COUNTIF(Table7[Company],P$2),INDEX(Table7[Product],AGGREGATE(15,3,(Table7[Company]=P$2)/(Table7[Company]=P$2)*(ROW(Table7[Company])-ROW(Table7[#Headers])),ROWS($L$2:L2))),"")

Named Ranges
Name	Refers To	Cells
Companies	=Sheet2!$M$2:$P$2	M2:M7

Cell formulas for products will copy down fine.
Might have been better to use cell refs in place of table ones . . .

 

[CSmith]

Forgot this one Named Range:

Named Ranges
Name	Refers To	Cells
CompanyList	=Sheet2!$E$2:$E$5	M2:M7

 

[Schturman]

Wow, thank you! I will try this.

 

[CSmith]

Wow, thank you! I will try this.

Welcome Please let me know if it works out like you were wanting.