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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,976
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)#
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,368
Members
418,499
Latest member
mbcmel

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
Top