Forum Discussion

eddgueLMX's avatar
eddgueLMX
Copper Contributor
Jan 20, 2022
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.

 

ArgentinaBiomac
ArgentinaEarly Crop
ArgentinaKingberry S.A
ArgentinaLA LOMA DEL ACONQUIJA
ArgentinaS.A Veracruz
BelgiumGreenyard Frozen
BelgiumPasfrost NV
BrazilAcai Macunaima
BrazilTropical Acai
CanadaSeenergy Foods
CanadaSouth Alder Farm

 

 

I'm using the following as the Data Validation List Function:  OFFSET('Supplier Ref'!$A$1#,MATCH(G2,INDEX('Supplier Ref'!$A$1#,,1),0)-1,1,COUNTIF(INDEX('Supplier Ref'!$A$1#,,1),G2),1),,1))

 

It's meant to filter supplier Data Validation List options based on the selection made in column G. Wat we are trying to achieve is to add the generic "No Supplier" option to the list independently of the contents of Column G.

 

So if Argentina entered in Column G the drop down in Column H should be:

Biomac
Early Crop
Kingberry S.A
LA LOMA DEL ACONQUIJA
S.A Veracruz
No Supplier

 

excel
  • Lorenzo's avatar
    Lorenzo
    Jan 21, 2022

    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

     

2 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 21, 2022

    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

     

Resources