View original (French)

Dynamic cascading dropdown list without named range and no macro

Kaly_CCM Posted messages 2 Status Member -  
via55 Posted messages 14391 Registration date   Status Member Last intervention   -
Hello everyone,

I have a problem creating a dynamic cascading drop-down list without named ranges.
I've gone through the forums and I haven't found what I'm looking for without using VBA/macro code.

In fact, I have a database of suppliers and sites. A supplier can have several different sites.

My goal is to be able to retrieve the list (drop-down list) of sites corresponding to a selected supplier (drop-down list). This, of course, by using data from the database.

Given the number of suppliers, it's not possible to create multiple named ranges with the corresponding sites.

Without resorting to macros, can someone help me with this?

I'd be happy to attach a prototype of the file to better understand my expectations. But, since this is my first post, I'm not sure how to proceed. I hope the screenshot is sufficient.

Thanks in advance for the help.



Configuration: Windows / Edge 16.16299

9 answers

  1.
    via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Hello

    An example to adapt to your needs
    https://mon-partage.fr/f/5sXU1TK7/

    If you can't, upload your file to the same sharing site, create a link that you copy and come back to paste it here

    Regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1
    1. Kaly_CCM Posted messages 2 Status Member
       
      Hello via55,

      Thank you for your example.

      It perfectly answers my request.

      Best regards,
      0
  2. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello to you,

    I have a similar problem but my data is not organized in the same way.
    I have partially succeeded but I have an error in one of my two formulas.
    With this link, an example of my workbook:

    https://www.cjoint.com/c/IFvmkUt6NyW

    Thank you in advance for your help.
    Best regards
    Mistral
    0
  3. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Hello,

    In the data validation of G9, replace the source =Souscat with the formula:
    OFFSET(Categories!$A2,,MATCH($E$9,Categories!$A$1:$M$1,0)-1,COUNTA(OFFSET(Categories!$A2:$A10,,MATCH($E$9,Categories!$A$1:$M$1,0)-1)))

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  4. Mistral_13200 Posted messages 649 Status Member 4
     
    Thank you for your response, but it doesn't work.

    To test your formula, I entered it into a cell, starting with the equals sign =, and I got a #VALUE! error instead, whereas I expected to find the first value of the selected category in E9.
    0
  6. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Re

    Yes, of course, the = that was missing when typing is needed and it works
    Your file with the formula in data validation
    https://mon-partage.fr/f/ND9qgde1/

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  7. Mistral_13200 Posted messages 649 Status Member 4
     
    PERFECT !!!

    I just added 20 at the end of the formula to limit to 20 lines below each category.

    One question: is there a way to clear cell G9 when we change the choice in E9?
    0
  8. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Why limit?? The formula takes into account the number of values in the range to limit the length to only the available values

    Clearing G9 when changing E9 is only possible through a macro

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  9. Mistral_13200 Posted messages 649 Status Member 4
     
    I limit myself to avoid having lists that are too long, and with 20, I’m still very broad.

    For the macro, I suppose it’s with a Worksheet_Change that we need to do it.
    I will move forward with my project, and if you allow me, I might come back to you eventually for this macro.
    For this reason, I leave the subject open.

    In any case, thank you very much for your help.
    Mistral
    0
  10. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Yes, perfectly with Worksheet_Change
    Ok, come back whenever you want for the rest

    --
    "Imagination is more important than knowledge." A. Einstein
    0