How to combine three worksheets into one for a pivot table

How to combine three worksheets into one for a...

Didn't find your answer?

Hi all,

i have recorded invoice, payment received and credit note in separate worksheet for each by month. eg worksheet 1 is january 06 invoice under filename 'Invoice' and january 06 credit note under filename 'Credit note', etc.

after learning the pivot table(which helps to sort out the data into individual debtors), but i would like to advance it by combine all three(invoice, credit note and payment received) into one worksheet to create debtor ageing.

any idea how i can maneuover this?

Thank you in advance and happy new year to you all ;-)

Ken Wen

Replies (4)

Please login or register to join the discussion.

avatar
By neileg
02nd Jan 2007 13:20

I half agree with Richard, but there are problems
If you make the three sheets match as Richard suggests, you can then use a multiple range pivot table to combine the three. Remember you will have to make the sign of the amounts correct, eg +ve for invoices and -ve for payments and credits.

However, you will run into problems in matching payments and credits with the invoices they relate to. Your overall totals will be OK, but monthly analysis will require some work to get the transactions into the appropriate periods.

To get a fully working system would be easier in Access, or even get hold of a shareware sales ledger!

Thanks (0)
avatar
By David Carter
03rd Jan 2007 11:40

combine all 3 into one worksheet
Agree with Richard and Neil. The key step is to store all transactions in a single worksheet.

Make a new column "TType" where you enter a value for the transaction type - maybe SIN, SCR, REC for invoice, credit, receipt.

You can have some fun doing this with a small volume of transactions, but the time will come when you have to buy a proper sales ledger program.

Thanks (0)
avatar
By Richard Willis
02nd Jan 2007 13:14

Happy New Year to you Ken
Hi Ken

You will first need to regularise all the existing sheets, i.e. make sure that all the like columns are in the same place. Any columns that exist in one sheet and not all will need to be inserted and left blank in the other sheets.

Then add to all the sheets an extra column, I suggest in 'A', for a 'Type' code, e.g. 'I' for Invoice, 'C' for Credit Note, 'R' for Receipt, and 'A' for Adjustment. In each of the existing sheets propogate this column with the relevant letter. Then copy all the data from all sheets into one.

I am assuming that the sheets already carry data such as an identifier for the Debtor, the date, etc. Once all the data is in one sheet I would add a column for Month No. and fill it from the date field using a date function. You can then add columns to the right, using an 'IF' function, to display the amounts in columns depending on age, and/or use the month no. in a pivot table.

I would save the workbook(s) with another name BEFORE you start!

Have Fun

Richard

p.s. Once you have the month no. field I would suggest that there is no need to keep the work in sheets by month but all in one sheet. I am assuming that there is not a huge amount of data, otherwise you should, perhaps, be in Access.

Thanks (0)
avatar
By mingali
04th Jan 2007 15:20

Thank you, all
i am currently busy to meet dateline of another project, so i havent tried on the suggestion provided.

i will give it a try once i finish my current project.

Thanks ;-)

Thanks (0)