Supplier Payments Consolidation

natheplas

Board Regular
Joined
Aug 28, 2016
Messages
97
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
Excel Workbook
ABCDEFGHIJ
1suppliersdatecheque netcash netbank payment netcheque VATcash VATbank VATnet totalvat total
21
31
41
52
62
72
Sheet1
 
Upvote 0
Excel Workbook
ABC
2
3Row LabelsSum of net totalSum of vat total
41444.001,530.00
52553.001,824.00
6Grand Total997.003,354.00
7
Sheet2
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top