Associate Multiple Invoices to a Single Purchase Order

ExcelEndeavor

New Member
Joined
Oct 13, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
Quick Background:

I have a budget workbook with 7 different worksheets - one for each "Business Line" (department). Budget items are added through a userform that currently allows the user to add up to 3 invoices when they input a purchase order number. The details of those invoices (number, date, amount) are captured in specific columns in each worksheet, depending on which business line is selected in the userform. I am now learning from my AP dept that there can be up to 14 invoices for a single purchase order, so I am looking for ways to organize the information.

This screenshot shows the userform that is used to input the budget info. The red highlighted area is where invoices can be added and the info is transferred to one of the worksheets, depending on which Business Unit is selected.

Invoice - Original View.jpg



On the worksheets, the invoice information is mapped to the columns shown when the "Submit" button is clicked in the userform.

Invoice - Columns.jpg



The mapping code of the submit button in the userform shows the invoice data going to:

VBA Code:
rSh.Range("I" & nextRow).Value = Invoice1Nbr
rSh.Range("J" & nextRow).Value = Invoice1Date
rSh.Range("K" & nextRow).Value = Invoice1Amnt
rSh.Range("L" & nextRow).Value = Invoice2Nbr
rSh.Range("M" & nextRow).Value = Invoice2Date
rSh.Range("N" & nextRow).Value = Invoice2Amnt
rSh.Range("O" & nextRow).Value = Invoice3Nbr
rSh.Range("P" & nextRow).Value = Invoice3Date
rSh.Range("Q" & nextRow).Value = Invoice3Amnt


The Question (two fold):

1. What is the best way to organize this? If I create more columns to accomodate the other 11 possible invoice fields, it would result in 3 columns per invoice (number, date, amount) - a grand total of 42 columns (all of which would be hidden from view - they would only exist for oranization. I would have to also figure out how to have the new invoice details map to the next available set of 3 columns (number, date, amount). Or would a different method work more efficiently?


2. How do I code this input method?

These are just userform mockups that I created - they are not coded:

The one on the left shows how the invoice portion would look (in red). By clicking the add invoice, another userform would open (the right side screenshot) where the invoice details would be input. Once the invoices has been added, it would show in the listbox of the original userform (highlighted in red) and keep a running list of invoices. Lastly, the invoice would only count mathematically if the "Paid" checkbox has been clicked.

Invoice - Record.jpg
Invoice - Add Invoice.jpg



I know this is a ton of information all at once, but what do I do and where do I start? Thank you in advance.

Here is the file if you want to explore...

Budget
 
Quick Background:

I have a budget workbook with 7 different worksheets - one for each "Business Line" (department). Budget items are added through a userform that currently allows the user to add up to 3 invoices when they input a purchase order number. The details of those invoices (number, date, amount) are captured in specific columns in each worksheet, depending on which business line is selected in the userform. I am now learning from my AP dept that there can be up to 14 invoices for a single purchase order, so I am looking for ways to organize the information.

This screenshot shows the userform that is used to input the budget info. The red highlighted area is where invoices can be added and the info is transferred to one of the worksheets, depending on which Business Unit is selected.

View attachment 122919


On the worksheets, the invoice information is mapped to the columns shown when the "Submit" button is clicked in the userform.

View attachment 122920


The mapping code of the submit button in the userform shows the invoice data going to:

VBA Code:
rSh.Range("I" & nextRow).Value = Invoice1Nbr
rSh.Range("J" & nextRow).Value = Invoice1Date
rSh.Range("K" & nextRow).Value = Invoice1Amnt
rSh.Range("L" & nextRow).Value = Invoice2Nbr
rSh.Range("M" & nextRow).Value = Invoice2Date
rSh.Range("N" & nextRow).Value = Invoice2Amnt
rSh.Range("O" & nextRow).Value = Invoice3Nbr
rSh.Range("P" & nextRow).Value = Invoice3Date
rSh.Range("Q" & nextRow).Value = Invoice3Amnt


The Question (two fold):

1. What is the best way to organize this? If I create more columns to accomodate the other 11 possible invoice fields, it would result in 3 columns per invoice (number, date, amount) - a grand total of 42 columns (all of which would be hidden from view - they would only exist for oranization. I would have to also figure out how to have the new invoice details map to the next available set of 3 columns (number, date, amount). Or would a different method work more efficiently?


2. How do I code this input method?

These are just userform mockups that I created - they are not coded:

The one on the left shows how the invoice portion would look (in red). By clicking the add invoice, another userform would open (the right side screenshot) where the invoice details would be input. Once the invoices has been added, it would show in the listbox of the original userform (highlighted in red) and keep a running list of invoices. Lastly, the invoice would only count mathematically if the "Paid" checkbox has been clicked.

View attachment 122922View attachment 122923


I know this is a ton of information all at once, but what do I do and where do I start? Thank you in advance.

Here is the file if you want to explore...

Budget
The normal way to store such information is to have each instance of the entity on different row. The entity being an invoice and the instance being each invoice.

Long and thin datasets are easier to work with in Excel than short and fat ones.

So instead of having three sets of three columns a new row is created for each invoice.

You will need to have a column for the PO number to link the multiple invoices to the PO.

Instead of showing the invoices in red you can have a scrolling listbox that just lists the invoice for the PO.

The filter function can be used to filter the invoice sheet for the appropriate invoices.
 
Upvote 0

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