Linking a new worksheet to an existing worksheet in a book

[Diana Coplen]

I have a workbook with 11 worksheets. Ten of those are
linked to the first which is the total of the other 10. I
have added a 12th worksheet and copied the formulas to
that worksheet. Now I need to be able to add the results
of sheet 12 to sheet 1 which also holds the totals of 10
other sheets so that sheet 1 is now the total of the other
11 sheets.

Thank you in advance for your help
Diana Coplen

 

[Kevin Stecyk]

Hi Diana,

I find the Data | Consolidate to be very helpful.

You specifiy what you want to have added together, and it sums the values
together.

You can also use a 3 D reference, as in

=SUM(Sheet2:Sheet12!A2:A5)

I hope that helps.

Regards,
Kevin

 

[Felipe]

Diana,

In sheet1 you can copy all the formulas refering to, say,
sheet10 to the column or row near them, if the cell
references are absolute (i.e. have $ signs) all you have
to do is select the range of the new formulas and use
replace (ctrl+H) and replace sheet10 with sheet11.

Example
If J1=Sheet10!$A$1
copy it to K1 and use Replace to end up with
K1=Sheet11!$A$1

Note, this will only work if:
1. Cell references are absolute, otherwise you will have
to make them absolute.
2. The new sheet (sheet11) has the same layout as Sheet10.

Regards,
Felipe