OK, I just spent about 5 mins reading through the thread. I think I know what WW is getting at, but is there any chance you can upload a spreadsheet for me to play with (Rather than have to create my own mockup etc etc)?
will PM you a link Tess by 12.15pm. thanks for your time and effort.....btw, you won't see the sumproduct formula in the cashbook, as they are put their using VBA code, then removed after calculation is complete. (this helps protect against corruption). will explain more in private message.
Otherwise, can't you just do what Belu said, and do that for the monthly sum of the payments and receipts, thus giving you your net position?
the issue with using sumif or sumproduct is that if you set up a worksheet like a formal cashbook, you have transaction details on the left, and chart of account categories on the right. for each category column, you have to use a sumproduct or sumif formula in each row. If you have 1000 transactions and 20 categories, that's 20,000 sumproduct formula......this slows the spreadsheet down dramatically and requires the wb to be put into manual calc mode.
I have the cashbook set up like this now.
I want to use pivot tables for the income statement, cash flow, and BAS tables, which are derivatives of the cashbook.
All will be clear when you see the file.
Re: cumulative balance at any point in time - check out the "running total" section of this website -
http://www.contextures.com/xlPivot10.html
sorry for the terrible explanation, I think I really need to see the spreadsheet before I can do anything sensible...