Way to keep formulas unchanged when deleting rows?

  • Thread starter Thread starter PeteJ
  • Start date Start date
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
 
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.
 
=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.
 
Thanks. But this makes the formula non-copyable to increment. so would this
work:

=INDIRECT(CELL($A$5))

??
 
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.
 
=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
 
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.
 
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
Try to make that with offset(). Pick some cell as a reference and in a formula give rows and columns with row() and column subtracting adequate amounts. Like in the attachment. On picture 2 you can see columns and rows calculated. If you delete a row nothing will happen, if you delete column - fomula will be messed up as in that case offset will sit in column C, which is 3-rd one and for that reason column()-4 will be negative. But - see at screen 3 - - you can set a fixed value for a column. Play with it and adjust to your needs. I'd say it may quite likely solve your problem.
 

Attachments

  • Excel1.webp
    Excel1.webp
    53.2 KB · Views: 660
Back
Top