running-total formulas written from one spreadsheet to another

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

Thank you, but what is, exactly, "a general module?" I'm not clear on where
to paste your info below.

 

[Dave Peterson]

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.