Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 - How to keep the formulas unchanged ?

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


--
A36B58K641
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,646
Default Excel 2002 - How to keep the formulas unchanged ?

Try this format:
=$C$1-$B$1

Regards,
Stefi

"Mr. Low" wrote:

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


--
A36B58K641

  #3   Report Post  
Posted to microsoft.public.excel.misc
bj bj is offline
external usenet poster
 
Posts: 1,397
Default Excel 2002 - How to keep the formulas unchanged ?

try
= indirect("C1")-indrect("B1")

"Mr. Low" wrote:

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


--
A36B58K641

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 - How to keep the formulas unchanged ?

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

--
A36B58K641


"bj" wrote:

try
= indirect("C1")-indrect("B1")

"Mr. Low" wrote:

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


--
A36B58K641

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 - How to keep the formulas unchanged ?

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

--
A36B58K641


"Stefi" wrote:

Try this format:
=$C$1-$B$1

Regards,
Stefi

"Mr. Low" wrote:

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


--
A36B58K641



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Excel 2002 - How to keep the formulas unchanged ?

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!
================================================== ===

"Mr. Low" wrote in message
...
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

--
A36B58K641


"bj" wrote:

try
= indirect("C1")-indrect("B1")

"Mr. Low" wrote:

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


--
A36B58K641



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Excel 2002 - How to keep the formulas unchanged ?

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


"Mr. Low" wrote:

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


--
A36B58K641

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 - How to keep the formulas unchanged ?

Hello Ron,

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

Many Thanks

Low

--
A36B58K641


"Ron Coderre" wrote:

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


"Mr. Low" wrote:

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


--
A36B58K641

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 - How to keep the formulas unchanged ?

Hello Rag,

It works well.

Many Thanks

Low

--
A36B58K641


"RagDyeR" wrote:

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!
================================================== ===

"Mr. Low" wrote in message
...
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

--
A36B58K641


"bj" wrote:

try
= indirect("C1")-indrect("B1")

"Mr. Low" wrote:

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


--
A36B58K641




  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default Excel 2002 - How to keep the formulas unchanged ?

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

Mr. Low wrote:

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


--
A36B58K641


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 Add-in: Open Database Connectivity available for Excel Nick B Excel Discussion (Misc queries) 8 December 7th 06 07:04 PM
Cells User Select Locked after upgrade to Excel 2002 TWilson Excel Discussion (Misc queries) 1 August 5th 05 12:22 PM
Putting Excel formatting and/or formulas into CSV file Frank D. Nicodem, Jr. Excel Discussion (Misc queries) 1 July 11th 05 10:18 PM
Excel 2002 Copying Exact formulas Kinjalip Excel Discussion (Misc queries) 4 April 27th 05 12:10 PM
Formula Arrays VERY SLOW in Excel 2002 Patrick Excel Worksheet Functions 2 January 27th 05 01:59 AM


All times are GMT +1. The time now is 11:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"