Prevent cell references from changing after inserting rows or columns

[tmaddison]

Hi,

I feel stupid posting this because I once knew the answer, but right now I can't even figure out what to call this to do a proper Google search for that answer.

Here's the situation. Let's say I have a worksheet with two tabs - DATA and SUMMARY.

In the DATA tab I have some data in cell A1.

In the SUMMARY tab, cell A1 is populated with =Data!$A$1. I always want what is in DATA!A1 to be displayed in SUMMARY!A1.

Now lets say I insert a row in the DATA tab BEFORE the current row 1. DATA!A1 is now empty, the entry that used to be in DATA!A1 is now in DATA!A2.

But I DO NOT want the formula in SUMMARY!A1 to change to refer to DATA!A2, I want it to continue to point to DATA!A1.

Despite using absolute references, if I insert a row before the existing data, Excel always "helpfully" changes the reference to continue to point to the same bit of data, which is now in DATA!A2

I remember dealing with this once long ago. I'd swear fixing it had something to do with an "Options" setting that could be toggled on or off, with the default being "on".

Now, I've searched and can't find it.

Am I imagining things, did that not ever exist, or am I just missing it?

Thanks,

Todd

 

[arthurbr]

Try

=INDIRECT("Data!$A$1")

 

[kvsrinivasamurthy]

Two options. Both in A1 and copied down.
Passive formula.

=INDIRECT("'DATA'!A"& ROW())

Normal formula

=INDEX(DATA!$A:$A,ROW())