Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
backtothesky
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.links
David McRitchie
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.links
backtothesky
 
Posts: n/a
Default 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
  #4   Report Post  
Junior Member
 
Posts: 1
Default

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?



Quote:
Originally Posted by David McRitchie
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
  #5   Report Post  
Posted to microsoft.public.excel.links
Bill Manville
 
Posts: n/a
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.links
Bill Manville
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking sheets into one major sheet? Ellymoo Excel Worksheet Functions 8 April 24th 06 11:22 AM
Need to look up value in another Sheet based on criteria in first sheet. Stacey Excel Worksheet Functions 1 March 28th 06 06:22 AM
data from one sheet to several in sequential order! firecord New Users to Excel 6 June 22nd 05 05:10 PM
Linking References from Multiple Sheets to One Summary Sheet Kim Setting up and Configuration of Excel 3 May 5th 05 04:56 PM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 06:57 PM


All times are GMT +1. The time now is 09:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"