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

notsonoobexcel

New Member
Joined
Jun 11, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
PlantSKUConcatPick a batch
abc
12345​
abc12345
def
98765​
def98765
ghi
33333​
ghi33333
abc
12345​
abc12345

Base data is in the below format:


PlantSKUConcatBatch
abc
12345​
abc12345BU9872
abc
12345​
abc12345MY6283
def
98765​
def987658B8123
def
98765​
def98765PP8122
abc
12345​
abc12345LI1233

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"
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
ABCDEFGHIJK
1PlantSKUConcatPick a batch
2abc12345abc12345LI1233BU98728B8123#CALC!BU9872#CALC!#CALC!
3def98765def98765PP8122MY6283PP8122MY6283
4ghi33333ghi33333LI1233LI1233
5abc12345abc12345MY6283
6
7Base data is in the below format:
8
9
10PlantSKUConcatBatch
11abc12345abc12345BU9872
12abc12345abc12345MY6283
13def98765def987658B8123
14def98765def98765PP8122
15abc12345abc12345LI1233
Sheet8
Cell Formulas
RangeFormula
F2:F4,I2:I4,H2,J2:K2,G2:G3F2=FILTER($D$11:$D$15,$C$11:$C$15=INDEX($C:$C,COLUMNS($F2:F2)+1))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
D2:D5List=INDEX($2:$2,ROWS($D$2:$D2)+COLUMN($F$2)-1)#
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top