Transfering a changing cell value progressively through a workbook

[Mike1558]

I am creating a payment application form in excel 2007 using windows 7. Each
worksheet represents one months invoice. Say I have a formula in "sheet 1/
cell Q7" that sums the total billed to date for a particular budget line
item. This value will be transfered to a the next months payment application
"sheet 2/ cell K7" this becomes the total amount of previous applications,
then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7,
and so on and so on, until the completion of the job. How do I acomplish
this.

 

[Gord Dibben]

If you're willing to use a User Defined Function this becomes quite
easy.......

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

Example of use......................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

 

[Mike1558]

I tried this and what I get in the cell that I enter PrevSheet(Q7)what shows
up in the cell is #REF!. I have been unable to make this produce a value.
What did I do wrong? I copied the UDF to vis basic like you discribed in the
previous email but there must be something that I did wrong.

Thanks

Mike

 

[Gord Dibben]

Only way I can get #REF! is if I enter the formula on first sheet.

There is no previous sheet in that case.


Gord