+ Reply to Thread
Results 1 to 3 of 3

Shift of range in formula by 1 column when 1 new column is added

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    30

    Shift of range in formula by 1 column when 1 new column is added

    Good day,
    I have probably a common problem which I tried to solve with the offset formula but didn't succeed.
    I have a dataset which is updated each week by adding a new column before the last one.
    On the right there are formulas pointing to the last 3 columns respective the last 3 columns before the last 3 ones.
    This principle should be kept.
    This means: if I add a new column, these formulas should then shift by 1 column to the right and point again to the 3 last columns & 3 last columns before the last 3 ones. And so on, so dynamically whenever a new column is added.
    Thanks for any help.
    Best regards
    Heinz
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Shift of range in formula by 1 column when 1 new column is added

    I would strongly suggest re-evaluating your process for adding new data to the table.

    Yes, the formula can be adjusted to accomodate, but it will involve using the OFFSET function..Alot.. On every range in the formula.
    It will become very large and difficult to read, and not to mention horribly inefficient.

    Instead of INSERTING between columns AE and AF..
    Try inserting the column at the very beginning of the table, between C and D
    Then, COPY D to AF and paste it to C to AE
    Then, PASTE your new data to AF


    that said, if you must do it in the formula, here's how to do it for 1 range..
    Take the first range of the formula in AK6
    SUM(AD5:AF5)

    To make that always refer to the last 3 cells of the table, even after that formula moves to AL6
    SUM(OFFSET(AK6,0,-7,1,3))

    Remember, you'd have to do that to EVERY range in the formula.

  3. #3
    Registered User
    Join Date
    01-09-2015
    Location
    Germany
    MS-Off Ver
    MS Office 2010
    Posts
    30

    Re: Shift of range in formula by 1 column when 1 new column is added

    Dear Jonmo1,
    Thank you very much for your quick answer.
    It helped a lot.
    I agree also with your comments/advices but it looks that in this case I cannot rework the "functionning" of my sheets/database.
    Hence I implemented the formulas with your support and it works well.
    Thanks again a lot
    Best regards
    Heinz

+ 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. shift cells in column 2 spaces to right if specific text appears in that column
    By hosburgh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-13-2014, 12:46 PM
  2. [SOLVED] How to shift a sum range from one column to another depending on Month?
    By sbham in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-05-2013, 12:48 PM
  3. Delete first value and shift cells up when new cell gets added in a range (column)
    By nlazarov in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-17-2013, 08:34 AM
  4. Delete first value and shift cells up when new cell gets added in a range (column)
    By nlazarov in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-17-2013, 07:53 AM
  5. [SOLVED] Macro Sort column(s) with range that is continuously changing or being added to
    By russell.suereth in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2012, 12:01 PM
  6. [SOLVED] If I apply formula to my column can the cell be blank until data is added?
    By KELLIS in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-01-2012, 03:39 PM
  7. Replies: 1
    Last Post: 07-20-2012, 11:34 AM

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