pick items from a list and total them

  • A friend has asked for help. I know the long way of doing this but there must be a simpler and faster way.


    She makes up packs of Lego bricks for schools. They have names such as Early Years Pack 1, Early Years Pack 2 etc. Her Sheet1 lists the packs on Row 1 so 'Early Years Pack 1' is in cell B1
    That pack has various bricks which are listed on rows 2-53, column A but this pack doesn't use bricks from all rows. Example:


    [TABLE="width: 303"]

    [tr]


    [td]

    Bricks

    [/td]


    [td]

    Early Years Pack 1

    [/td]


    [/tr]


    [tr]


    [td]

    black 1x2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    blue1x2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    yellow1x2

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    red1x2

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    white1x2

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    orange1x2

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    green1x2

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    lime1x2

    [/td]


    [td]


    [/td]


    [/tr]


    [/TABLE]

    She needs to have a data entry form on Sheet2 where she can insert the number of each pack required against the pack name so she knows what to order from her supplier. So if she wants 2x 'Early Years Pack 1' her order list will show black 1x2: 4, blue 1x2: 4 and yellow 1x2: 14 etc.
    I could of course just use an if statement to calculate from a drop down list: =IF($B$1="Early Years Pack 1",(B2*$J$2),0) (J2 contains the pack quantity needed) but feel there should be a better way. I hope someone can help.


    Thanks, Simon

  • Re: pick items from a list and total them


    You can use Index/Match.


    Example:


    [COLOR="#0000FF"]=INDEX('Sheet1'!$B$2:$F$10,ROWS($A$2:$A2),MATCH($B$1,'Sheet1'!$B$1:$F$1,0))*$J$2[/COLOR]


    copied down


    Where name of database sheet is Sheet1, and table occupies B2:F10 with headers in B1:F1 and Brick list in A1:A10.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: pick items from a list and total them


    That's great. I did something similar to this a couple of years ago in a Google sheet but got made redundant and lost access to all my work!
    Thanks for your help.
    Got it working on all data and works perfectly.8-)