SOLVED

How can i create 3 dependent drop down list with the 3rd drop down depending on 1st and 2nd list?

%3CLINGO-SUB%20id%3D%22lingo-sub-2379819%22%20slang%3D%22en-US%22%3EHow%20can%20i%20create%203%20dependent%20drop%20down%20list%20with%20the%203rd%20drop%20down%20depending%20on%201st%20and%202nd%20list%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379819%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20my%20list%20of%20data.%20For%20Dropdown3%2C%20I%20will%20want%20the%20dropdown%20to%20be%20dependent%20on%20BOTH%20Dropdown1%20and%20Dropdown2.%20Kindly%20note%20that%20some%20of%20the%20values%20within%20Order%20Information%2FShipping%20Information%20under%20Proforma%20Invoice%2FDraft%20Shipping%20docs%2FFinal%20Shipping%20docs%20can%20be%20the%20same%20and%20it%20can%20be%20different%20too%20.%20As%20such%20I%20will%20need%20the%203rd%20dropdown%20list%20to%20be%20dependent%20on%20BOTH%20Dropdown%201%20and%20Dropdown2%3C%2FP%3E%3CTABLE%20width%3D%22564%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22138%22%3EDropdown1%3C%2FTD%3E%3CTD%20width%3D%22142%22%3EDropdown2%3C%2FTD%3E%3CTD%20width%3D%22284%22%3EDropdown3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EProforma%20Invoice%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3ECustomer%20requirement%2Fbasic%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EProforma%20Invoice%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPayment%20terms%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EProforma%20Invoice%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPO%20number%20%2F%20Material%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EProforma%20Invoice%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPrice%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EProforma%20Invoice%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EQuantity%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EProforma%20Invoice%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EVendor%20information%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EProforma%20Invoice%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3EDate%20discrepancy%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3ECustomer%20requirement%2Fbasic%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPayment%20terms%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPO%20number%20%2F%20Material%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPrice%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EQuantity%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EVendor%20information%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3ECarton%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3EContainer%20information%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDraft%20shipping%20docs%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3EDate%20discrepancy%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3ECustomer%20requirement%2Fbasic%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPayment%20terms%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPO%20number%20%2F%20Material%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EPrice%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EQuantity%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EOrder%20information%3C%2FTD%3E%3CTD%3EVendor%20information%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3ECarton%20information%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3EContainer%20information%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3EDate%20discrepancy%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFinal%20shipping%20docs%3C%2FTD%3E%3CTD%3EShipping%20information%3C%2FTD%3E%3CTD%3EEEM%20-%20GAC%2FVSD%20accuracy%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381402%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20i%20create%203%20dependent%20drop%20down%20list%20with%20the%203rd%20drop%20down%20depending%20on%201st%20and%202nd%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061362%22%20target%3D%22_blank%22%3E%40CelineFoong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%3C%2FP%3E%0A%3CP%3ESelect%20C2%20on%20the%20Data%20Entry%20sheet%20and%20click%20Data%20Validation%20on%20the%20Data%20tab%20of%20the%20ribbon%20to%20see%20the%20formula%20I%20used.%20It%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DOFFSET(Lists!%24C%241%2CMATCH(1%2C(Lists!%24A%242%3A%24A%2427%3DA2)*(Lists!%24B%242%3A%24B%2427%3DB2)%2C0)%2C0%2CCOUNTIFS(Lists!%24A%242%3A%24A%2427%2CA2%2CLists!%24B%242%3A%24B%2427%2CB2)%2C1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2382241%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20i%20create%203%20dependent%20drop%20down%20list%20with%20the%203rd%20drop%20down%20depending%20on%201st%20and%202nd%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2382241%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20your%20great%20help%20and%20reply.%3C%2FP%3E%3CP%3EI%20tried%20at%20my%20end%20using%20the%20formula%20you%20provided%20and%20it%20works%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20great%20help%20%2C%20this%20formula%20really%20save%20alot%20of%20time%20in%20my%20work%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHappy%20Weekend%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECeline%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Here is my list of data. For Dropdown3, I will want the dropdown to be dependent on BOTH Dropdown1 and Dropdown2. Kindly note that some of the values within Order Information/Shipping Information under Proforma Invoice/Draft Shipping docs/Final Shipping docs can be the same and it can be different too . As such I will need the 3rd dropdown list to be dependent on BOTH Dropdown 1 and Dropdown2

Dropdown1Dropdown2Dropdown3
Proforma InvoiceOrder informationCustomer requirement/basic information 
Proforma InvoiceOrder informationPayment terms
Proforma InvoiceOrder informationPO number / Material information 
Proforma InvoiceOrder informationPrice
Proforma InvoiceOrder informationQuantity
Proforma InvoiceOrder informationVendor information
Proforma InvoiceShipping informationDate discrepancy
Draft shipping docsOrder informationCustomer requirement/basic information 
Draft shipping docsOrder informationPayment terms
Draft shipping docsOrder informationPO number / Material information 
Draft shipping docsOrder informationPrice
Draft shipping docsOrder informationQuantity
Draft shipping docsOrder informationVendor information
Draft shipping docsShipping informationCarton information 
Draft shipping docsShipping informationContainer information
Draft shipping docsShipping informationDate discrepancy
Final shipping docsOrder informationCustomer requirement/basic information 
Final shipping docsOrder informationPayment terms
Final shipping docsOrder informationPO number / Material information 
Final shipping docsOrder informationPrice
Final shipping docsOrder informationQuantity
Final shipping docsOrder informationVendor information
Final shipping docsShipping informationCarton information 
Final shipping docsShipping informationContainer information
Final shipping docsShipping informationDate discrepancy
Final shipping docsShipping informationEEM - GAC/VSD accuracy
2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@CelineFoong 

See the attached sample workbook.

Select C2 on the Data Entry sheet and click Data Validation on the Data tab of the ribbon to see the formula I used. It is

 

=OFFSET(Lists!$C$1,MATCH(1,(Lists!$A$2:$A$27=A2)*(Lists!$B$2:$B$27=B2),0),0,COUNTIFS(Lists!$A$2:$A$27,A2,Lists!$B$2:$B$27,B2),1)

@Hans Vogelaar 

Appreciate your great help and reply.

I tried at my end using the formula you provided and it works  

 

Thank you for the great help , this formula really save alot of time in my work

 

Happy Weekend

 

Celine