# Data Validation and Filter Function



## ziza (Dec 21, 2022)

Hi,
I have two defined tables, table A und table B with a realtion 1:n.

I want that the users choose in table A a Id from table B which matches the A_ID. Just like in row 12 Hardcoded.

In row 4 I made it with the Filter Function but that doesn't work in the data validation dropdown.
I know the option with defined names but the final tables have too much rows for definining are named range for each.


do you may know a soulution?

Best
ziza

Test1 Kopie.xlsxABCDEFGHIJK12Dropdown Should be like34511123Test15Test4678910TableATable B11A_IDChoose B_IDB_IDA_ID12511123Dropdown Hardcoded=FILTER(t_B[B_ID];t_B[A_ID]=D12) in Data Validation not workingTest151112313511124Test251112414511125Test351112515Test451112316Test551112417Test65111251819Tabelle1Cell FormulasRangeFormulaE4:E5E4=FILTER(t_B[B_ID],t_B[A_ID]=D4)Dynamic array formulas.Cells with Data ValidationCellAllowCriteriaE12ListTest1;Test4


----------



## Fluff (Dec 21, 2022)

You could put your formula in (for instance) F12 & drag down & then refer to that for the DV
Fluff.xlsmABCDEFGHIJ12345678910TableATable B11A_IDChoose B_IDB_IDA_ID12511123Test1Test4Test151112313511124Test2Test5Test251112414 Test351112515Test451112316Test551112417Test6511125DataCell FormulasRangeFormulaF14,F12:G13F12=TRANSPOSE(FILTER($I$12:$I$17,$J$12:$J$17=D12,""))Dynamic array formulas.Cells with Data ValidationCellAllowCriteriaE12:E14List=F12#


----------



## ziza (Dec 21, 2022)

Hi, yes this works for the moment. Great. Thank you


----------



## Fluff (Dec 21, 2022)

You're welcome & thanks for the feedback.


----------

