+ Reply to Thread
Results 1 to 4 of 4

RANGE reference needed to SUM the previous & current month's columns in a table formula

  1. #1
    Registered User
    Join Date
    09-04-2014
    Location
    London
    MS-Off Ver
    2013
    Posts
    6

    RANGE reference needed to SUM the previous & current month's columns in a table formula

    This issue comes from an annual project finance tracker that should update Actuals & Forecast totals month-by-month.

    Every month I need to:
    Confirm my Actuals (money spent in the month just ended)
    Update my Forecasts (money that I think will come out in the months ahead)

    In my table I have:
    A column with the Actuals total
    A column with the Forecast total
    Twelve columns, one for each month (when the month ends I overwrite what was a forecast figure with the actual).

    At the moment I am updating the column references in the formula manually but have 10 tables per project and currently 8 projects! Which is 160 updates to make as I have to increment the actual total column and decrement the forecast total column each month. I've mocked this up in the attached file.

    To save myself so many manual and error-prone updates I would like to control it from a single drop-down menu (also a named range). In longhand this would say:

    I have selected the current month to be April from the drop-down list. Therefore calculate the Actual total as columns January to April in the current row, and calculate the Forecast total from the May to December columns in the current row.


    Any help to get this nailed will be absolutely smashing!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: RANGE reference needed to SUM the previous & current month's columns in a table formul

    In C16, use the formula:
    =SUM(OFFSET([@Jan],0,0,1,MATCH(ranCurrentMonth,$F$2:$F$13,FALSE)))

    In D16, use the formula:
    =SUM(OFFSET([@Jan],0,MATCH(ranCurrentMonth,$F$2:$F$13,FALSE),1,12-MATCH(ranCurrentMonth,$F$2:$F$13,FALSE)))

    Or, simpler:
    =SUM(E16:P16)-[@Actual]

    To update all the tables, you should name F2:F13 with a global name, and make sure ranCurrentMonth is also global.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-04-2014
    Location
    London
    MS-Off Ver
    2013
    Posts
    6

    Re: RANGE reference needed to SUM the previous & current month's columns in a table formul

    Thank you Bernie, I spent all of Saturday fiddling with this in my main workbook, added some IF statements and so on, and it has done several things.

    - Transformed the way I work with it and report against the data
    - Closed off the final nagging issue I've got in my budgeting
    - Saved me, I would estimate, up-to four hours per month with edits to the current period, checking and cross-checking

    So a very heartfelt thanks, that's a life-changing bit of help!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: RANGE reference needed to SUM the previous & current month's columns in a table formul

    I don't know about life changing but I'm always grateful to hear that I've been of help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help Needed: Formula Dealing with Dates/Current Month
    By IAMROBBERMAN in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-08-2019, 11:22 AM
  2. SUM IF FORMULA BASED ON CURRENT/PREVIOUS/NEXT Month
    By AK123 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-21-2016, 01:10 PM
  3. [SOLVED] Dynamic formula to calculate the difference between current and previous month
    By kay007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2015, 11:19 AM
  4. [SOLVED] Formula issue for Current/Previous month Date
    By hecgroups in forum Excel General
    Replies: 3
    Last Post: 03-01-2014, 06:41 AM
  5. [SOLVED] Formula for amount on previous month based on current month
    By Yu Marquez in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-21-2014, 12:42 AM
  6. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  7. Formula needed for COUNTIF in current month
    By atomickitty in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-02-2012, 03:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1