Way to keep formulas unchanged when deleting rows?
 

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

Way to keep formulas unchanged when deleting rows?
 

=INDIRECT("A5")


On Nov 17, 8:02*pm, PeteJ wrote:
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

Way to keep formulas unchanged when deleting rows?
 

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


"PeteJ" wrote:

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

Way to keep formulas unchanged when deleting rows?
 

=INDIRECT("A5")
--
David Biddulph

"Jim Thomlinson" wrote in message
...
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


"PeteJ" wrote:

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

Way to keep formulas unchanged when deleting rows?
 

Thanks. But this makes the formula non-copyable to increment. so would this
work:

=INDIRECT(CELL($A$5))

??


"muddan madhu" wrote:


=INDIRECT("A5")


On Nov 17, 8:02 pm, PeteJ wrote:
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

.

Way to keep formulas unchanged when deleting rows?
 

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" wrote in message
...
Thanks. But this makes the formula non-copyable to increment. so would
this
work:

=INDIRECT(CELL($A$5))

??


"muddan madhu" wrote:


=INDIRECT("A5")


On Nov 17, 8:02 pm, PeteJ wrote:
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

.

Way to keep formulas unchanged when deleting rows?
 

=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
wrote:

Thanks. But this makes the formula non-copyable to increment. so would this
work:

=INDIRECT(CELL($A$5))

??


"muddan madhu" wrote:


=INDIRECT("A5")


On Nov 17, 8:02 pm, PeteJ wrote:
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

.

Way to keep formulas unchanged when deleting rows?
 

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:

=INDIRECT("A5")
--
David Biddulph

"Jim Thomlinson" wrote in message
...
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


"PeteJ" wrote:

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


.

Way to keep formulas unchanged when deleting rows?
 

Yes, I meant Sheets, sorry about that. I will give this a try.

Thanks.

"Gord Dibben" wrote:

=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
wrote:

Thanks. But this makes the formula non-copyable to increment. so would this
work:

=INDIRECT(CELL($A$5))

??


"muddan madhu" wrote:


=INDIRECT("A5")


On Nov 17, 8:02 pm, PeteJ wrote:
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

.


.