| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to keep =IF( ) formula at stationary position ?
Dear Sir ,
I have the formula at =IF(B2=C2,Yes,No) at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx .. .. 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position ?
Switch between relative, absolute, and mixed references
1.. Select the cell that contains the formula. 2.. In the formula bar , select the reference you want to change. 3.. Press F4 to toggle through the combinations. The "Changes To" column reflects how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right. Formula being copied Reference (Description) Changes to $A$1 (absolute column and absolute row) $A$1 A$1 (relative column and absolute row) C$1 $A1 (absolute column and relative row) $A3 A1 (relative column and relative row) C3 -- Don Guillett SalesAid Software "Mr. Low" wrote in message ... Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position ?
Low, I think people are not understanding the problem as you have
stated it. Columns cannot be moved "up and down". Columns can only be moved right or left. Are you moving cells within this columns? If you are talking about moving rows up or down, the formulas should adjust themselves, so we are not understanding your question. James Mr. Low wrote: Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position ?
I think what he means is that if he inserts or deletes cells in columns
B or C then the formulae will adjust, so that in cell A20, for example, he may end up with something like: =IF(B21=C19,"Yes","No") whereas he wants it to stay as: =IF(B20=C20,"Yes","No") He will also get #REF errors where he deletes cells, and will have to re-instate the formula to clear these. There is a quick way of copying the formula down after you have inserted or deleted a cell - select A2 and double-click the fill handle (the small black square in the bottom right corner of the cursor). Hope this helps. Pete Zone wrote: Low, I think people are not understanding the problem as you have stated it. Columns cannot be moved "up and down". Columns can only be moved right or left. Are you moving cells within this columns? If you are talking about moving rows up or down, the formulas should adjust themselves, so we are not understanding your question. James Mr. Low wrote: Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position
Hello Zone,
Sorry, I have wrongly stated as moving the column up and down, should be moving rows within the column up and down. I would like all the formulas in each row of column A unaffected by this movement. Any idea how to do it ? Previously I have leaned about how to use OFFSET formula when dealing with A2 = C2-B2 . Can =OFFSET( ) formula be used in this situation ? Thanks Low A36B58K641 "Zone" wrote: Low, I think people are not understanding the problem as you have stated it. Columns cannot be moved "up and down". Columns can only be moved right or left. Are you moving cells within this columns? If you are talking about moving rows up or down, the formulas should adjust themselves, so we are not understanding your question. James Mr. Low wrote: Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position
Hello Pete_UK,
Yes, you are right absolutely. I need the formula to be not affected by the movement of the cells with a column up and down. Your method of updating the original cell formula is helpful for me to a certain extent. However the user need to repeat the task when ever the cell is moved , Is there any other suggestions from anybody ? Many Thanks Low -- A36B58K641 "Pete_UK" wrote: I think what he means is that if he inserts or deletes cells in columns B or C then the formulae will adjust, so that in cell A20, for example, he may end up with something like: =IF(B21=C19,"Yes","No") whereas he wants it to stay as: =IF(B20=C20,"Yes","No") He will also get #REF errors where he deletes cells, and will have to re-instate the formula to clear these. There is a quick way of copying the formula down after you have inserted or deleted a cell - select A2 and double-click the fill handle (the small black square in the bottom right corner of the cursor). Hope this helps. Pete Zone wrote: Low, I think people are not understanding the problem as you have stated it. Columns cannot be moved "up and down". Columns can only be moved right or left. Are you moving cells within this columns? If you are talking about moving rows up or down, the formulas should adjust themselves, so we are not understanding your question. James Mr. Low wrote: Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position
Low, I see your problem now. Pete's suggestion looks pretty good! You
could also try the offset you mentioned. Paste this formula into A2: =IF(OFFSET(A2,0,1)=OFFSET(A2,0,2),"Yes","No") and then drag to fill down to A20. This seems to help if the values in columns B and C are moved around. Any help? James Mr. Low wrote: Hello Pete_UK, Yes, you are right absolutely. I need the formula to be not affected by the movement of the cells with a column up and down. Your method of updating the original cell formula is helpful for me to a certain extent. However the user need to repeat the task when ever the cell is moved , Is there any other suggestions from anybody ? Many Thanks Low -- A36B58K641 "Pete_UK" wrote: I think what he means is that if he inserts or deletes cells in columns B or C then the formulae will adjust, so that in cell A20, for example, he may end up with something like: =IF(B21=C19,"Yes","No") whereas he wants it to stay as: =IF(B20=C20,"Yes","No") He will also get #REF errors where he deletes cells, and will have to re-instate the formula to clear these. There is a quick way of copying the formula down after you have inserted or deleted a cell - select A2 and double-click the fill handle (the small black square in the bottom right corner of the cursor). Hope this helps. Pete Zone wrote: Low, I think people are not understanding the problem as you have stated it. Columns cannot be moved "up and down". Columns can only be moved right or left. Are you moving cells within this columns? If you are talking about moving rows up or down, the formulas should adjust themselves, so we are not understanding your question. James Mr. Low wrote: Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position
You could try
=IF(OFFSET(A2,0,1)=OFFSET(A2,0,2),"Yes","No") -- David Biddulph "Mr. Low" wrote in message ... Hello Zone, Sorry, I have wrongly stated as moving the column up and down, should be moving rows within the column up and down. I would like all the formulas in each row of column A unaffected by this movement. Any idea how to do it ? Previously I have leaned about how to use OFFSET formula when dealing with A2 = C2-B2 . Can =OFFSET( ) formula be used in this situation ? Thanks Low "Zone" wrote: Low, I think people are not understanding the problem as you have stated it. Columns cannot be moved "up and down". Columns can only be moved right or left. Are you moving cells within this columns? If you are talking about moving rows up or down, the formulas should adjust themselves, so we are not understanding your question. James Mr. Low wrote: Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002 : How to keep =IF( ) formula at stationary position
Hello David,
Yes, your formula works very well. It will be very useful for the spreadsheet work. Many thanks Low -- A36B58K641 "David Biddulph" wrote: You could try =IF(OFFSET(A2,0,1)=OFFSET(A2,0,2),"Yes","No") -- David Biddulph "Mr. Low" wrote in message ... Hello Zone, Sorry, I have wrongly stated as moving the column up and down, should be moving rows within the column up and down. I would like all the formulas in each row of column A unaffected by this movement. Any idea how to do it ? Previously I have leaned about how to use OFFSET formula when dealing with A2 = C2-B2 . Can =OFFSET( ) formula be used in this situation ? Thanks Low "Zone" wrote: Low, I think people are not understanding the problem as you have stated it. Columns cannot be moved "up and down". Columns can only be moved right or left. Are you moving cells within this columns? If you are talking about moving rows up or down, the formulas should adjust themselves, so we are not understanding your question. James Mr. Low wrote: Dear Sir , I have the formula at =IF(B2=C2,"Yes","No") at A2 and copy downwards to A20. I would like the formula to be able to compare each cell at column B and C even if I move either colu7mn up and down. Currently I need to copy the formula all over again if any cell in which the formula refers to is moved. A B C 1 Ref A Ref B 2 Yes xxxx xxxx 3 Yes xxxx xxxx 4 No xxxx xxxx . . 20 Yes xxxx xxxx Thanks Low -- A36B58K641 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| what is a formula | Excel Worksheet Functions | |||
| I was looking to get assistance with a formula in Excel 2002 | Excel Worksheet Functions | |||
| Excel 2002 Lookup formula returning wrong results? | Excel Worksheet Functions | |||
| Vlookup formula Excel version 2002 | Excel Discussion (Misc queries) | |||
| VLookup Formula Excel Version 2002 | Excel Worksheet Functions | |||
Linear Mode
