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

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

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

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. ## Re: Shift of range in formula by 1 column when 1 new column is added

Dear Jonmo1,
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

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

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