Automatically create invoices from sales invoices1 Excel (Excel)

There are many convenient software systems that automatically output invoices as you record sales data if you pay for them, but many people may not be able to install software because they "don't have enough money to pay for it" or "management won't allow it" and are making each invoice by hand.

So, let's create a system that can automatically generate invoices from sales statements.

Create a billing list

The first step is to create a list of billing clients to whom you will send invoices.

If you already have a billing list, you may use that. In that case, please replace the necessary items with the ones in this data.

The minimum fields required for invoice output are "billing name," "zip code," and "address.

If you want to add other more detailed features, you can add more items here. For example, you want to display phone numbers as well, "Remarks at the end of the invoice," etc.

Check the structure of the sales statement.

Next, check your sales receipt.

Is the billing name an exact match to the billing list?

It is important to note that the "billing address list" created earlier and the "billing address" on the sales invoice must match exactly. There are three major ways to achieve an exact match.

1. Using the "Entry Rules" list
2. Use the billing address code as the key, not the billing address name
3. Enforce rules such as "All letters are double-byte characters" and "Corporation must be Co.

While it would be more reliable if you were the only one entering the information, method 3. is not realistic because it is inevitable that the person working on the system may vary or change the rules without your permission.

The other two are either 1. or 2. but both of them guarantee an exact match, so it is up to your preference.

In this example, we would like to use method 1 because the number of columns can be expressed with a small number of rows.

Please refer to this article for information on how to create a list of input rules.Creating a pull-down (drop-down) Excel (Excel)

View (print) invoices

I made an image of the invoice.

The letters are.blueare cells that contain functions (formulas). The black and white text are populated with values, so the values do not change.

The following is a step-by-step explanation of how to make it.

Allow selection of billing address

The first step is to make a pull-down selection of the billing address.

See also the explanation of how to use the pull-down menu here.Creating a pull-down (drop-down) Excel (Excel)Linking two pull-downs (drop-downs) without macros (for linking only one set) Excel (Excel)

Select the column for the billing name in cell "B6" and click on the "Data" tab, then on the "Input Rules" button, and then on "Data Input Rules".

Change "Input Value Type" to "List" and select the original value.

Select a range of billing partner names, press the "Enter" key, and finally press the "OK" button.

You can now change the billing address in the pull-down menu.

Enable automatic display of billing address

The INDEX and MATCH functions are used here. If you want to know how to use them in detail, please refer to this article.How to use Excel (Excel) INDEX and MATCH functions in combination

In the "B4" cell, put this.

=INDEX(Billing address!$E$3:$E$14,MATCH($B$6$C$3:$C$14,0))

Then the billing address entered in cell "B6" was displayed.

Similarly, "B3" and "B5" cells are also entered. However, in the "B3" cell, the "〒" mark is inserted before the zip code.

="〒"&INDEX(billing address!$D$3:$D$14,MATCH($B$6,billing address!$C$3:$C$14,0))

Now we have a system whereby switching the billing name switches the address, etc.

Control the payment due date from the issue date

There may be many rules regarding the issue date and payment due date, but here we would like the issue date to be the date of output and the payment due date to be the end of the month following the invoice date.

The "E4" cell contains

=TODAY()

The "E5" cell contains

=DATE(YEAR(E4),.MONTH(E4)+2,0)

Enter

The meaning of this function isDate in cell "E4(at sentence-end, falling tone) indicates a confident conclusionAfter 2 months(at sentence-end, falling tone) indicates a confident conclusionEnd of previous monthThis is what it means.

Put a statement on the invoice.

Now I want to display the "Sales Detail" data to the billing address, but I need to exclude the rows with different billing addresses in the data rows, so I can't simply pull them in with the index function.

There are several ways to achieve this, and we will show you some of them in the next issue.Automatically create invoices from sales invoices 2 Excel (Excel)