I have been setting up a workbook to act as estimate tool for building different items.The Details for making different items are in one table. I have assigned each detail a unique number 0-100. Each Item has a default of 20 rows where i can enter material and cost for that material.
Detail Table
a,b,c,d
1: Detail#,Material,Qty, Cost
2:1,2X4x8 lumbar,4,$10
3:1,1x4 lumbar,2,$5
21:2,4x8x1/2 OSB,2,$50
22:2,1x4 lumbar,1,$5
The details are then rolled up in another table with one row of information for each detail showing total material from the detail table. This table Detail List also totals the number of times the Detail has been allotted to be used in the estimate. The Qty used of each detail comes from multiple Job tables
Detail List Table
a,b,c,d
1:Detail#,Cost,QTY USED
2:1,$15,3
3:2,$55,2
The Estimate Is completed on the JOBS Table. There are multiple job tables in which I enter a detail# then the Number of the detail to allot to the Job. The table totals cost off all the detail i have allotted to the Job. The Detail List Table sum's the QTY USED of each Detail from these Job tables.
JOB Table(There are multiple)
a,b,c,d
1:Detail#,QTY,Total Cost
2:1,2,$30
3:2,3,$165
I want to be able to generate the total of each Material Type that has been used in each Job Table. I have stumbled upon relationships and power pivot in my new version on excel but i am a complete beginner with respect to this. Can my request be answer using these new tools? I have completed this in an old version of this workbook i created but the formula is a combination of sum, sumif and sumproduct that is 4555 characters long. I have about 1000 different material types and 20 Job Tables. That gives me 20,000 cells with that large formula. So the workbook takes a couple of min to open and a couple of min to calculate each time i change a value.
I appreciate anyone that can take a stab and send me in the right direction.
Detail Table
a,b,c,d
1: Detail#,Material,Qty, Cost
2:1,2X4x8 lumbar,4,$10
3:1,1x4 lumbar,2,$5
21:2,4x8x1/2 OSB,2,$50
22:2,1x4 lumbar,1,$5
The details are then rolled up in another table with one row of information for each detail showing total material from the detail table. This table Detail List also totals the number of times the Detail has been allotted to be used in the estimate. The Qty used of each detail comes from multiple Job tables
Detail List Table
a,b,c,d
1:Detail#,Cost,QTY USED
2:1,$15,3
3:2,$55,2
The Estimate Is completed on the JOBS Table. There are multiple job tables in which I enter a detail# then the Number of the detail to allot to the Job. The table totals cost off all the detail i have allotted to the Job. The Detail List Table sum's the QTY USED of each Detail from these Job tables.
JOB Table(There are multiple)
a,b,c,d
1:Detail#,QTY,Total Cost
2:1,2,$30
3:2,3,$165
I want to be able to generate the total of each Material Type that has been used in each Job Table. I have stumbled upon relationships and power pivot in my new version on excel but i am a complete beginner with respect to this. Can my request be answer using these new tools? I have completed this in an old version of this workbook i created but the formula is a combination of sum, sumif and sumproduct that is 4555 characters long. I have about 1000 different material types and 20 Job Tables. That gives me 20,000 cells with that large formula. So the workbook takes a couple of min to open and a couple of min to calculate each time i change a value.
I appreciate anyone that can take a stab and send me in the right direction.
