In the attached Jpeg I have a formula in cell B4, (=SUM($C$4+$D$4). By inserting a new column in Column C the formula will change to (=SUM($D$4+$E$4)
I want to be able to insert a new column in Column C but have the formula remain exactly as it was I.e. “(=SUM($C$4+$D$4)”
Any ideas?
Try with =SUM(OFFSET(B4;0;1):OFFSET(B4;0;2))
Thank you very much, this works great.
The “SUM” formula I used here was just for simplicity sake.
My real formula relies on the input of a date in cell “C1”, is there a way to fix the column portion of the function OFFSET(B4,0,1)
Hi Leroy,
Again, many thanks.
Why not:
=SUM(OFFSET(B4;0;1;1;2))
??
To fix an applicable range-address or cell-address against any insertions or deletions of cells/columns/rows the function INDIRECT() is the appropriate means: SUM(INDIRECT("C4:D4")) e.g.
Solutions using OFFSET() will adapt any occurring direct reference address if a column/row is inserted/deleted in front/above the respective cell. The user has to decide anyway what he(f/m) actually wants.
In the given example the referenced summands form a contiguous range, and the explicit “+” on the argument position of SUM() isn’t compatibel with a well considered usage of that function which is to ignore cells containing text.
I cannot see any difference between:
=SUM(OFFSET(B4;0;1):OFFSET(B4;0;2))
and
=SUM(OFFSET(B4;0;1;1;2))
Hi Lupp,
Thanks for the input. The OFFSET option fixed my problem.
However your reply with INDIRECT fixed another aspect of my formula. I needed to address a fixed reference cell. OFFSET does not help there.
So thank you very much.