Populating a list

[barbz11]

Hello, please could somebody help me.

I run an events planning business and I was wondering if it was possible to populate a prep list from an invoice?

For example, one of our centerpieces includes a variety of vases. 1 x XL, 3 x 50cm, 3 x 10.5" & 6 x 7.5". We call this an XL candle cluster.

I was hoping that is would be possible that if I put the number of XL candle clusters required for that event it would populate a list to tell me exactly how many of each vase I would require.

Thank you

[JohnTopley]

You need a table with the "Product" e.g. Candle Cluster in first column and in subsequent columns all the components (e.g Types of Vases) and corresponding quantities which are part of a "Product".

Can you put together a sample table as above and then attach your sample workbook: see yellow at top of page on how to attach a workbook.

[Richard Buttrey]

Please upload a workbook that shows

a) A table of all your candle clusters / centrepieces which shows how many different vases are needed for each and the size of each vase
b) Manually add a couple of examples of what you would enter as the number of clusters and EXACTLY what you would wish to see as a result. Make clear notes as to why you have arrived at the result.

i.e. we wish to see what you start with and what you want to see

[barbz11]

Hi John,

Thank you for a quick response. Please see my example of a few product we offer. Hope this is okay.

[barbz11]

Hi Richard,

Thank you for your quick response. I will work on something now to show you what I am after and upload it soon.

[JohnTopley]

See attached as an example (illustration of the general approach).

You will probably have the first table on a separate sheet to that of the "output" but such changes are easy to accommodate.

A1:K5 is the Product/Item table with Items as Column Headers

A8:K11 is sample output for Qty in Column B for Product in A

in C8

=$B8*INDEX($C$2:$K$5,MATCH($A8,$A$2:$A$5,0),COLUMNS($A$1:A$1))

Copy across and down.

Hope this helps.

[barbz11]

Hi Richard,

Sorry for the delay. Please see attached. I hope this helps you to see what i am after. Obviously, this is a shorter, less formatted version but that shouldn't change the initial formula required.

Many thank for your help

Emma

[JohnTopley]

In W6 and copy down

=SUMPRODUCT(($F$5:$S$8)*($F$4:$S$4=$V6)*($C$5:$C$8))

[barbz11]

Thanks John,

That's great but i wondering if i could be triggered somehow my the item name.

I probably should of been more clear.

Of course, each invoice will be different so what i'm thinking is that if you type 'XL Candle Cluster' it will find it in the Item List and the populate a picking list. Does this make sense? Sorry if i'm being a pain.

[JohnTopley]

I used a helper row 2 F:S: you can hide by setting font colour to White (as in attached)

in V6

=IFERROR(INDEX($F$4:$S$4,AGGREGATE(15,6,ROW($A$1:$A$50)/($F$2:$S$2="x"),1),ROWS($1:1)),"")

Copy down