Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
relative references
I wish to create a sum formula that references the previous sheet in a
multiple worksheet workbook. I would then like to copy the formula to the next sheet and maintain the relative refrence to the new previous sheet. Is it possible to do this without having to change the reference in each sheet? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
relative references
Not really well without code, but it you put a formula somewhere in each
sheet that gets the prior sheets name and the location of the sum, you can reference the cell with an indirect. Something like this =MID(CELL("filename",Sheet1!A1),FIND("]",CELL("filename",A1))+1,256)&"!B2" Sheet1 being the prior sheet and B2 containing the value -- -John Please rate when your question is answered to help us and others know what is helpful. "Rob" wrote: I wish to create a sum formula that references the previous sheet in a multiple worksheet workbook. I would then like to copy the formula to the next sheet and maintain the relative refrence to the new previous sheet. Is it possible to do this without having to change the reference in each sheet? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
relative references
If you're willing to use a User Defined Function.......
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 Fri, 5 Dec 2008 04:12:00 -0800, Rob wrote: I wish to create a sum formula that references the previous sheet in a multiple worksheet workbook. I would then like to copy the formula to the next sheet and maintain the relative refrence to the new previous sheet. Is it possible to do this without having to change the reference in each sheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Relative references | Excel Worksheet Functions | |||
Relative references | Excel Discussion (Misc queries) | |||
Relative references | Excel Discussion (Misc queries) |