Way to keep formulas unchanged when deleting rows?

[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

 

[muddan madhu]

=INDIRECT("A5")

 

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

 

[David Biddulph]

=INDIRECT("A5")
--
David Biddulph

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.

 

[PeteJ]

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

=INDIRECT(CELL($A$5))

??

 

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

 

[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

 

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

 

[PeteJ]

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

Thanks.

 

[Vaeryn]

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.