Results 1 to 3 of 3

Thread: Keeping track of vendor invoices: what would you do differently
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Keeping track of vendor invoices: what would you do differently

    https://1drv.ms/x/s!AvjBsEPEq12ngR1M...B1Ew7?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.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,808
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Keeping track of vendor invoices: what would you do differently

    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 by My Aswer Is This; Jul 26th, 2019 at 04:26 AM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Keeping track of vendor invoices: what would you do differently

    Quote Originally Posted by My Aswer Is This View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •