Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas with relative references | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
How to use address from relative reference across worksheets? | Excel Worksheet Functions | |||
Copying a work sheet cell reference as relative not absolute? | Excel Discussion (Misc queries) | |||
Copying a Formula To Reference Multiple Worksheets | Excel Worksheet Functions |