Keep formula from changing?
 

Excel 2000

Excel is "too smart"!

I have a worksheet that is a simple check register. In the column
that calculates the checkbook balance, the formula, in R1C1 notation,
is:

=+R[-1]C-RC[-2]+RC[-1]

R[-1]C is the balance from the row above.

C[-1] contains credits/deposits.

C[2-] contains debits/checks

All very simple and works well.

On occasion I want to insert a row(s), generally by cut/pasting the
data in the credits and debits colums (and corresponding columns such
as "payee") to a lower (greater #) row. I'd like to be able to do
this without having the forumulae in the balance column (as above)
chage at all, never, no way. But Excel is too smart and changes the
formulae. There is some change, whether the formula is "relative
reference" or "absolute reference". The formula chages whether the
cell address notation is RC or A1.

On rarer occasions I want to delete a row, by moving the data up.

Any way to do this?

Current solution is simply to data, fill (drag the fill handle) the
formula in the balance column all over again.

Thanks,

Fred Holmes

On Fri, 14 Oct 2005 11:16:27 -0400, Fred Holmes wrote:

Excel 2000

Excel is "too smart"!

I have a worksheet that is a simple check register. In the column
that calculates the checkbook balance, the formula, in R1C1 notation,
is:

=+R[-1]C-RC[-2]+RC[-1]

R[-1]C is the balance from the row above.

C[-1] contains credits/deposits.

C[2-] contains debits/checks

All very simple and works well.

On occasion I want to insert a row(s), generally by cut/pasting the
data in the credits and debits colums (and corresponding columns such
as "payee") to a lower (greater #) row. I'd like to be able to do
this without having the forumulae in the balance column (as above)
chage at all, never, no way. But Excel is too smart and changes the
formulae. There is some change, whether the formula is "relative
reference" or "absolute reference". The formula chages whether the
cell address notation is RC or A1.

On rarer occasions I want to delete a row, by moving the data up.

Any way to do this?

Current solution is simply to data, fill (drag the fill handle) the
formula in the balance column all over again.

Thanks,

Fred Holmes


Assuming data is as follows. I've reverted to A1 reference because I
don't understand RC references :-)

A1 Debits, B1 Credits, C1 Balance and numerical data starts in A3.

Add a helper column D.
C3=
=IF(D3<D2,INDIRECT("C"&(ROW()-D3-1)),INDIRECT("C"&(ROW()-1)))-INDIRECT("A"&ROW())+INDIRECT("B"&ROW())

D3 =
=ROW()-ROW(D$3)-COUNTA(A$3:A3)+1

Copy down as appropriate.


Now you can cut and paste and delete rows whilst maintaining the
correct balance.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Looks like a winner. I'll have to figure out what this INDIRECT()
function is all about.

Thanks,

Fred Holmes

On Fri, 14 Oct 2005 17:13:03 +0100, Richard Buttrey
wrote:

On Fri, 14 Oct 2005 11:16:27 -0400, Fred Holmes wrote:

Excel 2000

Excel is "too smart"!

I have a worksheet that is a simple check register. In the column
that calculates the checkbook balance, the formula, in R1C1 notation,
is:

=+R[-1]C-RC[-2]+RC[-1]

R[-1]C is the balance from the row above.

C[-1] contains credits/deposits.

C[2-] contains debits/checks

All very simple and works well.

On occasion I want to insert a row(s), generally by cut/pasting the
data in the credits and debits colums (and corresponding columns such
as "payee") to a lower (greater #) row. I'd like to be able to do
this without having the forumulae in the balance column (as above)
chage at all, never, no way. But Excel is too smart and changes the
formulae. There is some change, whether the formula is "relative
reference" or "absolute reference". The formula chages whether the
cell address notation is RC or A1.

On rarer occasions I want to delete a row, by moving the data up.

Any way to do this?

Current solution is simply to data, fill (drag the fill handle) the
formula in the balance column all over again.

Thanks,

Fred Holmes


Assuming data is as follows. I've reverted to A1 reference because I
don't understand RC references :-)

A1 Debits, B1 Credits, C1 Balance and numerical data starts in A3.

Add a helper column D.
C3=
=IF(D3<D2,INDIRECT("C"&(ROW()-D3-1)),INDIRECT("C"&(ROW()-1)))-INDIRECT("A"&ROW())+INDIRECT("B"&ROW())

D3 =
=ROW()-ROW(D$3)-COUNTA(A$3:A3)+1

Copy down as appropriate.


Now you can cut and paste and delete rows whilst maintaining the
correct balance.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________