Linking from one sheet to another.
 

Can I to get a total from every sheet in a workbook into the last sheet,
while having Excel automatically update when a new sheet is added to the
workbook?

Example: I've got 4 sheets with an amount in cell V3, and I want the
total of all of the V3's to show up in a specific cell on a fifth
sheet. If I add another sheet containing a V3 value, can Excel add that
automatically to the formula on the total sheet? Or do I have to
manually change the formula every time a new sheet is added?


--
backtothesky

Linking from one sheet to another.
 

You can use a 3-D reference, which you can find in your Excel help.

So far you have sheet1, sheet2, sheet3, and sheet4

You can create a sheet0 before sheet1, and
sheet99 after sheet4 -- as seen physically on the worksheet tabs.

in your summary sheet:

V3: =SUM(sheet0:sheet99!V3)

insert any new sheets anywhere between sheet0 and sheet99
The names of the sheets does not matter, as long as you address
The SUM is actually inclusive, so if you had anything in V3 on the end sheets
it would be included in the SUM.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"backtothesky" wrote in message
m...

Can I to get a total from every sheet in a workbook into the last sheet,
while having Excel automatically update when a new sheet is added to the
workbook?

Example: I've got 4 sheets with an amount in cell V3, and I want the
total of all of the V3's to show up in a specific cell on a fifth
sheet. If I add another sheet containing a V3 value, can Excel add that
automatically to the formula on the total sheet? Or do I have to
manually change the formula every time a new sheet is added?


--
backtothesky

Linking from one sheet to another.
 

I tried to use this formula, with a sheet named "PF Track W.E. #1" as my
first sheet and one named "PF Track W.E. #2" as the last sheet. It comes
back with a "#NAME?" error.

The exact equation I had is "=SUM('PF Track W.E. #1':'PF Track W.E.
#2'!V3)".

Is this not working because of an error in my equation, or is it due to
the names of the sheets (i.e., spaces or periods)?

I appreciate the help thus far.



David McRitchie Wrote:
You can use a 3-D reference, which you can find in your Excel help.

So far you have sheet1, sheet2, sheet3, and sheet4

You can create a sheet0 before sheet1, and
sheet99 after sheet4 -- as seen physically on the worksheet tabs.

in your summary sheet:

V3: =SUM(sheet0:sheet99!V3)

insert any new sheets anywhere between sheet0 and sheet99
The names of the sheets does not matter, as long as you address
The SUM is actually inclusive, so if you had anything in V3 on the end
sheets
it would be included in the SUM.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"backtothesky" wrote in
message
m...

Can I to get a total from every sheet in a workbook into the last
sheet,
while having Excel automatically update when a new sheet is added to
the
workbook?

Example: I've got 4 sheets with an amount in cell V3, and I want the
total of all of the V3's to show up in a specific cell on a fifth
sheet. If I add another sheet containing a V3 value, can Excel add
that
automatically to the formula on the total sheet? Or do I have to
manually change the formula every time a new sheet is added?


--
backtothesky


--
backtothesky

I am currently using 3D references in a similar situation. I am encountering update problems with the links. My file is over 9MB (which is considered small for the work I do). If I move a sheet into the range area (to follow your example, between sheet0 and sheet99), the links stop updating. Ditto if i copy a sheet that resides within the range area.

Is there a known issue between file size and the functionality of 3D References?

Linking from one sheet to another.
 

"=SUM('PF Track W.E. #1:PF Track W.E. #2'!V3)"

The sheet names and the colon need to be within the '..'

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Linking from one sheet to another.
 

Presumably you are in automatic calculation mode?
Which version of Excel?
Can you give us an example of a formula that fails to update after you
have moved/copied a sheet?
Does Ctrl+Alt+F9 help?
Or Edit / Replace / = / with: = / Replace All ?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup