| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| deleting rows messing up formulas | Excel Discussion (Misc queries) | |||
| Excel 2002 - How to keep the formulas unchanged ? | Excel Discussion (Misc queries) | |||
| Adding and deleting rows with formulas ****Need Help**** | Excel Discussion (Misc queries) | |||
| Question about deleting rows and formulas | Excel Discussion (Misc queries) | |||
| deleting rows--calculating formulas with each row as it goes? | Excel Discussion (Misc queries) | |||
Linear Mode
