Pivot tables merging data on two sheets (Office 2007)

[Travis]

Hi,

I have invoices sent on one worksheet and payments received on
another.

I'd like to do a Pivot table which would be capable of doing a summary
of what each account owes.

At the moment I do a pivot table on the invoices sheet and another
pivot table on the payments sheet, then I copy and special paste as
values these pivot tables together into another table, subtract one
from the other and then I've got a nice summary of outstanding amounts
for all accounts.

Its a really messy way to go about doing it!

Would it be possible to do one pivot table incorporating both sheets
of data and have a calculated field showing the difference between the
two?

Thanks in advance.

Travis

 

[Debra Dalgleish]

Can you enter all the data on one worksheet, with a column to indicate
if the transaction is a payment or invoice?
Then, create a pivot table from that combined data.