Wow Arvi, that is a POST!
Just give me time to implements it and understand all the treasures of
such a solution.
For the moment really thanks.
I'll post again later.
Ataru


Arvi Laanemets ha scritto:

Hi

My advice was to design report sheets, not to use pivot tables. An example:

Transactions!A3="Date"
Transactions!B3="Account"
Transactions!C3="Amount"
Transactions!D3="Month"

B4 and down - apply data validation list with allowed values "In" and "Out"
D4=IF(A4="";"";--TEXT(A4;"yyyy.mm"))
, and copy down.
Define a named range (InsertNameDefine) Transact
=OFFSET(Transactions!$A$3,1,,COUNT(Transactions!$A :$A),4)

Lists!A1="Months"
Lists!A2=DATE(YEAR(MIN(INDEX(Transact,,1))),MONTH( MIN(INDEX(Transact,,1))),1
)
Lists!A3=IF(DATE(YEAR($A$2),MONTH($A$2)+ROW()-2,1)TODAY(),"",DATE(YEAR($A$2
),MONTH($A$2)+ROW()-2,1))
, and copy Lists!A3 down. Format Lists!A2:A? as Custom "yyyy.mm"
Define a named range Months
=OFFSET(Lists!$A$1,1,,COUNT(Lists!$A:$A),1)

Balance!A1="Month:"
Apply to Balance!B1 data validation list with source
=Months
, and format as Custom "yyyy.mm"

Balance!A3="Start balance:"
Balance!A4="Income:"
Balance!A5="Spending:"
Balance!A6="End balance:"

Balance!B3=SUMPRODUCT(--(INDEX(Transact,,4)<--TEXT($B$1,"yyyy.mm")),(INDEX(T
ransact,,2)="In")-(INDEX(Transact,,2)="Out"),INDEX(Transact,,3))
Balance!B4=SUMPRODUCT(--(INDEX(Transact,,4)=--TEXT($B$1,"yyyy.mm")),--(INDEX
(Transact,,2)="In"),INDEX(Transact,,3))
Balance!B5=SUMPRODUCT(--(INDEX(Transact,,4)=--TEXT($B$1,"yyyy.mm")),--(INDEX
(Transact,,2)="Out"),INDEX(Transact,,3))
Balance!B6=SUM(B3:B4,-B5)


Arvi Laanemets


wrote in message
oups.com...
Hi Arvi, thank you to still be here suggesting for my problem.
I am still working on that and the suggested solution to have all the
reports on one page has a problem....sigh!

Everything looks fine and sweet....add a column for month and have a
month selection in the pivot page...the problem is that the pivot will
only show the month transaction total and not the amout I have in the
bank accounts....let me explain:
in january I write all my in and outs and the total is +$5000 then in
february I have only outs for $3000 dollars.
Having the pivot showing only february it will say that I am under with
3000 debit while it shoud say plus 2000.
When I was having everythig divided by month in different pages I was
copying the total of january to february page...starting all over
again...
Am I a little bit "stupid" or tired or what?
Tks,
Ataru


Arvi Laanemets wrote:
Sorry!


Into 3rd column enter column headers - data start from 4th column.

Must be

Into 3rd row enter column headers - data start from 4th row


Arvi Laanemets