Help needed: VBA coding for creating dependent dropdowns based on multiple criteria

I require assistance in creating two difficult dropdowns using VBA coding.

The dropdowns will be created in the A2 and B2 cells of the Sheet 2 worksheet. The B2 dropdown will be dependent on the A2 dropdown value.

FOR A2 DROPDOWN:

If the first criteria is met, the A2 value list will include "All", "Zone-7_Jodhpur", and "Zone-7_Pali"

First Criteria:
if Sec Dump worksheet's Col S Unique Value (except header row) is exactly matched in count and text both with these values "Matoshree (Phalodi)","Shree Giriraj Ji Agency (Jaisalmer)","Sohan Communication Private Limited (Jodhpur-4)","Vijay Trading Company (Jodhpur-1)","Fair Deal (Jodhpur-3)","Gaurav Marketing (Sirohi)","Dhruv Enterprises (Sanchor)","Global System(Pali)","Maa Kripa Marketing (Barmer)","Adarsh Infratek (Jalore)"

If the second criteria is met, the A2 value list will include "All", and "Zone-7_Jodhpur"

Second Criteria (will only work if the first is not met):
if Sec Dump worksheet's Col S Unique Value (except header row) matches any text from these values "Matoshree (Phalodi)","Shree Giriraj Ji Agency (Jaisalmer)","Sohan Communication Private Limited (Jodhpur-4)","Vijay Trading Company (Jodhpur-1)","Fair Deal (Jodhpur-3)"


If the third criteria is met, the A2 value list will include "All", and "Zone-7_Pali"

Third Criteria (will only work if the first and second are not met):
if Sec Dump worksheet's Col S Unique Value (except header row) matches any text from these values "Gaurav Marketing (Sirohi)","Dhruv Enterprises (Sanchor)","Global System(Pali)","Maa Kripa Marketing (Barmer)","Adarsh Infratek (Jalore)"

FOR B2 DROPDOWN:
if A2 selection result is "All" then B2 list will show these values will be automatically fetch all values based on Sec Dump Col S (eg. "All", "Matoshree (Phalodi)","Shree Giriraj Ji Agency (Jaisalmer)","Sohan Communication Private Limited (Jodhpur-4)","Vijay Trading Company (Jodhpur-1)","Fair Deal (Jodhpur-3)","Gaurav Marketing (Sirohi)","Dhruv Enterprises (Sanchor)","Global System(Pali)","Maa Kripa Marketing (Barmer)","Adarsh Infratek (Jalore)") "All" will be added additionally at the top


if A2 selection result is "Zone-7_Jodhpur" then B2 list will show from these values "Matoshree (Phalodi)","Shree Giriraj Ji Agency (Jaisalmer)","Sohan Communication Private Limited (Jodhpur-4)","Vijay Trading Company (Jodhpur-1)","Fair Deal (Jodhpur-3)" (if any unique value found from these, means if 2 or 3 value find then only those value will be show in B2)) "All" will be added additionally at the top

if A2 selection result is "Zone-7_Pali" then B2 list will show from these values "Gaurav Marketing (Sirohi)","Dhruv Enterprises (Sanchor)","Global System(Pali)","Maa Kripa Marketing (Barmer)","Adarsh Infratek (Jalore)" (if any unique value found from these, means if 2 or 3 value find then only those value will be show in B2) "All" will be added additionally at the top

If I understand this correctly, the current file meets the first criteria so the other criteria need not be considered again UNLESS data is deleted from the file which causes a criteria change.

It appears a very complex solution rather simply having all selections based on criteria A where B is simply selected on A (as the others are just sub-sets).

so "Zone-7_Jodhpur" is an extract of "Zone 7" and "Jodpur - n" where n is 1,3,4

See attached using Advanced Filter

Please Login or Register  to view this content.

@JohnTopley Thank you for your response to this post. You're right that the file I shared as a sample workbook contains all the values that match criteria-1. However, I mentioned different criteria because the data will be split for sharing with different stakeholders, and both dropdown values will be updated accordingly.

I have uploaded the format in which I want to apply dropdowns. I have applied two dropdowns on cell O2 and cell P2. The dropdown result in P2 is dependent on the value selected in O2. Currently, these dropdowns are created based on the additional worksheet "Calc," but I do not want to include it when I share the split data with my stakeholders. That is why, I want the Result value in P2 to be automatically updated from the "Sec Dump" sheet (the available value in the sheet).

The attached has an updated Data Validation list. "*" is "All", "Jodhpur*" for all "Jodhpur".

You could limit this list to a stakeholder need and hide/password protect the sheet containing any lists e.g Sheet1.and you could add the "Receiving Agent" as another filter.

Hide the "Calc" sheet and password protect it: keep it simple!

Indeed, the final option it is

If it is, please mark as "SOLVED" ("Thread Tools" at top of page)