Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which formula should I use?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which formula should I use?
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 wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |