I have a cell A1 which = 5
I have a second cell B1 which =A1
I have a third cell C1 which = $A$1

When I insert a cell above A1, so its data becomes situated on A2, I would
expect B1 to track it, because it doesn't have the $ so that B1 = A2. I
would expect C1 to remain pointed at A1, because of the $ symbol, so C1 =
$A$1 as before.

In fact this isn't what happens. They both point to A2 after the insert.
This is very inconvenient in the context of a particular spreadsheet I'm
working with. I know I can work around it by changing C1 to the value $$$=A1
and then changing it back again after the insert, but that creates an
undesired side effect elsewhere.

Is there any setting or formula that will stop C1 tracking A1 after an
insert and keep it pointed at A1 irrespective?

Many thanks

On Sun, 28 Dec 2008 11:18:24 -0000, "teepee"
wrote:

I have a cell A1 which = 5
I have a second cell B1 which =A1
I have a third cell C1 which = $A$1

When I insert a cell above A1, so its data becomes situated on A2, I would
expect B1 to track it, because it doesn't have the $ so that B1 = A2. I
would expect C1 to remain pointed at A1, because of the $ symbol, so C1 =
$A$1 as before.

In fact this isn't what happens. They both point to A2 after the insert.
This is very inconvenient in the context of a particular spreadsheet I'm
working with. I know I can work around it by changing C1 to the value $$$=A1
and then changing it back again after the insert, but that creates an
undesired side effect elsewhere.

Is there any setting or formula that will stop C1 tracking A1 after an
insert and keep it pointed at A1 irrespective?

Many thanks


Try the following formula in cell C1:

=INDIRECT("A1")

Hope this helps / Lars-Åke

=INDIRECT("A1")

wrote

=INDIRECT("A1")

Thank you both. That's most helpful.