Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
running-total formulas written from one spreadsheet to another
Is there a way to automatically transfer running-total formulas written from
one spreadsheet to another, that automatically updates the worksheet formulas from the previous sheet to the next? For example, if sheet 2 has reference formulas (running totals) that carry from sheet 1, can I start sheet 3 to automatically inherit the same formulas but automatically reference to sheet 2, that referenced a running total from sheet 1, without having to go through each cell and rewrite the formula to correspond to the current page (sheet 3) and previous page (sheet 2)? I hope I've explained this clearly. I am working on spreadsheets that have numerous, running-total formulas and it would be a pain to have to go through and rewrite each one. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
running-total formulas written from one spreadsheet to another
Neumeister
Copy/paste this User Defined Function to a general module in your workbook. Function PrevSheet(rg As Range) N = Application.Caller.Parent.Index If N = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(N - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(N - 1).Range(rg.Address).Value End If End Function 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. Tip.......you can group several sheets and enter the formula in first sheet and all sheets will have the formula entered, referring to its previous sheet. If not familiar with VBA and macros see David McRitchie's site. http://www.mvps.org/dmcritchie/excel/getstarted.htm Gord Dibben Excel MVP On Wed, 8 Dec 2004 13:33:04 -0800, "Neumeister" wrote: Is there a way to automatically transfer running-total formulas written from one spreadsheet to another, that automatically updates the worksheet formulas from the previous sheet to the next? For example, if sheet 2 has reference formulas (running totals) that carry from sheet 1, can I start sheet 3 to automatically inherit the same formulas but automatically reference to sheet 2, that referenced a running total from sheet 1, without having to go through each cell and rewrite the formula to correspond to the current page (sheet 3) and previous page (sheet 2)? I hope I've explained this clearly. I am working on spreadsheets that have numerous, running-total formulas and it would be a pain to have to go through and rewrite each one. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
running-total formulas written from one spreadsheet to another
Thank you, but what is, exactly, "a general module?" I'm not clear on where
to paste your info below. "Gord Dibben" wrote: Neumeister Copy/paste this User Defined Function to a general module in your workbook. Function PrevSheet(rg As Range) N = Application.Caller.Parent.Index If N = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(N - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(N - 1).Range(rg.Address).Value End If End Function 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. Tip.......you can group several sheets and enter the formula in first sheet and all sheets will have the formula entered, referring to its previous sheet. If not familiar with VBA and macros see David McRitchie's site. http://www.mvps.org/dmcritchie/excel/getstarted.htm Gord Dibben Excel MVP On Wed, 8 Dec 2004 13:33:04 -0800, "Neumeister" wrote: Is there a way to automatically transfer running-total formulas written from one spreadsheet to another, that automatically updates the worksheet formulas from the previous sheet to the next? For example, if sheet 2 has reference formulas (running totals) that carry from sheet 1, can I start sheet 3 to automatically inherit the same formulas but automatically reference to sheet 2, that referenced a running total from sheet 1, without having to go through each cell and rewrite the formula to correspond to the current page (sheet 3) and previous page (sheet 2)? I hope I've explained this clearly. I am working on spreadsheets that have numerous, running-total formulas and it would be a pain to have to go through and rewrite each one. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
running-total formulas written from one spreadsheet to another
There are code modules that belong to worksheets, ThisWorkbook, class modules
and some that are for general use. Open your workbook then hit alt-f11 to get to the VBE (where the macros live) then hit ctrl-r to see the project explorer then select your project then Insert|module and paste Gord's code there. Then back to excel (alt-f11 again) and test it out. reds&bengals wrote: Thank you, but what is, exactly, "a general module?" I'm not clear on where to paste your info below. "Gord Dibben" wrote: Neumeister Copy/paste this User Defined Function to a general module in your workbook. Function PrevSheet(rg As Range) N = Application.Caller.Parent.Index If N = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(N - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(N - 1).Range(rg.Address).Value End If End Function 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. Tip.......you can group several sheets and enter the formula in first sheet and all sheets will have the formula entered, referring to its previous sheet. If not familiar with VBA and macros see David McRitchie's site. http://www.mvps.org/dmcritchie/excel/getstarted.htm Gord Dibben Excel MVP On Wed, 8 Dec 2004 13:33:04 -0800, "Neumeister" wrote: Is there a way to automatically transfer running-total formulas written from one spreadsheet to another, that automatically updates the worksheet formulas from the previous sheet to the next? For example, if sheet 2 has reference formulas (running totals) that carry from sheet 1, can I start sheet 3 to automatically inherit the same formulas but automatically reference to sheet 2, that referenced a running total from sheet 1, without having to go through each cell and rewrite the formula to correspond to the current page (sheet 3) and previous page (sheet 2)? I hope I've explained this clearly. I am working on spreadsheets that have numerous, running-total formulas and it would be a pain to have to go through and rewrite each one. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Over-written formulas | Excel Discussion (Misc queries) | |||
How do I input these formulas into cells that I want written in? | Excel Worksheet Functions | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
pivot table formulas - running total | Excel Discussion (Misc queries) | |||
Keeping formulas on running total and sorts | Excel Worksheet Functions |