How to Autopopulate information from several spreadsheets to one spreadsheet

[rtaw92]

Hi there,

I've created a workbook that's intended to track construction costs per sqft for various scopes of a home build.

Example :

[TABLE="width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]Labour
[/TD]
[TD]Materials
[/TD]
[TD]Total
[/TD]
[TD]Labour per sq.ft.
[/TD]
[TD]Materials per sq.ft.
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]General Requirements
[/TD]
[TD]2.00
[/TD]
[TD][/TD]
[TD]2.00
[/TD]
[TD]0.23
[/TD]
[TD][/TD]
[TD]0.23
[/TD]
[/TR]
[TR]
[TD]Site Prep
[/TD]
[TD][/TD]
[TD]3.00
[/TD]
[TD]3.00
[/TD]
[TD][/TD]
[TD]0.11
[/TD]
[TD]0.11
[/TD]
[/TR]
[TR]
[TD]Utilities
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Excavation
[/TD]
[TD]6.00
[/TD]
[TD][/TD]
[TD]6.00
[/TD]
[TD]0.14
[/TD]
[TD][/TD]
[TD]0.14
[/TD]
[/TR]
</tbody>[/TABLE]

For each house we are building, for each scope of work I enter in the Description (Ie. General requirements) with several jobs that are included. I simply input the cost of labour and/or materials for that item and I've formulated the cells to automatically calculate the total of labour & materials. Then I've also formulate the cells to auto calculate the per per sq.ft. for the given labour & materials separately and then automatically total that a well. The bottom of the spreadsheet also calculates the totals for all of the columns as well.

Anyways, what I am trying to do is add a reference worksheet that will give me an average price per sq.ft. of the work description for all of our projects. So if I have 3 project spreadsheets, I enter the costs for general requirements on each projects worksheet. On the reference page each project is a column, and I want the column to populate with the total per sq.ft. calculated in the last column of the projects spreadsheet for that given item (ie. general requirements).

This is meant to help us project costs for home building and also track any causes for discrepancies etc. Example of reference page below:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]Project 1 Price Per sq.ft.
[/TD]
[TD]Project 2 price per sq.ft.
[/TD]
[TD]Project 3 Price per sq.ft.
[/TD]
[TD]Price per sq.ft. Average
[/TD]
[/TR]
[TR]
[TD]General requirements
[/TD]
[TD]3.00[/TD]
[TD]2.00
[/TD]
[TD]1.00
[/TD]
[TD]2.00
[/TD]
[/TR]
[TR]
[TD]Site Prep
[/TD]
[TD]4.00
[/TD]
[TD]3.00
[/TD]
[TD]2.00
[/TD]
[TD]3.00
[/TD]
[/TR]
[TR]
[TD]Utilities
[/TD]
[TD]5.00
[/TD]
[TD]4.00
[/TD]
[TD]3.00
[/TD]
[TD]4.00
[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully that is clear enough. Anyways any help on this would be appreciated. I'm sure I could just go through and enter in forumlas to each cell on the reference page but there has to be an easier way that I don't know of with hopefully less room for error.

 

[LORDMARKS]

Hi, if your just trying to link sheets together, you just need to add the workbook name to the calculation. eg =[Book2]Sheet1!$B$5 this value will update when you open the summay sheet.

the quickest way I can suggest is to copy one of the sheets into your new summary book. Then set up a full import of all values, so on sheet1 B5=copiedsheet!$B$5, then do find replace "=" with "=[Book2]"

Hope that makes sense, if not, let me know

 

[rtaw92]

Hi, if your just trying to link sheets together, you just need to add the workbook name to the calculation. eg =[Book2]Sheet1!$B$5 this value will update when you open the summay sheet.

the quickest way I can suggest is to copy one of the sheets into your new summary book. Then set up a full import of all values, so on sheet1 B5=copiedsheet!$B$5, then do find replace "=" with "=[Book2]"

Hope that makes sense, if not, let me know

Hey I figured out the way that works best for me, and I think its what you said first. I used the formula ='Worksheet Name'!Cell number so in this case worksheet Project 1, cell E23 (where the total was calculated) is ='Project 1'!E23 (And so on for each worksheet and item) automatically updates to whatever update is in the cell E23, in case anyone else is wondering. Thanks for your help