A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Way to keep formulas unchanged when deleting rows?



 
 
Thread Tools Display Modes
  #1  
Old November 17th, 2009, 04:02 PM posted to microsoft.public.excel.misc
PeteJ
external usenet poster
 
Posts: 20
Default 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
  #2  
Old November 17th, 2009, 04:18 PM posted to microsoft.public.excel.misc
muddan madhu
external usenet poster
 
Posts: 695
Default 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


  #3  
Old November 17th, 2009, 04:41 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 2,641
Default 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

  #4  
Old November 17th, 2009, 04:58 PM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 8,714
Default 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



  #5  
Old November 17th, 2009, 07:10 PM posted to microsoft.public.excel.misc
PeteJ
external usenet poster
 
Posts: 20
Default 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


.

  #6  
Old November 17th, 2009, 07:39 PM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 8,714
Default 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


.



  #7  
Old November 17th, 2009, 08:54 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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


.


  #8  
Old November 17th, 2009, 09:41 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 2,641
Default 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



.

  #9  
Old November 18th, 2009, 05:19 PM posted to microsoft.public.excel.misc
PeteJ
external usenet poster
 
Posts: 20
Default 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

.


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:14 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.