Automatically add a column to summary sheet if another worksheet i

L

Living Design

I am using a pretty simple spreadsheet. One worksheet (estimate 1)
calculates a job cost by multiplying hours x labor rates. There are some
text boxes where I can enter notes etc. In a separate sheet (summary) I
summarize all the data from "estimate 1" by setting cells in "summary" = to
specific cells in "estimate 1"

A very simple transfer of data.... First Column is the Categories/Headings
and 2nd column is the data from "estimate 1"

Often, we are asked to estimate several jobs for the same client. To do
this, I make as many copies of "estimate1" (before I add data) as I need for
the number of projects we are estimating and name them "estimate 2, estimate
3 etc...."

What I would like to see happen is that for each copy of "estimate 1", I
would like a corresponding column to summarize the data in the sheet
"Summary".

Any ideas?
 
D

Dennis Tucker

If you can ensure the following things happen in your "Non-Summary"
worksheets, this will work.

Make sure that all data that is pulled from these "Non-Summary" worksheets
is located in the same RELATIVE location on each sheet. This means for the
data that is pulled to the SUMMARY sheet, it needs to be put in cells(Like
A100:A200) on each "Non-Summary" worksheet.

Then do as you normally do with the first project/estimate. On the summary
sheet, make the cell function like =Sheet2!$A100, =Sheet2!$A101,
=Sheet2!$A102 and so on. Make sure to use the $ symbols to lock in the
column.
Then copy and paste the formulas on the summary(I.e. Column A) to Column B.
Select all of the new formulas in Column B and run the search and replace
utility to replace "Sheet2" with "Sheet3"
Repeat any steps needed.


Dennis
 
L

Living Design

Short of the columns automatically adding themselves each time there is a new
sheet, this is the shortcut I was looking for. Thanks Dennis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top