Yep - I agree with you re: clumsiness of pivot tables - they're really not suited to that purpose that you have proposed.
Personally I'd write a formula to calculate the cumulative totals outside of the pivot table, but linking to the cells in the pivot table eg. e1, e2, e3 etc. does that make sense?
yeah thought about that.....but that's a problem when the p.table is of a dynamic database, with expanding time data and possibly categories. in other words, cells in a p.table don't stay in the same place.
Note that I only spent about 10 mins last night looking over the spdsheet, no new developments yet, should have more time on weekend though.
no probs.. I am a bit of a spreadsheet nut....which is a pretty lonely hobby to have ..... so appreciate your thoughts.
Re: SAS - what do you want to know? It's awesome for dealing with cashflows, the dataset I am dealing with has 6,000,000 transactions! (20 yrs worth of data) Cumulative stuff is a cinch, and it can handle dates easily too.
can you recommend a version that I might try?
there's seems to be a heap of variants.
as I said earlier, excel is so ready to be taken out by a better product for the domestic and small business market. I had hopes open office would come out with something better, but their business model won't see it soon.
You can easily merge between all sorts of files (eg. master file and transaction file) and it's very speedy to run and use. Downside is that you'll need to learn SAS, but it's quite a nice procedural language to learn, and there are a lot of good books out there.
