Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Michigan
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post

    How do I get a value from the previous monthly spreadsheet to add to the current month spreadsheet

    I'm using excel to track bike miles and several other things, on a monthly basis. Each new month I just copy and paste the entire spreadsheet for a new month and delete the entries to start the next month. One of the fields takes the previous month total and adds it to the current month total for a year-to-date figure, but every new month I have to change the previous month in the formula. Is there a way to make it automatically use the previous spreadsheet (month) instead of having to change it? Here is one of the formulas: =SUM(F4)+(OCT_2015!K6), so when I add a new spreadsheet and copy everything I have to change the OCT to NOV, wondering if there is a way to say "previous spreadsheet" in the formula instead?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    T.D.L.,

    Instead of creating a new workbook why not just create a new worksheet like this:
    Bike.JPG

    Now all you have to do is copy the last sheet, change the name and clear the entries. (This of course could be automated with VBA post back if interested).

    Note: the Total for the monthly sheets in in A1 and the sum range is A2:A32 so you don't have to worry about the number of days in the month.

    Test File: BikeMilesRG-1.xlsx

    HTH
    Last edited by RetiredGeek; 2015-12-03 at 11:32.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,186
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You could define one cell as the current month, enter the date and format it as month, then use that to calculate the name of the previous sheet. I'd use a second cell (outside the visible range) to hold the sheet name calculation otherwise the cell formulas will get out of hand.

    A2 is the current month.
    AA2 (Second Cell): =CHOOSE(MONTH(EDATE(A2, -1)),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug ","Sep","Oct","Nov","Dec") & "_" & YEAR(A2)
    Existing cell formula: =SUM(F4)+(AA2 & "!K6")

    Note: I've not tested this fully.

    cheers, Paul

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Michigan
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you, I'll give it a try.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Michigan
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you, I couldn't open the test file. I'm using an old suite of office, 03 I think, maybe that's why.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    T.D.L.,

    Here's a version that will work in Excel 2003.

    Test File: BikeMilesRG-1.xls

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Twodogs,

    Here is another solution. Place the following formula into the cell you want to carry the running total:

    =SUM(F4)+(INDIRECT(TEXT((MONTH(DATEVALUE(LEFT(MID( CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),3) & "1"))-1)*28, "mmm") & "_2015!K6"))

    It will look at the current sheet name (FEB_2015), extract the month (FEB), Find the previous month (JAN), and insert it into your formula (=SUM(F4)+(previous spreadsheet_2015!K6).

    When you copy the sheet, it will automatically pull from the previous month

    TwoDogs.png

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-12-05 at 01:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •