1. We need to have the basic inputs such as the Product Details in the product sheet and the Company Details in the Customer sheet like the following two figures. 
2. We created the basic outline of the GST invoice for “N” Fruit Supplier. 
3. Give some basic fixed inputs in the required places. So, we will fill up the indicated region first. 
Type 1 as Invoice No, ON as State Code (Code for Ontario), and for having today’s date we have used the TODAY function. 
4.Move into the filling of the banking details. 
Use your desired A/C Name and A/C No. (we used Michael James and 0034567) 
Get to know the way to create a dropdown list to change the values easily in the bill form. 1. Make a dropdown list for the GSTIN numbers of the companies to whom the products will be supplied. 
➤ Select the cell B8 where you want to have the dropdown list
➤ Go to Data Tab >> Data Tools Group >> Data Validation Option 
The Data Validation dialog box will pop up
➤ Select the List Option in the Allow box
➤ Type the following formula in the Source box and press OK
=Customer!$B$5:$B$10
Customer! Is the sheet name and $B$5:$B$10 is the range containing the GSTIN numbers in that sheet for different companies. 
In this way, you will get the dropdown sign in your desired cell and now you can choose any of the numbers from this list.
We selected the GSTIN number ONAAACXXXX5D1E5 for this example at first. 
The selected number will appear in cell B8 
2. Use the GSTIN number to look for the values of the Company Name in the Customer sheet and then have this value in the indicated region. 
➤ Type the following formula in the selected region
=IFERROR(VLOOKUP($B$8,Customer!$B$5:$E$10,2,FALSE),"")
$B$8 is the lookup value, Customer!$B$5:$E$10 is the table array where Customer! Is the sheet name, 2 is the column number of which we want the values, and FALSE is for an exact match.
If sometimes VLOOKUP returns an error, then IFERROR will convert it into a Blank 
➤ Press ENTER
Get the Company Name, A, for the GSTIN number ONAAACXXXX5D1E5 
Use the following two formulas for getting the Area and the Contact No. of the company respectively.
=IFERROR(VLOOKUP($B$8,Customer!$B$5:$E$10,3,FALSE),"")
=IFERROR(VLOOKUP($B$8,Customer!$B$5:$E$10,4,FALSE),"")
See that, we have the following Company Name, Area and Contact No. for the corresponding GSTIN number ONAAACXXXX5D1E5 
When you change the GSTIN number by using the dropdown list then the corresponding Company Name, Area and Contact No. will be changed 
1. Type the HSN Codes of your products in the HSN Code 2. Use the HSN Code to look for the values of the Product Name in the Product sheet and then have this value in cell C13. ➤ Type the following formula in this cell
=IFERROR(VLOOKUP(B13,product!B5:F9,2,FALSE),"")
B13 is the lookup value, product!B5:F9 is the table array where product! is the sheet name, 2 is the column number of which we want the values and FALSE is for an exact match.
If sometimes VLOOKUP returns an error then IFERROR will convert it into a Blank 
➤ Press ENTER
➤ Drag down the Fill Handle tool 
Get the Product Names in the Product column 
Use the following formula to get the Unit Price of the products.
=IFERROR(VLOOKUP(B13,product!B5:F9,3,FALSE),"")
3. Type the number of your products in the Qty 
4. Get the prices by multiplying the Qty and Unit Price in the Amount column
=D13*E13
5. Get the CGST rates and the SGST rates by using the values of the HSN Codes by using the following formulas.
=IFERROR(VLOOKUP(B13,product!B5:F9,4,FALSE),"")
=IFERROR(VLOOKUP(B13,product!B5:F9,5,FALSE),"")
6. It is the turn to get the CGST Amount by using the following formula
=F13*H13
We used the following formula for having the SGST Amount
=F13*J13
For calculating the Taxable Amount you can use the following formula
=I13+K13
7. Calculate the Total Amount of these products. By using the following formula, we will sum up the amounts of the products
=SUM(F13:F17)
SUM will add up the values of the Amount column To get the total CGST Amount we will use the following formula
=SUM(I13:I17)
To have the total SGST Amount you can use the following formula
=SUM(K13:K17)
After having all of these amounts, we will sum up them by using the following formula to get the Total Amount
=SUM(F19:K21)
After performing all of these calculations, we will get the following bill form 
Use two VBA codes to refresh the bill form for performing calculations for new data and then saving the bill form. 1. Go to Developer Tab >> Visual Basic Option 
The Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option 
A Module will be created. 
➤ Write the following code for resuming the sheet
Sub resumeinvoice() Range("C4").Value = Range("C4").Value + 1 Range("B13:B17").ClearContents Range("D13:D17").ClearContents Range("B9").ClearContents End Sub
This code will clear these ranges and add up 1 with the value in the cell ➤ Type the following code to save the code in your desired location
Sub savegst() Set ws = Worksheets("Invoice") ws.Range("A1:K27").ExportAsFixedFormat xlTypePDF, _ Filename:="C:\Users\Mima\Downloads\" & ws.Range("C4").Value, _ openafterpublish:=False End SubThe Invoice is the sheet name and A1:K27 is the range of the sheet you want to save 
2. Go to Developer Tab >> Insert Group >> Button Option 
A plus sign will appear and drag down, and to the right side, this sign. 
The Macro name dialog box will open up 
➤ Select the resumeinvoice macro name to create a button for resuming the operation
➤ Press OK 
We edited the name of the button as Resume 
Create a Save button for saving the bill form as a PDF by using the savegst macro name. 
3. After pressing the Save button the bill form will be saved as a PDF like below in your desired location 
4. When you press the Resume button, the Invoice No will be increased by 1 and the values of the indicated boxes will be removed 
After giving the inputs in the indicated boxes of the previous figure you can get a new bill form like below 
Download Practice Workbook
GST Invoice.xlsm
