Keeping track of vendor invoices: what would you do differently

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
167
Office Version
  1. 365
Platform
  1. Windows
https://1drv.ms/x/s!AvjBsEPEq12ngR1M8FneK-3B1Ew7?e=1pqHkd

Hey guys,
Let me explain my setup.

This workbook is what I use to manage invoices that we pay to other companies.

Company (1) sends us invoices. We'll pay them.
Each week, Company (1) will send us a statement with a list of overdue/unpaid invoices.
I will copy and paste the invoice numbers from the statement onto the sheet called Company (1).
Then the formulas in Columns G to Columns U will populate. The formulas look at the data in Sheet2.

The populated results tell me:

- which invoices have been booked
- which invoices have been paid
- the date it was booked
- the date it was paid
- how many entries were made to book the invoice
(e.g. was a $100 invoice booked as $100 once, or was it booked two times: $60 + $40 = $100
- the internal reference number of the booking entry
- the total amount of that booking entry

Sheet2 is where I paste the data. I usually delete all the rows and paste the updated again every day.
There are approx. 20,000 rows.
I have 20 more sheets (in the same workbook) that look identical (same column, formulas) to Company (1) representing the other companies' accounts.
Total file size is ~ 14 MB.

Looking for ways to improve this process (however big or small the change), I would love to hear your thoughts, opinions, what would you do differently, what would you change etc.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I really do not know much about how you may want to do things.
But I always believe it's best to limit how much you want to split your data out.

Like some users make a separate Workbook for every one of their customers.
And some users want to just separate out each customer in their separate sheet.

But if possible I think it's best to keep all customers in their own worksheet if possible

Then with some formulas or Vba you can get all the results you want on each customer.

A few formulas could provide any result you want.
Like how much did company "Alpha" spend in October or March on Cars or Electricity

If necessary a Vba script could be used to copy all Records on a particular company to another sheet if you just wanted a print out on all transactions for that company.

But then that is just my opinion.
 
Last edited:
Upvote 0
I really do not know much about how you may want to do things.
But I always believe it's best to limit how much you want to split your data out.

Like some users make a separate Workbook for every one of their customers.
And some users want to just separate out each customer in their separate sheet.

But if possible I think it's best to keep all customers in their own worksheet if possible

Then with some formulas or Vba you can get all the results you want on each customer.

A few formulas could provide any result you want.
Like how much did company "Alpha" spend in October or March on Cars or Electricity

If necessary a Vba script could be used to copy all Records on a particular company to another sheet if you just wanted a print out on all transactions for that company.

But then that is just my opinion.

Thanks for sharing and noted.
Being new to Excel, EVERY bit of feedback is tremendously helpful.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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