Articles

How keep running total between excel sheets

August 24, 2010 at 22:10:07
Specs: Windows XP

I need to be able to keep a running total of lieu time. Each sheet is a weekly time log with a specific cell assigned for lieu time. I need the next sheet to be able to recognize the value of the week before and add it to the cell on that sheet. Eg: A2 on sheet 1 (total lieu up to Aug. 14) is added to A1 and sheet 2 (total of lieu that week) with the result showing in A2 of sheet 2 (total lieu up to Aug. 21).

I've tried inputing in A2 on sheet 2: ='sheet 1'!A2+'sheet 2'!A1
which works but how do i get the result without having to change the formula on each sheet.


See More: How keep running total between excel sheets

Report •


#1
August 25, 2010 at 07:11:29

Hi,

If you put this formula into A2 on sheet 2:
='sheet 1'!A2+A1

when you copy it to sheet 3, cell A2, you will only have to change the 1 to 2
='sheet 2'!A2+A1

There are ways of creating worksheet/cell addresses using formulas but IMHO it's going to be more time consuming than just making a single edit each week.

I did create a custom function to do this:

Public Function LastLieu(CellAddress As String)
Application.Volatile
Dim strWsThis As String
Dim strWsLast As String

'get this worksheets name
strWsThis = Application.ThisCell.Worksheet.Name
'convert it to last worksheet name based on Sheet 2 -> Sheet 1
strWsLast = "Sheet " & Format(CInt(Right(strWsThis, Len(strWsThis)  _
- InStr(1, "Sheet ", strWsThis) - 5)) - 1, "#0")
'get value from cell passed to this function in last worksheet
LastLieu = Worksheets(strWsLast).Range(CellAddress).Value
End Function

This would have to be entered in a new standard module in the visual basic section of the workbook.
In cell A2 on each workbook enter =LastLieu("A2")

The function will fail if you name your worksheets differently.
You used "sheet 1" which is not the default naming for Excel worksheets.
If you used "Sheet1" etc. which is the Excel default format the custom function will need to be edited.

Regards


Report •
Related Solutions


Ask Question