Which formula should I use?

[hendis]

Hi. TIA to anyone that can help.

I've got Office 2003 Pro. I'm trying to build a workbook in Excel that
can handle transactions (always debits) that haven't been processed by
the bank yet.

Let me explain by recreating a part of a worksheet:

A B C D
1 DATE DESCR DEBIT BALANCE
2 1/5/99 beg bal 500.00
3 1/9/99 screws 7.00 493.00
4 1/12/99 bolts 10.00 483.00
5 1/15/99 wood 20.00 463.00

The example above shows how a "normal" credit card/checkbook register
would look. In the worksheet I need is different, because (1) there are
never any deposits or credits since the beginning balance in cell D2
can easily be increased, and (2) I need to delete a debit (in column C)
when it is no longer needed (when the bank's online records reflect the
transaction as completed.)

(I would prefer to delete an entire row, which would prevent the
worksheet from becoming too large.)

My question is this: what formula(s) do I need to use in column D
(cells D3, D4 and D5) so that everything still works OK when I delete a
row? I don't know what mix I should use of absolute vs. relative
references, or if I should use the offset function, or what.

Currently, I'm (1) recreating the beginning balance in cell D2, (2)
deleting the row that reflects the completed transaction (which causes
a REF error), then (3) selecting - and deleting - the REF error, (4)
recreating the formula in cell D3 (=D2-C3) and finally, (5) performing
an autofill from cell D3 down. I want to get away from all of this and
make it automatic, since I get a lot of cleared transactions every day.

How? (Please post all replies to the group, so that everyone benefits.)

Thanks a million.

 

[Biff]

Hi!

Try this in D3:

=IF(C3="","",OFFSET(D3,-1,,)-C3)

Copy down to enough cells that allows for new entries. Also, keep in mind
that every time you delete a row the range that you copied the formula to
shrinks by one row.

Here's an idea: In column E you could enter an "X" when you want to delete
that particular transaction and use an event macro to automatically delete
that row for you after you've entered the "X". The only problem with that is
the possibility of making a mistake and entering the "X" in the wrong row.

Biff