Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
|
|||
|
|||
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:
|
#5
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking sheets into one major sheet? | Excel Worksheet Functions | |||
Need to look up value in another Sheet based on criteria in first sheet. | Excel Worksheet Functions | |||
data from one sheet to several in sequential order! | New Users to Excel | |||
Linking References from Multiple Sheets to One Summary Sheet | Setting up and Configuration of Excel | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |