A cell reference in a formula on Sheet 2 points to cell A1 on Sheet 1. I want to be able to insert new data into cell A1, shifting it and its contents down BUT have the formula on Sheet 2 still point to cell A1. Now, whether I use a relative or absolute reference in the formula, it always shifts to point to A2.
I’d appreciate any help.
I disagree that the question is either irrelevant or outdated, as I found the reply quite useful. However, it might be appropriate to close the question because it has been adequately answered.
It may be not tghe best idea to insert new date above old ones inserting a new row every time. You would gethave the same issue with a formual in B1 referencing to, say A5 in the same sheet when inserting a new row below the first and above the fifth one. It’s the wa y spreadsheets try to keep references.
If you urgently want to to make the address persistent, you need to use the INDIRECT function like in =INDIRECT(“Sheet1.A1”). Now you won’t get any adaption, of course. Moving the target cell or renaming its sheet will cause the formula to point wrong or to return a #REF! error…
Lupp, thanks for your response to my question. The calculations in Sheet 2 and others are intended to be based on a block of data on Sheet 1 representing certain occurrences on the most recent 100 days. Each day a new row of data is entered at the top of the block and anything beyond the 100th row at the bottom of the block is ignored. If the addresses in the calculations are persistent, then nothing has to change in any of the other sheets. At least, that’s the idea. I’ll try =INDIRECT().