Create Seamless Proforma Invoices in Excel for Life Sciences Effortlessly

Proforma invoice in excel for Life Sciences

today I'm going to show you how to do a v lookup formula I've already prepared uh a performant invoice and a list of companies on the second sheet and list of products on the third sheet now what I intend to do with this vlookup formula is to take the customer number and enter it in where the customer number is manually and then after I've done the vlookup formulas in the bill to we'll automatically put in the customer's address and the same with the product we'll put the product code in manually then the name and the unit price will automatically be filled in for that product code so what a vlookup formula is is is it's a uh it's a lookup and it's a reference function and it's kind of like a a onetoone match like in a database anyway there's a lot of uses for it it's great so what we're going to do is we have to have uh we have to have ranges so I'm going to name the ranges and we need to always have um the account number because that's going to be it's it's Unique and that's what's going to pull all that uh information like the company name the address the city The Province and the postal code that will be the account number so it always has to be in the First Column when we're highlighting our list it doesn't necessarily have to be in column A but it has to be in the First Column of the the list that we're highlighting okay I'm going to name this list up here I'm just going to name it company going to do the same thing for products and to highlight that and I'm going to name the list range product okay now I'm going to come to my invoice and we'll put a p in and an invoice number and today's date which is 20 6 March not March it's April April 2nd 2016 we'll put the first customer number in for example let's put in ABC coom now now in the bill two I want uh the customer name here on this in this cell and I'm going to put my V lookup formula in it's equals the look up left bracket the lookup value is the first argument is abcc the customer number and because it's in K1 and that is always going to stay in K1 I'm going to I'm going to make it an absolute reference here by putting the dollar sign in front of the column and putting the dollar sign in front of the row then our next parameter is the table array so we put a comma and we've already named our table for uh the companies we named IT company so that's all we need to put in otherwise we'd have to go into uh that company's sheet and highlight that again but because we've named it that's all we need to do and and the company name is in column 2 or in column B in this case we call it column 2 it's a second column from the left because the first column in the left is what we use to look up our values here and then we're going to type in false and that means that it has to be an exact match to the customer number so what is what's it what it's doing it's going into look at everything that's in that list and it's going to match it up because I've got I've put that customer number in so there we go it has ABC company so now I want to put the city in so I'm going to fill that formula down and the city is in column three or column C it's the third column from the left and now I want to put in the province and I the province I mean the city The Province a a comma The Province and then two spaces and the postal code and I'm going to join them all together with a concatenate text formula so I can actually I can uh fill this down and copy it down so the city is in the fourth column so that's all I need to change there and next I want to put the province so I put an and sign not a plus sign and because it's text quotation mark and a comma and a space and a quotation mark and another n this is going to join the two formulas together I'm going to copy this one and paste it and the province is in column five I'm going to want to join the postal code now and do the same thing two spaces in between and it's in column six so there you go that's exactly what I wanted now now I can reuse this I can put another customer number in in here so I can put DF would be the next one and if you look over the left hand side where the buo is that's going to change so what I am going to end up doing is I'm going to use this as a template every time I want to do a performa invoice so now we're going to go down to we're going to enter the product code okay let's uh okay we'll put in Pro a we can put in the quantity shipped just manually and in the description but I'm going to bring it sorry I'm going to bring it down one line quantity shipped 10 I didn't bring it down one line my goodness I'm not thinking okay okay now equals V look up and this time we're going to look at it up against this row here in this column but this doesn't need to be absolute reference because it's not going to stay in just in a25 I'm going to do another another one in a26 so it does not need to be absolute let me put a comma and then our table array is named product and I want the information from column two which is um the product name and faults so that's great that's widget a the unit price if you look up whoops I can just select it here I select that again the product code everything everything for the product is dependent on this product code and it has to be correct or else this doesn't work and I can I'll show you that later and the table array is product and that was in uh column three and false so it's put in it's put in the price and over here I've uh I just put it in a calculation to multiply the quantity by the unit price so I'm going to fill this down a little bit yeah okay so okay for example if I I just want to show you that if if that product code is wrong I'm going to take out the a this is what you're going to get because it doesn't know what it is it doesn't mean anything so it has to be exactly what the product code is so let's do whoops product D for example do another 10 can copy the formula down here here oops price product we going do product e and do another 10 and there you go and uh I've just put in I've just summed that and multiplied it by 133% which is Ontario sales tax to get a total so now every time I want to do a perform a invoice I am going to uh I'm going to use this as a template and the reason I'm using this perform invoice by the way would be I don't know for in this case I I'm I'm doing this because we have a customer that doesn't pay very well so this customer has to pay in advance of shipping but because he has to pay in advance of shipping the invoice in the actual accounting system hasn't been done yet but he needs something to pay from so that is the reason so uh what I would would do now is I would save this as a PDF file and I would email it to my customer so I hope uh you enjoyed this um there's a lot of uh different ways to use the V lookup formula I think it's just absolutely great and it's uh it's a a little bit Advanced but once you catch on to it you'll you'll figure out all kinds of ways to use it thank you