I use Excel to maintain my projected personal and corporate cash flows based on my current balance and projected income and expenses throughout the month.
Column B contains income, column C contains expenses, and column E contains my running total. When I first created my worksheet, I used formulas such as =E4 + B5 – C5 to calculate the running balance (in this case for cell E5) after figuring in some income or an expense. The problem came when I deleted a row. For example, assume that I deleted the 8/2/2006 transaction from the list in the previous graphic.
When you delete a cell referred to in a formula, Excel displays the #REF! (bad reference) error code. Editing the formula in the first error cell causes Excel to fix the problem in every affected cell, but there is a way to avoid the errors entirely. To do so, create an OFFSET function to look up the previous balance, income (if any), and expense (if any). Here’s the formula I use for cell E5:
Managing running totals and balances using Excel 2003 data lists, or Excel 97-2002 worksheets, is somewhat involved. You can find a free article documenting the process on my web site.
All the best.