Hey Everyone,
I’m currently working on a material resource planning system. I’ve prepared a sheet where given the inputs on “Finished Goods Production Schedule” and “Raw Materials Needed for Each Kind of Finished Goods”; it generates “Raw Material Consumption Schedule”.
It’s pretty simple actually, I create two different pivots for these two tables and then do matrix multiplication of these two pivots on another sheet. I used to achieve the same results with “sumifs, sumproducts, etc.” before but you know how RAM-efficient that could be.
I couldn’t manage to turn get dynamic references from a pivot table to the “Matrix Multiplication” sheet. I tried with VBA, but I’d like to avoid adding macro here if possible.
Now, I want to go further and peek if powerpivot can offer a more elegant solution. Here's the link for a simplified version of the sheet.
https://www.dropbox.com/s/u39xwp1qrf0etex/mrexcel help 1.xlsx?dl=0
Please note that the columns have duplicate values in production schedule. The original sheet is much, much larger and includes other types of calculations (regarding purchase, capacity planning, forecast, location management etc.) built up on “Raw Material Consumption Schedule”. What I’m willing to achieve, is to get the “Raw Material Consumption Schedule” (presented in the last sheet) directly from the tables in first two sheets.
And of course, I’m lower than a newbie in excel/pivot/powerpivot. It’s just been a few months since I’ve started excel. Please bear with my noobness haha. I’m looking for a push towards right direction.
Cheers.
I’m currently working on a material resource planning system. I’ve prepared a sheet where given the inputs on “Finished Goods Production Schedule” and “Raw Materials Needed for Each Kind of Finished Goods”; it generates “Raw Material Consumption Schedule”.
It’s pretty simple actually, I create two different pivots for these two tables and then do matrix multiplication of these two pivots on another sheet. I used to achieve the same results with “sumifs, sumproducts, etc.” before but you know how RAM-efficient that could be.
I couldn’t manage to turn get dynamic references from a pivot table to the “Matrix Multiplication” sheet. I tried with VBA, but I’d like to avoid adding macro here if possible.
Now, I want to go further and peek if powerpivot can offer a more elegant solution. Here's the link for a simplified version of the sheet.
https://www.dropbox.com/s/u39xwp1qrf0etex/mrexcel help 1.xlsx?dl=0
Please note that the columns have duplicate values in production schedule. The original sheet is much, much larger and includes other types of calculations (regarding purchase, capacity planning, forecast, location management etc.) built up on “Raw Material Consumption Schedule”. What I’m willing to achieve, is to get the “Raw Material Consumption Schedule” (presented in the last sheet) directly from the tables in first two sheets.
And of course, I’m lower than a newbie in excel/pivot/powerpivot. It’s just been a few months since I’ve started excel. Please bear with my noobness haha. I’m looking for a push towards right direction.
Cheers.
