Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totalling sequential worksheets
I want to set up a generic formula that automatically looks for a particular
cell in the previous worksheet & adds 1 to the value. When I copy & paste the formula, it copies exactly, instead up updating to the previous worksheet, even when I try copying only the formatting or formula. Example: In worksheet January 13, 2008, the formula is: =SUM('Dec 9, 2008'!M2+1) & the value is 7 In the worksheet February 10, the formula is: =SUM('Jan 13, 2008'!M2+1) and the value is 8 When I create the new worksheet March 10, 2009, I copy the cell from the February worksheet & paste it into the March work sheet, it comes out as: =SUM('Jan 13, 2008'!M2+1) with a value of 8 I want it to come out as: =SUM('Feb 10, 2009'!M2+1) with a value of 9. And so on.... so each total value in cell M2 gets greater in increments of 1, & that it calculates it without me having to create the formula for that cell each time - because there are approximately 25 cells in each worksheet that need to be updated in this way. Anybody got a clue? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totalling sequential worksheets
This subroutine is copied from
http://www.exceltip.com/st/Add_value...Excel/516.html Function SumPreviousSheet(InputRange As Range) As Double ' adds the values in InputRange in the previous worksheet ' returns 0 if the function is used in the first worksheet Application.Volatile ' omit if not necessary SumPreviousSheet = 0 On Error Resume Next SumPreviousSheet = _ Application.WorksheetFunction.Sum(InputRange.Paren t.Previous.Range(InputRange.Address)) On Error GoTo 0 End Function Use is like this =SumPreviousSheet(M2)+1 New to VBA? VBA intro Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm By the way: you do mot need SUM when you are just adding two values So =SUM('Dec 9, 2008'!M2 + 1) can be written as ='Dec 9, 2008'!M2 + 1 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "KErin" wrote in message ... I want to set up a generic formula that automatically looks for a particular cell in the previous worksheet & adds 1 to the value. When I copy & paste the formula, it copies exactly, instead up updating to the previous worksheet, even when I try copying only the formatting or formula. Example: In worksheet January 13, 2008, the formula is: =SUM('Dec 9, 2008'!M2+1) & the value is 7 In the worksheet February 10, the formula is: =SUM('Jan 13, 2008'!M2+1) and the value is 8 When I create the new worksheet March 10, 2009, I copy the cell from the February worksheet & paste it into the March work sheet, it comes out as: =SUM('Jan 13, 2008'!M2+1) with a value of 8 I want it to come out as: =SUM('Feb 10, 2009'!M2+1) with a value of 9. And so on.... so each total value in cell M2 gets greater in increments of 1, & that it calculates it without me having to create the formula for that cell each time - because there are approximately 25 cells in each worksheet that need to be updated in this way. Anybody got a clue? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totalling Values From The Same Cell In 12 Worksheets Onto New Shee | Excel Worksheet Functions | |||
Totalling a group of worksheets | Excel Discussion (Misc queries) | |||
Totalling Criteria from Muliple Worksheets | Excel Discussion (Misc queries) | |||
Transfering and totalling corresponding data to other worksheets | Excel Discussion (Misc queries) | |||
Totalling Separate Worksheets | Excel Worksheet Functions |