formula problem in grouped worksheets
Excel 2003
I have 99 tabbed worksheets in one workbook. All 99 have the same template. Each template collects a weeks worth of data and calculates a year to date column. I have grouped sheets 2-99 and on the active sheet 2 entered the following formula. =+Sheet1!E13+Sheet2!D13 Sheet1 E13 being the previous week year to date. Sheet2 D13 being this weeks total. Problem: The formula in all sheets 3-99 always looks at the data in Sheet1 and Sheet2 as if the formula read =+$Sheet1$E13+$Sheet2$D13. How do you make it quit. |
formula problem in grouped worksheets
Sounds like you want a progressive accumulation across sheets.
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 Mon, 17 Aug 2009 16:47:01 -0700, Bo wrote: Excel 2003 I have 99 tabbed worksheets in one workbook. All 99 have the same template. Each template collects a weeks worth of data and calculates a year to date column. I have grouped sheets 2-99 and on the active sheet 2 entered the following formula. =+Sheet1!E13+Sheet2!D13 Sheet1 E13 being the previous week year to date. Sheet2 D13 being this weeks total. Problem: The formula in all sheets 3-99 always looks at the data in Sheet1 and Sheet2 as if the formula read =+$Sheet1$E13+$Sheet2$D13. How do you make it quit. |
All times are GMT +1. The time now is 02:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com