It will reference the cell on the previous sheet only.
But make it reference a cell on each previous sheet that accumulates from
sheet to sheet to sheet.
Gord
On Sun, 23 Nov 2008 21:08:01 -0800, Joe Lewis
<(E-Mail Removed)> wrote:
>Thank you so much. Will this only reference that cell for the previous
>worksheet, or will it reference that cell on all worksheets previous to the
>current one?
>
>The reason I ask, is that I want to have a "year-to-date" statistics section
>on each page that draws information from all previous worksheets to the
>current one.
>
>That way at the end of the year, I can go back through the pages and see the
>year-to-date stats changing over time.
>
>
>
>"Gord Dibben" wrote:
>
>> Copy/paste this UDF to a general module in your workbook.
>>
>> 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 usage...................
>>
>> 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 Sun, 23 Nov 2008 19:39:00 -0800, Joe Lewis
>> <(E-Mail Removed)> wrote:
>>
>> >I want to refence a cell in all previous sheets in a workbook. The problem is
>> >that I'd like to paste the formula to other sheets and still have it refernce
>> >all previous sheets to that sheet.
>> >
>> >For example, on sheet 5 the formula would refence cell A1 in sheets 1, 2, 3,
>> >and 4. When I copy and paste the formula to a cell in sheet 9, I want it to
>> >automatically expand the sheet range to now reference A1 in sheets 1, 2, 3,
>> >4, 5, 6, 7, and 8.
>> >
>> >Is this possible?
>>
>>
|
Similar Threads
