Way to keep formulas unchanged when deleting rows?

P

PeteJ

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
 
J

Jim Thomlinson

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.
 
D

David Biddulph

=INDIRECT("A5")
--
David Biddulph

Jim Thomlinson said:
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.
 
P

PeteJ

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

=INDIRECT(CELL($A$5))

??
 
D

David Biddulph

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.
 
G

Gord Dibben

=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
 
J

Jim Thomlinson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top