copying relative reference across worksheets
 

I want to set up a financial report with a separate worksheet for each month.
I will have column of receipts & expenses for the month and columns of
receipts & expenses for YTD. The YTD will be computed by adding this months
entry to the total from the previous month. When I copy the formulas they
copy as an absolute reference so always refer to January. I could do this
with Quattro but haven't found how to do it with Excel.

copying relative reference across worksheets
 

Hi,
Your formula has $A$, remove the $ and you are ready to go, if this not
solve your problem please show your formula. thanks

"MrPhysics" wrote:

I want to set up a financial report with a separate worksheet for each month.
I will have column of receipts & expenses for the month and columns of
receipts & expenses for YTD. The YTD will be computed by adding this months
entry to the total from the previous month. When I copy the formulas they
copy as an absolute reference so always refer to January. I could do this
with Quattro but haven't found how to do it with Excel.

copying relative reference across worksheets
 

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

On Wed, 1 Apr 2009 11:02:15 -0700, MrPhysics
wrote:

I want to set up a financial report with a separate worksheet for each month.
I will have column of receipts & expenses for the month and columns of
receipts & expenses for YTD. The YTD will be computed by adding this months
entry to the total from the previous month. When I copy the formulas they
copy as an absolute reference so always refer to January. I could do this
with Quattro but haven't found how to do it with Excel.