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]

On Fri, 14 Oct 2005 11:16:27 -0400, Fred Holmes <[email protected]> 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
__________________________

[Fred Holmes]

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
<[email protected]> wrote:

>On Fri, 14 Oct 2005 11:16:27 -0400, Fred Holmes <[email protected]> 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
>__________________________