Hello,
I would be really grateful if somebody could help me with a formula which I believe should be a combination of INDEX MATCH and SUMPRODUCT but I may be wrong
I have three spreadsheets:
The first spreadsheet is Bill of Material (BOM) - products are in row and semi-products in column.
The second spreadsheet shows how many pallets of products I will dispatch on a particular day. Products are in row again, dates in column.
The third spreadsheet should be the outcome. Semi-products are in row, dates are in column. And I need to combine both spreadsheets into this one.
I need to know how many semi-products I will need to prepare for a dispatch day of a product
For example 18.1. I will dispatch 3 pallets of 12117. It means I will need to prepare for that day 102000 2712 pcs (=904*3) and 103000 2682 pcs (=894*3).
18.1. I will also dispatch 5 pallets of 12076. It means I will also need to prepare 101000 4245 pcs (=849*5) and 102000 4520 pcs (=904*5)
=> row in the third spreadsheet with date 18.1. will have these values: the cell F39 is 4245, the cell G39 is 7232 pcs (= 2712+4520) and the cell H39 is 2682
In other words, I need to return a value to the third spreadsheet according to semi-products, how many of them I have to prepare (to complete requested products) and what day.
I'm totally clueless here.
Thanks for any help
Michaela
I would be really grateful if somebody could help me with a formula which I believe should be a combination of INDEX MATCH and SUMPRODUCT but I may be wrong
I have three spreadsheets:
The first spreadsheet is Bill of Material (BOM) - products are in row and semi-products in column.
The second spreadsheet shows how many pallets of products I will dispatch on a particular day. Products are in row again, dates in column.
The third spreadsheet should be the outcome. Semi-products are in row, dates are in column. And I need to combine both spreadsheets into this one.
I need to know how many semi-products I will need to prepare for a dispatch day of a product
For example 18.1. I will dispatch 3 pallets of 12117. It means I will need to prepare for that day 102000 2712 pcs (=904*3) and 103000 2682 pcs (=894*3).
18.1. I will also dispatch 5 pallets of 12076. It means I will also need to prepare 101000 4245 pcs (=849*5) and 102000 4520 pcs (=904*5)
=> row in the third spreadsheet with date 18.1. will have these values: the cell F39 is 4245, the cell G39 is 7232 pcs (= 2712+4520) and the cell H39 is 2682
In other words, I need to return a value to the third spreadsheet according to semi-products, how many of them I have to prepare (to complete requested products) and what day.
I'm totally clueless here.
Thanks for any help
Michaela
