Data validation drop down list source to lookup a value and list unused items ?

[gaftalik]

Hello everyone!

In a sheet i have the following:

A2=Joe; A2= Jack; A4= John and for each name there is a recommended Name list in B2:B4 (Fruitlist;Vegetableslist;Dairylist)
A2:A4 is defined as Names
G2:G5 is defined Fruitslist
L2:L4 is defined Vegetableslist
Q2:Q4 is defined Dairylist


In B7 i have a data validation drop down list where source = Names
In B10:B13 i have a validation data drop down list with the source formula =INDIRECT(INDEX($B$2:$B$4,MATCH($B$7,$A$2:$A$4,0)))
In each of G2:G5; L2:L4 and Q2:Q4 i have formulas which calculate the remaining unused products of B10:B13


Is it possible to have in B10:B13 a data validation drop down list to look up B7 in the Name list (B2:B4) to return the unused products range without the blank cells of the hidden products?

Thank you.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Names	Name list		Fruits	Position	ID	Unused products		Vegetables	Position	ID	Unused products		Dairy	Position	ID	Unused products
2	Joe	Fruitslist		Apple	1	1	Apple		Tomatoes	1	1	Tomatoes		Cheese	1	1	Cheese
3	Jack	Vegetableslist		Banana	2	2	Banana		Peas	2	2	Peas		Milk	2	2	Milk
4	John	Dairylist		Strawberry	3	3	Strawberry		Onions	3	3	Onions		Yoghurt	3	3	Yoghurt
5				Peach	4	4	Peach
6
7	Name	Joe
8
9
10	Type 1
11	Type 2
12	Type 3
13	Type 4
Sheet1

Cell Formulas
Range	Formula
J2	=IF(ISNA(MATCH($I2,$B$10:$B$13,0)),MAX(J$1:J1)+1,"")
J3	=IF(ISNA(MATCH($I3,$B$10:$B$13,0)),MAX(J$1:J2)+1,"")
J4	=IF(ISNA(MATCH($I4,$B$10:$B$13,0)),MAX(J$1:J3)+1,"")
L2	=IF($K2<=MAX($J$2:$J$4),INDEX($I$2:$I$4,MATCH($K2,$J$2:$J$4,0)),"")
L3	=IF($K3<=MAX($J$2:$J$4),INDEX($I$2:$I$4,MATCH($K3,$J$2:$J$4,0)),"")
L4	=IF($K4<=MAX($J$2:$J$4),INDEX($I$2:$I$4,MATCH($K4,$J$2:$J$4,0)),"")
O2	=IF(ISNA(MATCH($N2,$B$10:$B$13,0)),MAX(O$1:O1)+1,"")
O3	=IF(ISNA(MATCH($N3,$B$10:$B$13,0)),MAX(O$1:O2)+1,"")
O4	=IF(ISNA(MATCH($N4,$B$10:$B$13,0)),MAX(O$1:O3)+1,"")
Q2	=IF($P2<=MAX($O$2:$O$4),INDEX($N$2:$N$4,MATCH($P2,$O$2:$O$4,0)),"")
Q3	=IF($P3<=MAX($O$2:$O$4),INDEX($N$2:$N$4,MATCH($P3,$O$2:$O$4,0)),"")
Q4	=IF($P4<=MAX($O$2:$O$4),INDEX($N$2:$N$4,MATCH($P4,$O$2:$O$4,0)),"")
E2	=IF(ISNA(MATCH($D2,$B$10:$B$13,0)),MAX(E$1:E1)+1,"")
E3	=IF(ISNA(MATCH($D3,$B$10:$B$13,0)),MAX(E$1:E2)+1,"")
E4	=IF(ISNA(MATCH($D4,$B$10:$B$13,0)),MAX(E$1:E3)+1,"")
E5	=IF(ISNA(MATCH($D5,$B$10:$B$13,0)),MAX(E$1:E4)+1,"")
G2	=IF($F2<=MAX($E$2:$E$5),INDEX($D$2:$D$5,MATCH($F2,$E$2:$E$5,0)),"")
G3	=IF($F3<=MAX($E$2:$E$5),INDEX($D$2:$D$5,MATCH($F3,$E$2:$E$5,0)),"")
G4	=IF($F4<=MAX($E$2:$E$5),INDEX($D$2:$D$5,MATCH($F4,$E$2:$E$5,0)),"")
G5	=IF($F5<=MAX($E$2:$E$5),INDEX($D$2:$D$5,MATCH($F5,$E$2:$E$5,0)),"")