Financial planning - Excel 2000

[hog.badger]

I'm making a souped-up cheque-balancing spreadsheet for planning how
much I can save and spend on my limited student's allowance.

First there is a sheet with nine tables of various recurring and
non-recurring incomes and expenditures.

Then a sheet that translates those to rows with date, description,
debit, credit

Then a table that has rows of all days's dated from now until 2010,
with nine rows per date and details filled in from the previous sheet,
but with most of the entried blank.

Finally, what I want to do but am stuck on is to consolidate that sheet
to another that just has the rows with items so that I can add columns
with running totals. I would like to be able to add daily transactions
to that sheet... but I suppose that I could add another table to the
first sheet for that.

I could do this manually by making a column that has the row number of
valid rows and is otherwise blank and sorting the sheet by that column,
but I'm looking for an automatic method so that any change in the first
table propagates automatically through to the last.

If I could conditionally hide the blank rows.... but I can't find a way
to do that.

Any advice appreciated.

 

[Roger Govier]

Hi

I'm not sure that I fully understand your structure, but the simple
answer to your final question

If I could conditionally hide the blank rows.... but I can't find a way
to do that.

would be to use Data>Filter>Autofilter.
Use the dropdown on appropriate column to select Non Blanks
Take a look at the Subtotal() function.
For example =SUBTOTAL(9,$D$2:$D$1000) will total just the visible
(filtered) rows in that range.

But I don't think I would tackle your problem this way, allowing for 9
items of income or expenditure for each day from now till 2010.
What if you have more than 9 on any given day? What a waste of space
when there will be days with nil or single transactions.
Just enter all your data as it happens. By all means enter now, all
known future transactions that are going to happen.
Then create a Pivot Table to provide your report, with Year as a page
filed, Month as a Row field and Income and Expenditure as Data Fields.

For instruction on how to set up and use Pivot Tables, take a look at
Debra Dalgleish's site
http://www.contextures.com/tiptech.html
Scroll to the section on Pivot Tables