Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Add-in: Open Database Connectivity available for Excel | Excel Discussion (Misc queries) | |||
Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) | |||
Putting Excel formatting and/or formulas into CSV file | Excel Discussion (Misc queries) | |||
Excel 2002 Copying Exact formulas | Excel Discussion (Misc queries) | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions |