Dependent data validation

parkjun

New Member
Joined
May 27, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I have data with list of departments. I want to create a dependent data validation for user choose to input information (exp list of values in center depends on value in division)
I have try formula Filter but it doest work in data validation or Indirect (not suitable with list of value has more than 20 values)

Can someone help me resolve this problem? Thank you.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
helper cells are needed (cols D and F below)

MrExcelPlayground23.xlsx
ABCDEFGHI
1NameDeptDepartmentsDept:A
2JohnAAJohnPerson:Mary
3MaryABMary
4SallyBFred
5FredAKeith
6BillBBo
7KeithA
8BoA
Sheet4
Cell Formulas
RangeFormula
D2:D3D2=UNIQUE(Table3[Dept])
F2:F6F2=FILTER(Table3[Name],Table3[Dept]=I1)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I1List=D2#
I2List=F2#
 
Upvote 1
Thank you @JamesCanale, I tried this method but this one is only suitable for single input, it doest work if I want to apply for multi-row input
helper cells are needed (cols D and F below)

MrExcelPlayground23.xlsx
ABCDEFGHI
1NameDeptDepartmentsDept:A
2JohnAAJohnPerson:Mary
3MaryABMary
4SallyBFred
5FredAKeith
6BillBBo
7KeithA
8BoA
Sheet4
Cell Formulas
RangeFormula
D2:D3D2=UNIQUE(Table3[Dept])
F2:F6F2=FILTER(Table3[Name],Table3[Dept]=I1)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I1List=D2#
I2List=F2#
 
Upvote 0
Maybe:
MrExcelPlayground23.xlsx
ABCDEFGHIJKLM
1NameDeptDepartmentsDept:AB
2JohnAAJohnPerson:Mary
3MaryABMary
4SallyBCSally
5FredADFred
6BillBBill
7KeithAKeith
8BoABo
9Person 1CPerson 6
10Person 2CPerson 7
11Person 3C
12Person 4C
13Person 5D
14Person 6A
15Person 7B
16Person 8D
Sheet4
Cell Formulas
RangeFormula
D2:D5D2=UNIQUE(Table3[Dept])
F2:F10F2=FILTER(Table3[Name],ISNUMBER(XMATCH(Table3[Dept],I1:M1,0)))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I2List=F2#
I1:M1List=$D$2#
 
Upvote 1

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

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