Associate Multiple Invoices to a Single Purchase Order

[ExcelEndeavor]

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.

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

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

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.

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

 

[HighAndWilder]

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:

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.