How to create a dynamic list based on value in one cell when the list to be created is based on another cell?

[notsonoobexcel]

I will try to be as clear as possible. This is my destination where I would like for a drop down in the column "Pick a batch" for each row.

Plant	SKU	Concat	Pick a batch
abc	12345​	abc12345
def	98765​	def98765
ghi	33333​	ghi33333
abc	12345​	abc12345

Base data is in the below format:

Plant	SKU	Concat	Batch
abc	12345​	abc12345	BU9872
abc	12345​	abc12345	MY6283
def	98765​	def98765	8B8123
def	98765​	def98765	PP8122
abc	12345​	abc12345	LI1233

An example of what I would like to see as end result under "Pick a batch" :
For row 1 where "Concat" = abc12345 - required result should be a drop down list of "BU9872, MY6283, LI1233"
For row 2 where "Concat" = def 98765 - required result should be a drop down list of "8B8123, PP8122"

 

[Eric W]

Welcome to the MrExcel forum!

Unfortunately, Excel won't let you put a SPILL formula in a data validation formula. So you'll need to find some real estate in your worksheet to create your lists. In the example below, I create the formula in F2, and drag it to the right. I then create the data validation rule in D2:D5:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Plant	SKU	Concat	Pick a batch
2	abc	12345	abc12345	LI1233		BU9872	8B8123	#CALC!	BU9872	#CALC!	#CALC!
3	def	98765	def98765	PP8122		MY6283	PP8122		MY6283
4	ghi	33333	ghi33333			LI1233			LI1233
5	abc	12345	abc12345	MY6283
6
7	Base data is in the below format:
8
9
10	Plant	SKU	Concat	Batch
11	abc	12345	abc12345	BU9872
12	abc	12345	abc12345	MY6283
13	def	98765	def98765	8B8123
14	def	98765	def98765	PP8122
15	abc	12345	abc12345	LI1233
Sheet8

Cell Formulas
Range	Formula
F2:F4,I2:I4,H2,J2:K2,G2:G3	F2	=FILTER($D$11:$D$15,$C$11:$C$15=INDEX($C:$C,COLUMNS($F2:F2)+1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D2:D5	List	=INDEX($2:$2,ROWS($D$2:$D2)+COLUMN($F$2)-1)#