Keeping track of vendor invoices: what would you do differently

[danhendo888]

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.

 

[My Aswer Is This]

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.

 

[danhendo888]

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.