Supplier Payments Consolidation

[natheplas]

Hi All,

I need your expertise help with this please!

Work has tasked me with an excel spreadsheet and I've played around with it, but can't get it to function properly and need someones help with more knowledge.

What they want is a master spreadsheet of supplier payments for the year (April 2016 - March 2017), and then another one for current.

What the worksheet needs to do is merge all the costs for suppliers over 12 months, showing a total and when they put in a new month it adds to it too.

Ive formatted the data so each sheet is set out the same, e.g column a is suppliers etc. i now need to collate the data so that is shows a final table of totals.

The added variable will be that there is cheque net, cash net, bank payment net these need to be combined. Then cheque VAT, cash VAT and bank VAT, this also need to be combined.

So on the master sheet you will have 3 columns. The list of suppliers, then totals for net and Vat totals.

How do I do this?!

Thank you for your help, please keep in mind everyone needs to use this, i dont mind if its complicated to set up, i need it to be easy to run for users who are a bit scared of excel, haha!

Thank you guys!!

Natheplas

 

[etaf]

a pivot table should do that for you
and as you add info , you can refresh and it will update

you need to make the sheet with the data into a table
the the formulas for total each row
and pivot table

 

[etaf]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	suppliers	date	cheque net	cash net	bank payment net	cheque VAT	cash VAT	bank VAT	net total	vat total
2	1
3	1
4	1
5	2
6	2
7	2
Sheet1

 

[etaf]

Excel Workbook
	A	B	C
2
3	Row Labels	Sum of net total	Sum of vat total
4	1	444.00	1,530.00
5	2	553.00	1,824.00
6	Grand Total	997.00	3,354.00
7
Sheet2

 

[natheplas]

Hi Etaf,

currently the data for each month is on a separate spreadsheet. How do I combine that data using a pivot table?

I need to be able to pull all of the supplier data together across for certain months and then group all the net together and then group VAT togther.

Thank you for your patience and help!

 

[natheplas]

Hi Guys,

Thanks for your notes, this one is solved.

 

[etaf]

to assist others searching the forum.
what was the solution - I did not see the reply here for some reason , hence why i did not respond