Excel 2002 : How to keep =IF( ) formula at stationary position ?

[Guest]

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

 

[Zone]

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

 

[Pete_UK]

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

 

[Guest]

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

 

[Guest]

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

 

[Zone]

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

 

[David Biddulph]

You could try
=IF(OFFSET(A2,0,1)=OFFSET(A2,0,2),"Yes","No")

 

[Guest]

Hello David,

Yes, your formula works very well.

It will be very useful for the spreadsheet work.

Many thanks

Low