How to make Excel not update formulas when a new row/column/cell is added

[dc16]

I would like to be able to have a table which I can update daily by adding a new column for that day. I want formulas that reference the table to keep referencing the same columns. For example, if I have a formula which references columns G to R (the last 12 days) and I insert a new column G for today (the columns are in reverse time order), I want it to still reference columns G to R, for the most recent 12 days, so the last day (13 days ago) falls out of the formula. In other words, I don't want the cell references in the formula to change at all. Is there a setting in Excel for this? I have tried using absolute references with "$" but it still changes to formula. I have searched for info about this but I haven't found anything. Perhaps I am not using the correct search terms.

Also, is there a way to do this for some formulas and not others?

Thanks.

 

[SanjayGMusafir]

We recently had similar question on the forum

You may check the thread below if it helps you in some way. Other option is using INDIRECT Function. You can find discussion on it in the same thread itself.

Cell references

Hi all, I think a simple one for most of you. I know if i put $ in a formula for cell reference, and how that works. is there a way of doing the opposite? i mean if cell A1 is looking at C1, but i then move column C's data out to column D, can i have A1 keep looking at whatever now falls...

www.mrexcel.com