1. ## 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. 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

3. 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. Thank you, I'll give it a try.

5. 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. T.D.L.,

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

Test File: BikeMilesRG-1.xls

HTH

7. 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

#### Posting Permissions

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