Forum Discussion
Solved
Append Additional Option Data Validation With Dynamic Arrays
I'll try to summarize this as best i can. We are using the spill range below to populate a Data Validation List. Argentina Biomac Argentina Early Crop Argentina Kingberry S.A A...
Hi eddgueLMX
One way (D2:E12 simulates your current dynamic array):
1) Create another dynamic array (in G2# above) that lists the unique countries in one column and "No Supplier" next to each entry. Formula in G2:
=CHOOSE({1;2}, UNIQUE(INDEX(D2#,,1)), "No Supplier")2) Combine your current dynamic array with the one created above. Formula in J2:
=LET( dummyArr, D2#, noSupArr, G2#, dummyRows, ROWS(dummyArr), seqRows, SEQUENCE(dummyRows + ROWS(noSupArr)), SORT(IF(seqRows <= dummyRows, dummyArr, INDEX(noSupArr,seqRows-dummyRows,{1;2})),1) )3) Revise your Data Validation formula so it refers to J2#
Sample attached
Hi eddgueLMX
One way (D2:E12 simulates your current dynamic array):
1) Create another dynamic array (in G2# above) that lists the unique countries in one column and "No Supplier" next to each entry. Formula in G2:
=CHOOSE({1;2}, UNIQUE(INDEX(D2#,,1)), "No Supplier")2) Combine your current dynamic array with the one created above. Formula in J2:
=LET(
dummyArr, D2#,
noSupArr, G2#,
dummyRows, ROWS(dummyArr),
seqRows, SEQUENCE(dummyRows + ROWS(noSupArr)),
SORT(IF(seqRows <= dummyRows, dummyArr, INDEX(noSupArr,seqRows-dummyRows,{1;2})),1)
)3) Revise your Data Validation formula so it refers to J2#
Sample attached
