Append Additional Option Data Validation With Dynamic Arrays

[eddgueLMX]

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
Argentina	LA LOMA DEL ACONQUIJA
Argentina	S.A Veracruz
Belgium	Greenyard Frozen
Belgium	Pasfrost NV
Brazil	Acai Macunaima
Brazil	Tropical Acai
Canada	Seenergy Foods
Canada	South 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

[#2 · Accepted solution]

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

[#3]

@L z.Thank you! This worked perfectly!