Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Function
I have 12 different worksheets for each month in a year in one workbook. In
each month's worksheet, I want year-to-date figures up to that month. All the worksheets have the same fields in same cells. I prepare one worksheet with all data and formulas for one month. Then I name the worksheet "Jan" for January and copy that worksheet 11 times and rename them according to individual months. How can I insert a common formula that will work in every worksheet for getting the year-to-date figures in each month's worksheet? Thanks in advance for your time. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Function
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 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, 10 Mar 2008 16:46:00 -0700, Roger wrote: I have 12 different worksheets for each month in a year in one workbook. In each month's worksheet, I want year-to-date figures up to that month. All the worksheets have the same fields in same cells. I prepare one worksheet with all data and formulas for one month. Then I name the worksheet "Jan" for January and copy that worksheet 11 times and rename them according to individual months. How can I insert a common formula that will work in every worksheet for getting the year-to-date figures in each month's worksheet? Thanks in advance for your time. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Function
Let's say that in each sheet the monthly value is in B9 and we want the cumm
to date in B10. In Feb sheet in cell B10 enter: =B9+Jan!B10 In the Mar sheet in B10 enter: =B9+Feb!B10 etc. B10 builds cummulatively month-by-month -- Gary''s Student - gsnu200772 "Roger" wrote: I have 12 different worksheets for each month in a year in one workbook. In each month's worksheet, I want year-to-date figures up to that month. All the worksheets have the same fields in same cells. I prepare one worksheet with all data and formulas for one month. Then I name the worksheet "Jan" for January and copy that worksheet 11 times and rename them according to individual months. How can I insert a common formula that will work in every worksheet for getting the year-to-date figures in each month's worksheet? Thanks in advance for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |