Data Validation and Filter Function

[ziza]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2			Dropdown Should be like
3
4				511123	Test1
5					Test4
6
7
8
9
10			TableA					Table B
11				A_ID	Choose B_ID				B_ID	A_ID
12				511123		Dropdown Hardcoded	=FILTER(t_B[B_ID];t_B[A_ID]=D12) in Data Validation not working		Test1	511123
13				511124					Test2	511124
14				511125					Test3	511125
15									Test4	511123
16									Test5	511124
17									Test6	511125
18
19
Tabelle1

Cell Formulas
Range	Formula
E4:E5	E4	=FILTER(t_B[B_ID],t_B[A_ID]=D4)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E12	List	Test1;Test4

 

[Fluff]

You could put your formula in (for instance) F12 & drag down & then refer to that for the DV

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6
7
8
9
10			TableA					Table B
11				A_ID	Choose B_ID				B_ID	A_ID
12				511123		Test1	Test4		Test1	511123
13				511124		Test2	Test5		Test2	511124
14									Test3	511125
15									Test4	511123
16									Test5	511124
17									Test6	511125
Data

Cell Formulas
Range	Formula
F14,F12:G13	F12	=TRANSPOSE(FILTER($I$12:$I$17,$J$12:$J$17=D12,""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E12:E14	List	=F12#

 

[ziza]

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

 

[Fluff]

You're welcome & thanks for the feedback.