dimanche 8 août 2021

Data Validation with IF statement

I'm trying to create a data validation with multiple conditions. On this Spreadsheet,

I have data validation on both column "J" and "K" Sheet1. The value of column K, will depend on the current value of column J. It does work on the first row, but on the succeeding rows, it does not, since my code for them still relies on the value of the first row, in this case, J4. Here's my code on sheet2(G1):

=if('1_Procurement Plan_Final'!J4="1.0 Goods",unique(B2:B),if('1_Procurement Plan_Final'!J4="2.0 Services",unique(C2:C),if('1_Procurement Plan_Final'!J4="3.0 Construction",unique(D2:D),if('1_Procurement Plan_Final'!J4="4.0 Lease and Rentals",unique(E2:E),if('1_Procurement Plan_Final'!J4="5.0 Others",unique(F2:F))))))

Please guide? Can I use array for this? Unfortunately I've been trying arrays but it always ends up in error.

Aucun commentaire:

Enregistrer un commentaire