Announcement

Collapse
No announcement yet.

Dependent dropdown, with data arranged in columns

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dependent dropdown, with data arranged in columns



    I have frequently used the "indirect" method for creating a second, dependent dropdown. This seems to require that the data are arranged like this with 1st level categories in the first row and the 2nd choices in the column below it.
    Fruits Vegetables Other
    Apples Carrot Meat
    Oranges Potato Bread
    Bananas Pizza
    This is all fine if you do not have many 1st level categories, but I have like +100 of these and currently my data is arranged like this. I will also have items that are present in more than one category
    Fruits Apples
    Fruits Oranges
    Fruits Bananas
    Vegetables Carrot
    Vegetables Potato
    Other Meat
    Other Bread
    Other Pizza
    Round fruits Apples
    Round fruits Oranges

    This is an example. My true data source is an AAO Query with bills of materials from SAP for several materials.. So column 1 is the material. Column 2 is the components used in the given material and then there's a 3rd column with the quantity for each component per material. That's also why it's not trivial (to not say impossible) to just arrange the data as shown in the first table.

    I need to provide the user with an easy way to select with an in cell drop down:

    1) The desired material
    2) in the cell next to it a list of components assigned to that material

    Finally I can with lookup find the quantiy of the component for the material.

    Putting it all in a standard table and just use filtering would not work as I need a lot of other stuff going on in the user interface part.

    Any good ideas?

  • #2
    hI Ninecows,
    Tables should be ok but an alternative use treeview
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

    Comment


    • #3
      or have another table with the sub assemblies - materials and collate and tally selections via dictionary
      If the solution helped please donate to RSPCA

      Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

      Comment


      • #4
        I've made up a dummy example
        The ribbon will show the assemblies available > click on an item in the Tree and then click add assemble button
        After adding a few assemblies > right click and use the context menu > top selection to generate the bill of materials
        Attached Files
        Last edited by pike; September 11th, 2019, 18:43. Reason: remove names from workbook
        If the solution helped please donate to RSPCA

        Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

        Comment


        • #5


          Originally posted by pike View Post
          I've made up a dummy example
          The ribbon will show the assemblies available > click on an item in the Tree and then click add assemble button
          After adding a few assemblies > right click and use the context menu > top selection to generate the bill of materials
          Hi pike :-)

          Thanks a lot for your efford, but I'm afraid it's waaaay too advanced.

          I might not have been totally clear or I have not understood the suggested solution. Sorry :-)

          I (and especially the users) will need two cells in a row. First cell you select from the list of materials (e.g. fruits/Vegetables/Other/Round fruits etc). The second cells drop down will then populate with the list of components that is within the selected material (e.g. select Fruits in cell 1 and cell 2 will the let you select between apple, orange and banana).

          I hope it make sense

          Comment

          Working...
          X