Streamline Your Operations with Automated Invoices for NPOs

Automated invoices for NPOs

Aloha I'm Glenn poo I'm Chad neelson I'm kid IG we are the Backstreet Boys we are going to show you how to make an automated invoice system uh as we can as you can see we started off uh with a customer base we have a whole list over here and so you can uh set it up like this if you want it was just an example uh we have products as well for sale and this is our custom made invoice you can have it set up like this or however you want but the most important part would be how to set up an an invoice where it would bring out the database for customers and also products and just bring it up instead of C uh uh manually typing it one by one and uh we will show you how to use the vlookup function and also how to protect it so we will uh begin with the vlookup with Chad now just to take a look at what the vlookup does let's go to customer number we're going to change the one to a two this part's pretty cool when you press enter it changes the name it also changes the City Country address everything to Discount and that's taken from this this this customer name to because we just typed in the number two there and that's basically what the vck does it it saves you Time by entering a lot of information so to enter in this equation we're going to go to form formulas and then we're going to insert function that's where you type in oh actually one second we have everything locked we're going to go back and unlock it this is a something that we're going to show you here at the end and we're going to type in our password that's a loha um so now we're uh we're okay and we're unlocked you can pause okay sorry about that we got it unlocked here so just check take a look at the top here at our V our vook formula um so to um enter this formula we got to go to um just again we're going to go to formulas insert function and as we type in V this pops up now C9 is important we're going to click on Cell C9 that's our customer number that's the anchor to everything we're doing that's where you put in the number all the information pops up next we're going to go to table array and that's where you go to customers we probably want to highlight table array just to change and then yeah we highlight the entire table and here we're going to highlight the entire table put it in table array and that gives us all our information that is necessary um for the entire thing so this V lookup can draw from it as you can see we're deleted then we're going to highlight it we're going to put it all in and there we go set all the sales next we're going to do number two the reason you do that number two is because that's the customer name if you put into to so for instance if you put in three that would be the addresses and four would be City but for right now we need two for customer name and lastly we're going to put in the word false that way it brings up all the true information anything false would be um Na and will not work um so that that that that takes care of all of our uh equations and so we're going to have to go through and change each one of the red zones and so here are street addresses we're going to change it to a three and the reason we do that is because the third one is addresses and fourth would be City fifth state and so we went there to go back so there's the number three so City would be four and you got to go through and change each one of those that way everything's formatted to vlookup and you should be able to go from there okay now Chad just talked about how we use the vlookup to draw the customer information so that would get put on the invoice for the customer information the shipping information the customer terms like the discount and discount days and now we can use that same V lookup formula to find the product information so what we do is click on description and we do the same thing we'd go to formulas insert function we'd have the vook up in there and instead of referencing the customer spreadsheet we'd reference the product spreadsheet so we' um let's see first we' reference we'd want all that in there all the data and um let's see hold hold on here there we go so we have that all the data referenced in there and then to specify the description in the the the spreadsheet it's under column two so we'd have a two in there and again we'd put the false in there to make sure everything's accurate right there okay and so now and just to do the same thing for the the unit price we'd also put in the v lookup formula but then the only difference is we change the column index number which we changed to three because unit prices in the third column on the spreadsheet right there and now with that properly in we can accurately show how the V lookup works we can go to the product number and type let's say product 104 hit enter that comes up as the description of the plan and also has the the unit price which is $4 right there and now we do the little totaling boxes we do the total price we do the simple formula of quantity times the unit price and that gives us the total price for that purchase and then we also do the same thing for the the row below and now we do the sub total we do a simple sum formula summing those two together and we add the sales tax and the shipping and then now to get our final total we would do a simple sum formula of the subtotal sales tax and the shipping okay and now just to make sure um to show you how you can lock your your automated invoice spreadsheet down to prevent any tampering or any mistakes in the keystrokes by the the clerking the wrong cells we simply go to um format we clicked on format cells on the very bottom and now we go to locked so we lock all the cells down we click okay and to let the clerk have only access to the certain cells he needs we go back to format cells we uncheck unlocked we do that for the customer number the product quantity and also the invoice number up at the top of the page then now we create a password to protect the sheet just type in Aloha reconfirm the password okay now the the whole spreadsheets uh tamper proof so now we can try to type in something in the description oh and there's an errow that comes up but now the clerk has access only to the Yellow Boxes the only thing he needs access to like the customer numbers product numbers and the quantities sold okay and that right there is our simple uh do it-yourself automated invoice and that's all done using the V lookup