Forum Discussion
How can i create 3 dependent drop down list with the 3rd drop down depending on 1st and 2nd list?
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
| Dropdown1 | Dropdown2 | Dropdown3 |
| Proforma Invoice | Order information | Customer requirement/basic information |
| Proforma Invoice | Order information | Payment terms |
| Proforma Invoice | Order information | PO number / Material information |
| Proforma Invoice | Order information | Price |
| Proforma Invoice | Order information | Quantity |
| Proforma Invoice | Order information | Vendor information |
| Proforma Invoice | Shipping information | Date discrepancy |
| Draft shipping docs | Order information | Customer requirement/basic information |
| Draft shipping docs | Order information | Payment terms |
| Draft shipping docs | Order information | PO number / Material information |
| Draft shipping docs | Order information | Price |
| Draft shipping docs | Order information | Quantity |
| Draft shipping docs | Order information | Vendor information |
| Draft shipping docs | Shipping information | Carton information |
| Draft shipping docs | Shipping information | Container information |
| Draft shipping docs | Shipping information | Date discrepancy |
| Final shipping docs | Order information | Customer requirement/basic information |
| Final shipping docs | Order information | Payment terms |
| Final shipping docs | Order information | PO number / Material information |
| Final shipping docs | Order information | Price |
| Final shipping docs | Order information | Quantity |
| Final shipping docs | Order information | Vendor information |
| Final shipping docs | Shipping information | Carton information |
| Final shipping docs | Shipping information | Container information |
| Final shipping docs | Shipping information | Date discrepancy |
| Final shipping docs | Shipping information | EEM - GAC/VSD accuracy |
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)
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)
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
It looks like it is exactly I have been looking for, but for some reason your Dropdown3 is not working.
Could you please let me know how to fix it?
Thanks a lot!
It looks like data validation has to be refreshed when the workbook is opened.
The attached new version is now a macro-enabled workbook; you may have to allow macros when you open it. I hope this works for you.
