Excel 2002 - How to keep the formulas unchanged ?

M

Mr. Low

Dear Sir,

May I know how to keep the formula in a cell at its stationary position when
the other cell is moved ?

Lets consider the folowing worksheet example:

A B C D
1 = C1-B1
2 = C2-B2
3 = C3-B3
4

Formula =(C1-B1) is entered cell A1, however when cell C1 is cut and moved
to position C2, the formula in cell A1 now becomes = (C2-B1).

Is there a way to keep the formula in column A remain unchanged even if
column C is moved out from its original position (i.e. up, down, lefft or
right) ?


Thanks

Low
 
M

Mr. Low

Dear BJ,

I tried to key in the formula manually, it works.

However in real work situation, I have sometime up to 5000 cells need to
have this formula, copying the formula from the first cell to the others is
not working as the cell address within the foumula does not change.

Eg. =INDIRECT("B4")-INDIRECT("C4") wiull remain the same when it is copied
downwards. Is there anyway to overcome this problem ?


Thanks

Low
 
M

Mr. Low

Hello Stefi,

Thanks for your info.

However it does not work as formula $C$1-$B$1 becomes $C$1-$B$2 when cell BI
is moved on row down. Is there any other ways


Kind Regards

Low
 
R

RagDyeR

Try this:

=INDIRECT("C"&ROWS($1:1))-INDIRECT("B"&ROWS($1:1))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Dear BJ,

I tried to key in the formula manually, it works.

However in real work situation, I have sometime up to 5000 cells need to
have this formula, copying the formula from the first cell to the others is
not working as the cell address within the foumula does not change.

Eg. =INDIRECT("B4")-INDIRECT("C4") wiull remain the same when it is copied
downwards. Is there anyway to overcome this problem ?


Thanks

Low
 
R

Ron Coderre

Try this:

A1: =OFFSET(A1,0,2)-OFFSET(A1,0,1)
Copy down as far as you need

That formula is durable against either the Col_B or the Col_C cells moving
(or both).

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
M

Mr. Low

Hello Ron,

This is a great formula, It works very well irrespective of the cell
positions (rows ande column)

Many Thanks

Low
 
L

Lori

Or for a non-volatile solution use a defined name e.g. =Diff by
choosing Insert > Name > Define Diff: =C2-B2
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top