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.

    Files

    • Book1.xlsx

      (10.32 kB, downloaded 174 times, last: )

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Hello again,


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

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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 ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • 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.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • 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 ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

    Files

    • Book2.xlsx

      (10.52 kB, downloaded 174 times, last: )

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

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


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Quote

    You gave me what I needed ...

    Delighted ...:):)


    Thanks for your Thanks ... AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Delighted ...:):)


    Thanks for your Thanks ... AND for the Like :thumbup:

    Hello Carim,


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

    Files

    • Book1.xlsx

      (9.06 kB, downloaded 171 times, last: )

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • " 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 ...

    ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Again ... as mentioned earlier ...


    Should you need further assistance ... and a tailor-made solution ...


    please share a file which is much closer to your real-life situation ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you Carim. I am on case.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

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

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Hello,


    Curious to know how the next episode unfolds ... and if the final outcome is up to your expectations ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)