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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|