Hi

Assuming your data is currently in column A:C, add an extra column and in D1
enter Month
In D2 enter
=IF(A2="","",TEXT(A2,"mmm"))
Copy down as far as required
Add a new column E and enter in E1 Amount
In E2 enter
=IF(C2="","",IF(RIGHT(C2,2)="dr",LEFT(C2,LEN(C2)-3)*-1,LEFT(C2,LEN(C2)-3)*1))
copy down as far as required.

Extend the source for your PT to include these new columns
Substitute your new column E, Amount, in place of your exiting column C
heading in the Data section.
Add the new Month column to the Page area

Select Apr from the page dropdown, and the Total will be the Closing Balance
for April
You can Overtype the cell entry Total Value with Closing Balance if you wish
--
Regards
Roger Govier

"shripaldalal" wrote in message
...
hi,

have a pivot table that gives a bank statement:

1-apr opening bal 5000 dr
2-apr withdrew cash 2000 cr
3-apr chq to creditor 1000 cr

how do i get a new line automatically in the pivot:

1-apr opening bal 5000 dr
2-apr withdrew cash 2000 cr
3-apr chq to creditor 1000 cr
30-apr closing bal 2000 dr (that is total debits - total credits for
THIS MONTH OF APR ONLY)

how do i get the above last row automatically in a pivot, without
having it in source data, something like a calculated item or
something ? is it possible ?

best regards,
shripal d.