Keep formula from changing?

[Fred Holmes]

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

 

[Richard Buttrey]

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
__________________________

 

[Fred Holmes]

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

Thanks,

Fred Holmes

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
__________________________