Creating an Automated Invoice with Macros in Excel With the help of Excel VLOOKUP function and Macro

       

  Automated invoice in action, all the input got clear after clicking on ‘Restart Invoice’.

Introduction

I will be converting an invoice into an automated invoice where the end user needs to only select Customer Name, Product, and Enter Number of Invoice & Qty to generate an Invoice. In this scenario, I have been provided with a template, customer information, and products which are shown below.

Stakeholder's requirements for the invoice:  

Invoice No. should look like INV001, if Number “1” is entered by the user

Date of Invoice should be automatic and should show current date.

Disount eligibility: Gross upto 2500- 0%, Gross more than or equal to 2500–2%. Functions & Excel features used: IF(), Data validation, VLOOKUP(),INDEX(),ISBLANK(), Format Cell, protect cell/sheet.

FIg.1 Template of Invoice

Fig.2 Screenshot of Customer information

Fig.3 Screenshot of product and rate information

Converting invoice into automated invoice

Invoice Number — To make the invoice number begin with “INV” I will use custom format.

Fig.4

Fig.5 

After applying the customer filter. We can see in the formula bar I typed 1 and it's showing as INV001.

Date — Now to automate the date I will use IF and BLANK functions. The current date will appear the moment someone will type the invoice number, else it will be blank.

Fig.6

In the above image, we can see the current date is showing since the invoice number was already entered.

Customer Name — Now, I will add customer's names in the form drop-down list with the help of Data Validation. After clicking on the source I went to the page with the customer clicked and dragged till the last customer name.

Fig.7 

First image with option, second image after applying the validation.Above you can see all the customer names are available in the form of a drop-down list. Now as the user selects any customer name from the drop-down list automatically the address should be filled with that customer address. I didn’t write the address(which was supposed to be above quantity) in all of the above images but I updated it later. To automatically enter the address I’ll IF and VLOOKUP function combination.

Fig. 8

If cell B6 is blank then the address will be blank and if a name is selected from the drop-down the address(from customer information sheet) will be reflected corresponding to the Customer name and we want an exact match hence FALSE in the range LOOKUP argument.

Serial Number (S.No.) Now as the user selects any product we want the serial number to be populated automatically and if more than one product is selected the serial number should increment accordingly. To do this first, I will add numbers from 1 to 11 in the customer sheet for reference.

Fig. 9

 Now, I will use a combination of IF and INDEX function. And dragged down till the 18th row to apply the formula in each cell, and since we want the reference to be changed for each cell we will not use ‘$’ as it is a relative reference.

Fig. 10

Above we can see if a cell is blank in Product column the S.No is also blank and if any value is entered, S.No is also populated according to increment.Product and Rate — Now I will add Product in the form of a drop-down list. I will follow the same procedure as done for the customer name and the corresponding rate will populate in the Rate column. In case of rate column we want lookup value to be changed but we don’t want our table array to be changed so we will lock it that is called absolute refence with the help of ‘$’ symbol, and then drag the formula till the last cell.

Fig. 11

 

In the above image, we can see the Serial number and rate populating automatically as we select the product from the drop-down list. Users have to enter the quantity manually as it depends on the situation.

Amount calculation — Now the amount should be calculated automatically and quantity & rate taking into account. And then Gross Amount, Tax, Discount and Net amount should also populate automatically.

The amount will be equal to the product of Rate and Quantity. The amount will be calculated only if quantity is entered otherwise blank.

Fig. 12

Now, moving on to further calculations. Gross amount will be equal to the sum of all the amount. Tax of 5% on the gross amount. As the stakeholder asked to apply discount only if Gross amount is equal or greater than 2500.

Fig. 13

 

Fornula of highlited cellsIn the above image, we can see the formula used for each highlighted cell. Now, we can see if the gross amount is higher than 2500 discount will be applied.

Fig. 14

And if the gross amount is less than 2500 it will be zero.

Fig. 15

We won’t want the user to type intentionally or accidentally type in cells where formula is entered, so we will protect those cells. First I will select all the cells by clicking on top left arrow then in format uncheck the locked cell then okay.

Fig. 16

 

Then I will select the cells where formula is entered and check the locked options. Now in review tab select protect sheet select okay without any password this will restrict the user to type in cells where formula is entered until the sheet is unprotected.

Fig. 17

 

Now for example when I try to enter in the Date cell I get the following message.

Fig. 18

 

Finally, if a user has used the invoice and wants to restart for a new invoice they will need to delete all the information selected through the drop-down list. Now, to overcome this tedious work I will record a Macro that will delete all the selected information to restart for a new invoice.

Fig. 19

 

Now I will assign this macro to a button available in the control group insert option. Copy and paste the code from the macro.

Fig. 20

After formatting the button the automated invoice is ready.

Fig. 21

Thank you for reading!