Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Update worksheet references as new worksheets are added
I have a workbook where I add a new worksheet each week. Each sheet contains
cells with formulas that reference the worksheet from the previous week. When I add a new worksheet, I copy the formulas from the previous week, but it continues to reference the week prior to that. I must manually update each reference, and it's getting old. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Update worksheet references as new worksheets are added
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 Mon, 12 Jan 2009 11:10:02 -0800, Rick wrote: I have a workbook where I add a new worksheet each week. Each sheet contains cells with formulas that reference the worksheet from the previous week. When I add a new worksheet, I copy the formulas from the previous week, but it continues to reference the week prior to that. I must manually update each reference, and it's getting old. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN EXCEL AUTO DATE ADDED WORKSHEETS? | Excel Worksheet Functions | |||
how do i auto update cell values after pasting linked references | Excel Worksheet Functions | |||
Cell references auto update when sorting | Excel Discussion (Misc queries) | |||
update consolidated sheet when data is added to worksheets | Excel Discussion (Misc queries) | |||
Adding a row to worksheet does not update cell references in another. | Excel Worksheet Functions |