Dependent dropdown lists with multiple words in the different sheets

Hello OzGrid,

I have a workbook, and trying to arrange a dependent drop down list to product name and company. Problem is that, product names (named ranges or groups? I don't know how I should call them) are placed in different sheets in the same workbook and they have multiple words. Addition to this, in the main sheet, product company and product name are placed in different cells. After a couple of hours, my work returned no success. I do need help. Thank you. Sample is in the attachment.

Quote from Carim

Hello again,

When it comes to issues to fix with Excel ... an auto-loading firearm is called a Machine Gun ... !!!

Hi Carim, I look forward to receiving your good news as usual.

Quote from Carim

Just had a quick look at your workbook ...

you will have to accept to redesign its basic structure ... not only to be in a position to easily build your dependent dropdown lists

... but also to avoid sowing the seeds of all your future problems ...

Okay. Of course, little tweaks are acceptable.

Quote from Carim

Quite honestly the sample file you have attached is way too vague to come up with any meaningful recommendation ...

Could you share a file which is closer to your real-life situation ...

Hello Carim, here is the file you requested. I tried to adapt some examples from different websites but none of them worked for me. Sample workbook is in the attachment and very simplified as real life situation.

Quote from Carim

Attached is your test file ... with the Basic Principle of Dependent DropDown Lists ...

Hope this will help

Hi Carim,

Thank you very much and much again. It works like a charm. You gave me what I needed. Case is solved.

Quote from Carim

Delighted ...

Thanks for your Thanks ... AND for the Like

Hello Carim,

Could you check the workbook what I did myself. It returns an error.

Quote from Carim

" It returns an error ..."

Because you have not followed the structure of the Test File ...

You should take some time to analyze the Test File in order to fully understand :

1. Why is there a second worksheet with all the references ...

2. Why have all the validation lists have been named ( with Ctrl F3 ... you can see the list)

3. Why the data validation lists are using these specific range names ...

4. How are the two dropdown lists becoming inter-dependent ...

5. Why the event macro clears the dependent-product cell ... while waiting for the selection of the company ...

The ' dependency link ' hinges on two elements : the way the ranges are named ...AND... the use of the INDIRECT function

Once, you take the time to demystify the process ...everything becomes clear and simple ...

Display More

Thank you Carim. I am on case.

Quote from bosco_yip

Maybe,

In I6 >> Data Validation >>

>> Allow : List

>> Source : =OFFSET($A$4,MATCH($H6,Company,0),2,,2)

Display More

Hello, bosco. It is useful solution. Let me apply the same way you do in my own sheet. Thank you very much.