Hello,
I am having trouble figuring out a formula that needs to determine how many units of a "sub part" are needed to assemble a "final product" for a foretasted period of time using data from two tables.
Both tables are coming from a pivot table, so they will be dynamic and from what I believe, there is no way to consolidate or merge them. And so a formula is needed to sum the values in a third table.
The first table would be the "final product" forecasts, with the final product number on the y axis and the month it is expected to sell on the x axis. The corresponding columns would show how much of each part is expected to sell in a given month.
The second table would indicate what "sub-parts" are needed to assemble one "final product". On the y axis we would have the "final product" number and on the x axis there would be the "sub parts" needed to assemble one final part. In the screenshot example, to make one unit of Final Product A, you would need 10 units of Sub Part A and 5 parts of Sub Part B etc.
I believe a SUMPRODUCT formula is needed, but I can't get anything close to what I am looking for, so hoping I can get some help here. Ideally the output would be a table that has the forecasted "sub parts" needed and split by month on the X axis. The screenshot is a simplistic version of the data I am working with, the real application will have tens of thousands of sub parts and hundreds of final products. The data would always be dynamic as the information is being pulled from a database.
Any help at all would be very much appreciated, I feel like I am missing a big but simple piece of the puzzle so hopefully it can be done. Thank you in advance.
I am having trouble figuring out a formula that needs to determine how many units of a "sub part" are needed to assemble a "final product" for a foretasted period of time using data from two tables.
Both tables are coming from a pivot table, so they will be dynamic and from what I believe, there is no way to consolidate or merge them. And so a formula is needed to sum the values in a third table.
The first table would be the "final product" forecasts, with the final product number on the y axis and the month it is expected to sell on the x axis. The corresponding columns would show how much of each part is expected to sell in a given month.
The second table would indicate what "sub-parts" are needed to assemble one "final product". On the y axis we would have the "final product" number and on the x axis there would be the "sub parts" needed to assemble one final part. In the screenshot example, to make one unit of Final Product A, you would need 10 units of Sub Part A and 5 parts of Sub Part B etc.
I believe a SUMPRODUCT formula is needed, but I can't get anything close to what I am looking for, so hoping I can get some help here. Ideally the output would be a table that has the forecasted "sub parts" needed and split by month on the X axis. The screenshot is a simplistic version of the data I am working with, the real application will have tens of thousands of sub parts and hundreds of final products. The data would always be dynamic as the information is being pulled from a database.
Any help at all would be very much appreciated, I feel like I am missing a big but simple piece of the puzzle so hopefully it can be done. Thank you in advance.