Dynamic dependent Validation List based in multiple tables and formulas

Hi,

I've tried a few ways to make this work (using dependent drop-downs), but I haven't come even near to a solution, and would be grateful for any insight. The ideal solution should be based in formulas, to avoid using macro-enabled files altogether.

You'll find a sample file attached with two structured tables, PROPOSALS and DOCS.

1st Table, in the first Sheet, "PROPOSALS" have a unique index column (first one, "PROPOSAL_ID") that may not be sorted at all times and have two related fields "CUSTOMER_ID" and "AGREED":

PROPOSAL_ID (unique text string, first column)	CUSTOMER_ID	AGREED (YES/NO)
1	C	YES
2	B	YES
3	A	YES
4	A	YES
5	B	NO
6	A	YES
7	A	NO
8	C	NO
9	C	YES
10	B	YES
11	A	NO

2nd Table, in the second Sheet, "DOCS" also has a unique index column ("ID_DOC" - not the first as I can't move it, but I could add a new first column with a referenced copy, if necessary), a related field with the "CUSTOMER_ID" (relates to the first table) and a second related field ("RELATED PROPOSAL") that should populate with a validation list that lists "PROPOSAL_ID"'s of the "CUSTOMER_ID" on the left and that are marked with "AGREED" in the "PROPOSALS" table.

DOC_DATE	DOC_ID (unique, not first column)	CUSTOMER_ID	RELATED PROPOSAL (dependent dropdown Validation List with the entries below)
(date)	222XYZ	A	(3, 4 and 6)
(date)	333XYZ	B	(2 and 10)
(date)	444XYZ	D	("No Proposals")
(date)	555XYZ	A	(3, 4 and 6)
(date)	888XYZ	C	(1 and 9)

Thank you in advance for any input

This solution uses an Array Formula: =IFERROR(LEFT(SUBSTITUTE(CONCAT(IF(PROPOSALS[CUSTOMER_ID]=[@[CUSTOMER_ID]],PROPOSALS[PROPOSAL_ID]&",",FALSE)),FALSE,""),LEN(SUBSTITUTE(CONCAT(IF(PROPOSALS[CUSTOMER_ID]=[@[CUSTOMER_ID]],PROPOSALS[PROPOSAL_ID]&",",FALSE)),FALSE,""))-1),"No Proposal")

The "derivation" of this formula is shown on the Proposals sheet. =SUBSTITUTE(CONCAT(IF(PROPOSALS[CUSTOMER_ID]=G3,PROPOSALS[PROPOSAL_ID]&",",FALSE)),FALSE,"")

The formula looks at the customer ID and considers only those rows where the ID is equal to cell G3. I "add" a comma to to the row so I have some sort of a delimiter. The results come out as a mix of FALSE and matching IDs. So I use the substitute command to eliminate FALSE.

The result has a trailing comma, so the LEFT(X,Len(X)-1) takes care of that.

Finally, if there are no matches the formula yields #VALUE! Wrapping it in IFERROR takes care of that.

OK, here's a version that uses "joined" pivot tables.