Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Way to keep formulas unchanged when deleting rows?

1,455 views
Skip to first unread message

PeteJ

unread,
Nov 17, 2009, 10:02:01 AM11/17/09
to
I have a bunch of references to some data that I copy into a datasheet.
Occasionally, this data has some blank rows or rows with unneeded data in it.
If I delete those rows, it messes up all the formulas on other pages. Is
there a way to define the formulas such that deleting data won't change them?
I've tried using absolute cell references ($A$5), but when I delete the line,
this cell reference gets changed.

Thanks,

Pete

muddan madhu

unread,
Nov 17, 2009, 10:18:47 AM11/17/09
to

=INDIRECT("A5")

Jim Thomlinson

unread,
Nov 17, 2009, 10:41:04 AM11/17/09
to
In short the answer is no. Deleting a row will modify all formulas that
referenced a range which contained that row as a part of it.
--
HTH...

Jim Thomlinson

David Biddulph

unread,
Nov 17, 2009, 10:58:34 AM11/17/09
to
=INDIRECT("A5")
--
David Biddulph

"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:0A9EC364-8965-4D7A...@microsoft.com...

PeteJ

unread,
Nov 17, 2009, 1:10:01 PM11/17/09
to
Thanks. But this makes the formula non-copyable to increment. so would this
work:

=INDIRECT(CELL($A$5))

??


"muddan madhu" wrote:

> .
>

David Biddulph

unread,
Nov 17, 2009, 1:39:19 PM11/17/09
to
The answer to your question is "No, it would not work".

You may need to remind yourself what the CELL function does and what its
syntax is; look it up in Excel help.
--
David Biddulph

"PeteJ" <Pe...@discussions.microsoft.com> wrote in message
news:FF372E1C-8B69-4B66...@microsoft.com...

Gord Dibben

unread,
Nov 17, 2009, 2:54:25 PM11/17/09
to
=INDIRECT("A" & ROW() + 4) entered in B1

BTW........what are "other pages"?

Do you mean other worksheets?

Then you might want

=INDIRECT("Sheet1!A" & ROW() + 4) in those other sheets.


Gord Dibben MS Excel MVP

On Tue, 17 Nov 2009 10:10:01 -0800, PeteJ <Pe...@discussions.microsoft.com>
wrote:

Jim Thomlinson

unread,
Nov 17, 2009, 3:41:02 PM11/17/09
to
As Indirect is volatile I rarely use it and post it as a suggestion even
less. Indirect is a truely static reference and unless you want to write all
of your formulas individually or write very complicated formulas it's usage
is limited to little one off applications. Normally a properly organized
spreadsheet will not require it.

To each his own but I can count on one hand the number of times I have used
it in the past couple of years.
--
HTH...

Jim Thomlinson


"David Biddulph" wrote:

> .
>

PeteJ

unread,
Nov 18, 2009, 11:19:01 AM11/18/09
to
Yes, I meant Sheets, sorry about that. I will give this a try.

Thanks.

"Gord Dibben" wrote:

> .
>

0 new messages