Master sheet question

[rfcomm2k]

I have a large spreadsheet with 5 worksheets in it used for estimating jobs.

The first worksheet is where I input all the parameters for the jobs. This changes from customer to customer, and is called the Tasks worksheet.

The next two sheets are pricing sheets, one for the local area and the other for pricing in another time zone where I also have an office.

The 4th sheet is the main project sheet that has all the formulas to calculate how much material and labor is needed.

The last sheet is a summary page that breaks out the project by trade (framing, drywall, insulation, etc).

If I have several jobs I am working on bidding and I make changes to either the pricing sheets, project sheet, or both, then I need to make those changes in every workbook for current projects. This is cumbersome. I have been looking for a way to have a single "project master" workbook that I can maintain changes in, and then just adding in the tasks sheet for a particular job and saving it as that job. Then reopen the master workbook, paste in the tasks sheet from another job, and save that job, etc.

My attempts have failed because every time I copy and past the contents of a tasks sheet, the formulas all change to indicate where those task items came from. i.e. the formula could read "=IF(AND('[project estimating master.xls]Tasks'!$B$8="y",'[project estimating master.xls]Tasks'!$B$9=4,'[project estimating master.xls]Tasks'!$B$10>0,'[project estimating master.xls]Tasks'!$B$11>96,'[project estimating master.xls]Tasks'!$B$11<121),ROUNDUP(((('[project estimating master.xls]Tasks'!$B$10/96)*2)+('[project estimating master.xls]Tasks'!$B$10/16)-('[project estimating master.xls]Tasks'!$B$15/84/12))+1,0),0)"

This really makes troubleshooting a tedious problem. If I could figure out how to update ALL the project files by making changes to jut one file I would be very happy.

Any suggestions?

[Richard Buttrey]

Hi,

If you can upload the workbook we'll be able to forma better picture and advise accordingly. Suffice to say you have the right idea in wanting a single sheet with normalised data records since this would simplify analysis considerably.

[rfcomm2k]

Attachment 372502

I had this previously uploaded. I do not remember if it was accurate to the current version or not, and I cannot seem to be able to delete it so I can upload a replacement. Furthermore, the replacement file is 1.92MB in size so it exceeds the limit anyway.

[FDibbins]

I dont think your attachment made it

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[rfcomm2k]

Well, I cannot upload the file. I tried using a file that was previously attached but apparently it is missing now. Even though it still shows in the list of uploaded files when I go to manage files.

And I cannot upload an update because it has grown to 1.9MB and I cannot delete enough of it to downsize it without ruining the integrity of the formulas.

[rfcomm2k]

And now I stand corrected. Apparently the "missing" file was not missing after all.

[Richard Buttrey]

Hi,

You mention 5 sheets yet there are only 3 in the attachment

[rfcomm2k]

Hi,

You mention 5 sheets yet there are only 3 in the attachment

The file with 5 sheets is 1.9MB in size and I cannot upload it. The sample sheet was the only way I could downsize it enough to upload it.

Also, as previously stated, the attached sheet is a previous version and there were numerous changes made since. However, for the purposes of the original question it should be more than adequate.