I am working on a Macbook pro using Microsoft excel for Mac 2011.
Hi there,
I have developed a simple construction estimating template consisting of a number of sheets.
The basic sheets are:
(1) A submission schedule on which I list the item number, as short description and the quantity required for the item. Each item line to its relevant pricing sheet (3) and brings forward the calculated rates. (I have this sheet working)
(2) A resources sheet in which all the resources required are listed and classified as materials, labour, plant etc etc etc
(3) Price build up sheets for each item listed in (1) above. I use data validation lists and vlookup formulas to bring data front the resources sheet and all works well. I have used a VBA formula that automatically names the sheet after the item number being prices. (I have got this sheet working well)
I have inserted tables into all the sheets and the pricing sheets has 3 tables namely,
My issue is that I would like to summarise each resource usage in the resources sheet.
When I use the following formula with specific cell references the resource usage calculates fine.
=SUMPRODUCT(SUMIF(INDIRECT("'"&table_item&"'!$A$34:$A$39"),A2,INDIRECT("'"&table_item&"'!$d$34:$d$39")))
the formula works fine while i only work with in the range of row 34 to 39. This is however not always the case as some item will need 3 rows to price and other many more.
I have tried the following formula using table and named ranges
=SUMPRODUCT(SUMIF(INDIRECT("'"&table_item&"'!Res_Description_Mats"),A2,INDIRECT("'"&table_item&"'!Res_Qty_Mats")))
the problem is however the formula on uses the qty's in the first tab and multiplies (or adds) the quantity by the number of items there are.
References
table_item = submission schedule (1) A24:A29
Res_Description_Mats = first pricing sheet (3) A33:A39
Res_Qty_Mats = first pricing sheet (3) D34:D39
The question is how do I get the second formula to look at all the pricing tabs lists in the "table_item"
I need the resources to summarise to use in the budget tab.
I have attached the file.
Thanks in advance.
Bookmarks