Populate multiple cells using 2 corresponding drop down lists

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello
    I'm trying to create a worksheet that populates cells corresponding to 2 drop down lists.


    The list is a material list (it will contain many more rows that what are currently in there).

    I'd like to be able to select the material type from a list in the 1st column and the size of the material in the second column (which I've created using data validation) but I'd like the next 2 cells to be populated with the information corresponding to that specific type of material and size.

    I've named the data range and created the drop down lists but I don't know how to automatically populate the remaining 2 cells.

    Also is there a quicker way to update the Data Validation formula in column 2 (Size) (=Indirect(A24))
    than doing it manually for each cell. Like a Select the whole 2nd column and update all cells in it at once so the formula (=Indirect(A24)) will change the cell number to A25 in row 25 and A26 to row 26?

    Sample worksheet attached.


    Thanks,

    Vlad
    forum.ozgrid.com/index.php?attachment/37365/

    Re: Populate multiple cells using 2 corresponding drop down lists


    Vlad,

    Here is one solution to your issue:

    1. redefine your named ranges for W, HSS, and ANGLE to be the area that includes the drop down criteria for column two, and the other parameters. I have done so already for "W" in the attached spreadsheet. The data validation will still be driven off the first column - so no worries there.

    2. copy the VLOOKUP formulas I wrote as needed.

    As for your data validation problems:

    1. copy the cell with the validation you want to copy down.
    2. go to the cells you want the validation in.
    3. do the following: paste --> special --> validation.

    forum.ozgrid.com/index.php?attachment/37366/

    --Jeremy

    Re: Populate multiple cells using 2 corresponding drop down lists


    What you did is great but:

    1) the second column (Size) drop-down now contains not only the size but also the weight and the price options.

    2) If in the same row i want to select HSS in the first column (Material) and the size associated to HSS in the second column (size) it gives me an error of #REF!

    Could you please help me out on this one...

    P.S.-----This will be a bid material take off sheet so any of the rows could be any of the materials. One time I could choose a row to be HSS the next it could be a W, and the next it could be an Angle... so on and so on...

    Thanks.

    Vlad

    Re: Populate multiple cells using 2 corresponding drop down lists


    I figure it out.. Thank you Jeremy...

    Now if you select HSS or Angle in the first column and then the size of that material in the 2nd column both the weight/foot and the price reflect teh correct numbers based on data on Material Tab. :)

    Had to reconfigure my source doc a little, if interested on how this works I've attached a working file.

    Thanks,
    Vlad

    forum.ozgrid.com/index.php?attachment/37368/

    Re: Populate multiple cells using 2 corresponding drop down lists


    Thanks for posting your solution. Hope I was at least somewhat helpful to you.

    Regards,


    WidgetWonka
    Puuuureeee Imagination

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login