+ Reply to Thread
Results 1 to 2 of 2

Help with changing multiple =SUM column references

  1. #1
    Registered User
    Join Date
    06-16-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    1

    Help with changing multiple =SUM column references

    Hi - wondering if someone can help me.

    I have a financial spreadsheet where I have monthly results, and every month I make a copy of the previous sheet and then fill in that month's "actual" vs budget column.

    I have one column which looks at a tab containing 12 columns of LAST YEAR's results, and for each monthly tab I want to show the "YEAR TO DATE" info from last year.

    So each of those cells references the other worksheet tab and is a =SUM('LAST YEAR'!E13:i13) where E13 is "January" and then the second reference is to the same column for the month I'm in....in this case I13 gets me to January->May in that reference tab.

    So next month when I copy the sheet and then turn it into "June", I have been having to go down that column and hit F2 and delete "I" and type "J" into each formula so it will then be referencing 6 columns from the reference sheet.

    Is there a way that I can set up the =SUM formula to pick off the column reference from a single text cell somewhere....so I can just change "I" to "J" on that sheet and then all the formulas will then pick off =SUM E:J.......

    I would just change it once and then use the mouse to fill down, but some of the cells are taking just 1 row from the other sheet and others are taking 2 rows and combining, so if I copy the top formula all the way then I lose those differences.

    Any ideas?

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Help with changing multiple =SUM column references

    Hi chrisober


    You can use defined names in your sum formula OR,
    indirect function to reference the correct month..

    Upload the sample workbook in case of any issues.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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