May 23 2021 09:51 PM
May 23 2021 09:51 PM
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
|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|
May 24 2021 10:50 AMSolution
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
May 24 2021 06:45 PM
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
Apr 05 2022 06:43 PM
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!
Apr 06 2022 03:04 AM
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.
Apr 06 2022 08:43 AM
Thanks for your help and reply.
I am looking not to use VBA.
Please have a look on the file and see if you can help me.
Apr 06 2022 12:58 PM
Unfortunately, we cannot use functions such as SORT or UNIQUE directly in data validation. I don't see a solution without using VBA.
Sep 05 2023 07:37 AM
How do I make Drop down 2 dependant on the selection of Drop down 1 and Drop down 3 dependant on the selections of both?
Thank you in advance
Sep 05 2023 07:56 AM
If to google "excel dependent drop down list" lot of posts just on first page, e.g. How to make dependent dropdown lists in Excel | Exceljet. You may find more suitable in your case and your Excel version.
Sep 06 2023 05:18 AM
@Sergei Baklan thanks! I couldn't get it working using any of the guides online! I think the problem was that my data has multiple words and spaces and is very complicated against basic one word just a few items in a lot of the examples. I found a way to reduce the data down and cross reference using the Define Names - labelling each section of data as the simple name so it matches.
It is a very over complicated way to do it and I am sure any updates to the data will break it - but it works for now. Thanks
Sep 06 2023 10:06 AM
In latest Excel it was improved, it ignores duplicates and blanks automatically. But all this is better to discuss on concrete sample if you have more questions.