Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Changing formulas to point to new column in budget

    I am working on budget files and while it's not that troublesome to change the formula's manually as we move from requested to proposed to adopted, etc., maybe someone knows of an easier way to do this?

    For example lets say all my formulas in my spreadsheets currently point to column M to get the requested budget amounts. What would be the easy way (if there is one) to have them point to column N when it changes to proposed?

    This change will happen 4 times in the budget process so it would be great to know of a short cut if there is one.

    Thanks in advance for your input.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    One quick thought would be to use find and replace if you can uniquely identify the M formulas.
    For example, could you search for $M$ and replace with $N$ ? Is that unique enough in the sheet?

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    DG,

    Here's a rudimentary sum formula that will use the column letter in E1 (RangeName: ColumnToSum) to determine which column to sum.
    Note the use of the Offset function to create the range. The last two arguments 5 & 1 where 5 is the number of rows to sum and 1 is the number of columns to include in the range (which will always be 1 in your example question).
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you have headers of Requested, Proposed, Adopted etc you could use a match formula to locate which column to refer to
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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