| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Turn off the formula correction feature
When you cut or delete cells, any formula that references those cells changes.
I am trying to compare columns of data using an "IF" function to show TRUE or FALSE. In the process I wind up adding or deleting cells. Whenever I do this, the folmula no longer works - it tries to reference the cell that is no longer there. What I would like to happen is that the formula references the new cell in what was the previous location. In example: if I delete A1, so that A2 becomes the new A1, I want the fomula to look at the new A1 and ignore that I deleted a cell. Is it possible to do this? -- Thank you, John Gregory |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Turn off the formula correction feature
=INDIRECT("A1")
-- David Biddulph JGreg7 wrote: When you cut or delete cells, any formula that references those cells changes. I am trying to compare columns of data using an "IF" function to show TRUE or FALSE. In the process I wind up adding or deleting cells. Whenever I do this, the folmula no longer works - it tries to reference the cell that is no longer there. What I would like to happen is that the formula references the new cell in what was the previous location. In example: if I delete A1, so that A2 becomes the new A1, I want the fomula to look at the new A1 and ignore that I deleted a cell. Is it possible to do this? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Turn off the formula correction feature
Hi,
You need to use INDIRECT. If you struggle with setting up your formula using this then post back. example =INDIRECT("A1") will always reference A1 even if you delete the cell -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "JGreg7" wrote: When you cut or delete cells, any formula that references those cells changes. I am trying to compare columns of data using an "IF" function to show TRUE or FALSE. In the process I wind up adding or deleting cells. Whenever I do this, the folmula no longer works - it tries to reference the cell that is no longer there. What I would like to happen is that the formula references the new cell in what was the previous location. In example: if I delete A1, so that A2 becomes the new A1, I want the fomula to look at the new A1 and ignore that I deleted a cell. Is it possible to do this? -- Thank you, John Gregory |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Turn off the formula correction feature
Can I use it in a "IF" function?
=IF((INDIRECT(A1))=(INDIRECT(B1)),"yes"," ") In this case the formula editor says it is "Volitile" and does not return a value. -- Thank you, John Gregory "Mike H" wrote: Hi, You need to use INDIRECT. If you struggle with setting up your formula using this then post back. example =INDIRECT("A1") will always reference A1 even if you delete the cell -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "JGreg7" wrote: When you cut or delete cells, any formula that references those cells changes. I am trying to compare columns of data using an "IF" function to show TRUE or FALSE. In the process I wind up adding or deleting cells. Whenever I do this, the folmula no longer works - it tries to reference the cell that is no longer there. What I would like to happen is that the formula references the new cell in what was the previous location. In example: if I delete A1, so that A2 becomes the new A1, I want the fomula to look at the new A1 and ignore that I deleted a cell. Is it possible to do this? -- Thank you, John Gregory |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Turn off the formula correction feature
I tried that, however when you copy the formula, it copies identiaclly.
Instead of copying as =INDIRECT("A1") =INDIRECT("A2") =INDIRECT("A3") It copies as =INDIRECT("A1") =INDIRECT("A1") =INDIRECT("A1") When I use it as =INDIRECT(A1), it returns the value "#REF". -- Thank you, John Gregory "David Biddulph" wrote: =INDIRECT("A1") -- David Biddulph JGreg7 wrote: When you cut or delete cells, any formula that references those cells changes. I am trying to compare columns of data using an "IF" function to show TRUE or FALSE. In the process I wind up adding or deleting cells. Whenever I do this, the folmula no longer works - it tries to reference the cell that is no longer there. What I would like to happen is that the formula references the new cell in what was the previous location. In example: if I delete A1, so that A2 becomes the new A1, I want the fomula to look at the new A1 and ignore that I deleted a cell. Is it possible to do this? . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Turn off the formula correction feature
=INDIRECT("A"&ROW())
-- David Biddulph JGreg7 wrote: I tried that, however when you copy the formula, it copies identiaclly. Instead of copying as =INDIRECT("A1") =INDIRECT("A2") =INDIRECT("A3") It copies as =INDIRECT("A1") =INDIRECT("A1") =INDIRECT("A1") When I use it as =INDIRECT(A1), it returns the value "#REF". =INDIRECT("A1") -- David Biddulph JGreg7 wrote: When you cut or delete cells, any formula that references those cells changes. I am trying to compare columns of data using an "IF" function to show TRUE or FALSE. In the process I wind up adding or deleting cells. Whenever I do this, the folmula no longer works - it tries to reference the cell that is no longer there. What I would like to happen is that the formula references the new cell in what was the previous location. In example: if I delete A1, so that A2 becomes the new A1, I want the fomula to look at the new A1 and ignore that I deleted a cell. Is it possible to do this? . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Turn off the formula correction feature
That works! Thank you very much for your help!
-- Thank you, John Gregory "David Biddulph" wrote: =INDIRECT("A"&ROW()) -- David Biddulph JGreg7 wrote: I tried that, however when you copy the formula, it copies identiaclly. Instead of copying as =INDIRECT("A1") =INDIRECT("A2") =INDIRECT("A3") It copies as =INDIRECT("A1") =INDIRECT("A1") =INDIRECT("A1") When I use it as =INDIRECT(A1), it returns the value "#REF". =INDIRECT("A1") -- David Biddulph JGreg7 wrote: When you cut or delete cells, any formula that references those cells changes. I am trying to compare columns of data using an "IF" function to show TRUE or FALSE. In the process I wind up adding or deleting cells. Whenever I do this, the folmula no longer works - it tries to reference the cell that is no longer there. What I would like to happen is that the formula references the new cell in what was the previous location. In example: if I delete A1, so that A2 becomes the new A1, I want the fomula to look at the new A1 and ignore that I deleted a cell. Is it possible to do this? . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Turn off Research feature | Excel Discussion (Misc queries) | |||
| how do I turn off automatic spelling correction | Excel Worksheet Functions | |||
| How do I turn off security feature for hyperlinks? | Setting up and Configuration of Excel | |||
| How do you turn off the Undo feature in excel? | Excel Discussion (Misc queries) | |||
| how to turn on and off autofill feature in 2000 | Excel Discussion (Misc queries) | |||
Linear Mode
