Simplify Your Billing Sheet in Excel for Purchasing Processes

Billing sheet in excel for Purchasing

[Music] hi everyone in this video we will be learning how to automate your invoice system using simple formulas and macros in Excel you can save your invoice spdf using custom name and location by just a single click [Music] you can also create new invoice with the next invoice number by just a single tap [Music] all the VBA codes are mentioned in the description let's get started we have an invoice template here and a list of customers with their details now we want the customer details to fill in automatically use the vlookup formula lookup value is the customer name now select the table we have column 2 for address make sure to change table reference to fixed reference now copy the same formula for others change the column number in both formulas for date use this formula this will update the date automatically every day [Music] to avoid this error do this foreign type this format [Music] now only type the number of days due date is updated automatically [Music] let's add a drop down list for customers foreign customer detail is automated [Music] [Music] to avoid this change the formula like this if quantity is greater than one then apply formula otherwise keep it blank [Music] [Music] now let's apply macro code to save the invoice spdf with just a single tap [Music] enter the code now you'll have to change the code as per your requirement by following these steps here you will write the cell reference where your invoice number is mentioned for example here it is in cell E3 [Music] now here you will write the cell number where customer name is mentioned [Music] here you will specify the location to save the PDF let me show you an easy way to do this let's say you want to save it here then right click and go to properties copy the location and paste it here now write the name of the last folder save the macro and close the window insert a button like this assign the macro and press ok right-click to edit the text to save it as PDF in one page only do this [Music] let's try it out saved as PDF in a single click now let's add the macro to create new invoice with the next invoice number foreign [Music] now you'll have to change the code as per your requirement by following these steps in the bracket with inverted commas enter the cell references which you want to clear contents of when next invoice is made for example in this invoice customer name [Music] items description [Music] and credit terms will be cleared when new invoice is made save the macro and close the window insert a button like before let's try it out [Music] all the invoice is cleared with the next invoice number to remove this error change the formula like this if n a error then keep it blank [Music] do the same for other to formulas now make a new invoice as you like make sure to subscribe [Music]