Create Your Perfect Sheets Invoice Template for R&D with Ease

Sheets invoice template for R&D

hello I'm going to show you how to go from a quote to an invoice in just one click but first of all we need to set everything up so open up a new spreadsheet we're going to go to file new from template Gallery so these are Google's templates we're going to scroll down until we get to work and we're going to use an invoice we're going to start with the invoice rather than the quotes just because I think it's easier and then what we can do is we can update all of our company details so put in your company name put in your company address maybe your vat number anything that you want on the top of every quote get rid of this submitted from change this invoice number to be an internal reference and this will be a quote number yeah and we're going to change this to today's date instead of having something that you have to type in here if you just type in today then you will have the dates of today and this will be automatically updated you can change the format of that date format number and then this is my preferred format so you don't get confused about whether this is us or european dates let's add in a logo as well okay insert image insert image over sales and I'm going to insert my B logo get a little bit smaller lovely and then if we do control print we want this current sheet to be portrait who is our invoice and our quote will look a little bit like this control p the next thing we need to do is build our database which is going to hold the information that we're going to fill in the into the dynamic Fields so these are going to be dynamic because who we're going to invoice whether it's a quote or an invoice is going to come from our database everything apart from our internal reference which is going to be the reference we're going to use to select which invoice or quotes that we want to do so we're going to create a database now space so you don't have to see me type I'm going to copy in here's one I made earlier right here we've added in an internal reference number the customer name the address one address two postcode and customer email these are things are going to be the same whether it's an invoice or whether it's a quote we are then going to have a status column which will help us determine whether it's a quote or an invoice I'm going to do this status column is going to be a drop down I'm going to highlight that whole column here scroll all the way back up to the top and I'm going to go to data data validation and my options are going to be I'm going to have a drop down but I want it to be either a quote my job's going to be in progress or it's going to be an invoice or it's going to be paid so we can now have the first couple of couple yes quotes I can have a couple in progress I have a couple invoiced and a cup of eight probably the wrong way around like the early ones are more likely to be than paid but that's fine let's open that up in the in here you can do some Advanced options you can show a warning rather than reject the input you can have a chip an arrow or plain text I think chip is quite a nice one here because you want it to be really obvious but this is something that that you want to change and we don't want them to be able to put anything else other than what we've got in here so reject the input you can always add more items if these don't include every stage of your project for instance you might have things on order okay that's done next we want our invoice or quote to update based off our internal reference number so let's put an internal reference number in here so let's copy this and paste this into here if you notice I copied it into the formula bar rather than copying the whole cell otherwise it copies the formatting as well okay so this is our internal reference I'm going to go through this quite quickly because if you want to know how to automate all of these bits you can watch the how to automate your invoicing video first thing I want to do is payable 2 isn't actually going to change so I'm going to change this to blindfold analysis but my invoice for will change depending on my reference my reference is one your reference is two the formula I'm going to put in here is going to be an index match formula so what this does is this crate we say an index we're going to tell you which row I want out of my customer name this is the column that I want to index so I want you to index that and then I'm going to tell you which value I want so if I go for the first value it's going to be customer name I don't want the first value I want whatever row number this one's in so this row ref one zero zero one is in row two so I want to find where that is so I want to match I want to match reference one zero zero one in this column here in column A and then what we need to do is a comma zero to make sure it's an exact match finish your brackets so this is my invoice for Mr Boss Next we want our address one then our address to then our postcode so all that's going to change we're going to keep the reference is always going to be in column A but our columns are going to change wherever we're looking address one will be column C address two the postcode e we're now going to fix the columns that will never change so our match parts will never change we're always going to be matching our internal reference and it's our internal references are always in column A so function F4 to fix these and they won't change uh column that we're looking up though will change so here we're looking at B but for address line one we want the C or a dress line two two D and then our postcode was hey now when we update our internal reference we are updating address to be the address for the different references table two is staying the same as our date is already Dynamic so tomorrow this date will change the only other thing that we want to change is our quote number our quotes number is currently in column h copy all of this put this in here we go H so our quote number is QT Triple A D lovely now for this next part we want to update our quote here so if if we have a quote we wanted to say quote it's in progress quote if it's an invoice or paid then we want it to be an invoice so actually we're going to look up column G which is our status so we're going to copy this formula that we've already made and we're going to get that to tell us what our status is if we copy all of that again and our status column it's column g wasn't it yes so reference for was in progress number one is a quote and then with the voice what we want to do now is if it is a quote so we're going to add an if formula if it's a quote tell me quote so if all of that stuff that we've already said equals quotes then while on capitals I give me a quote otherwise if it's not a quote I want it to be an invoice so this is uh if formula so our check if this equals this then do this otherwise do this so if our status column which is column G is quote say quote otherwise tell me it's an invoice there you go we've got a quote if I go to number four we've got an invoice we've got a couple of other statuses here we've got in progress and we've got paid so I'm going to copy this whole if formula again and it's going to say if this is a quote then give me a quote otherwise if the status is progress I want you to say quote so this whole section is a it's an awful wise if function and then we need to end the brackets there so if we are a quote quote otherwise if it's in progress quote otherwise it could be an invoice and that will capture if it's an invoice or whether it's paid so we've got a quote here and this will be our one click so let's go reference number four was currently a quote because it's in progress if it's a quote it's still a quote but we've just gone to invoice jobs complete so now it's an invoice so that is a quote to an invoice in one click as I promised you there's a few more things that we want to check so first of all let's have a little look there's your quote let's go to one which is an invoice this is what our invoice looks like lovely we've got a lot of space down here so we probably want to fill this out with different information depending on whether it's a quote or an invoice so let's add in some more lines down here and what I want it to say is and additional details terms as agreed but I want to change whether it gives us a company information and our bank details like this if it's an invoice but if it's a quote I want it to tell us this quote is valid for three months if you'd like to discuss any aspects of the quote here's an email address and I'm going to leave in the video T registration number and company number so the only two lines that change are these two lines in the middle and I'll redo them so you can see what's happening and we're going to do the same things we did up here where we're going to say so if status is a quote then say quote if it says in progress then blah blah blah blah I'm going to copy all of this down here and I'm going to say if it's a quote then I want to say this quote is valid for three months I'm going to say the same thing if it's in progress run the in capital letters otherwise I want it to provide my bank details so otherwise please pay it into my bank account be lovely this is my real bank account by the way if you want to pay me some money I'm more than happy to provide my bank details my second line is to be if it's a quota in progress if you'd like to discuss any details please uh email me at sales at Bloomfield analysis um do the same thing here copy paste so it's a really long formula but it's a lot of copying and pasting so if you'd like to discuss any aspect of your quote please email this otherwise I want to tell people that the vat amount is what's due to hm revenue and Customs I'm assuming you have a very similar thing in other countries right so there you go reference one this is a quote so this is saying quotes valid for two months and if you would like to discuss please email me I'm keeping my registration numbers here however if instead of a quote we have an invoice so let's change reference to to an invoice one click here's our invoice then we're going to provide our bank details and tell them that the v80s for the government let's have a look there you go it fits my page much more nicely the only bit we haven't done is the items that's because there's a few different ways of doing this items part you can either just have one item which just reads from the database or two items if you're always going to have one or two items just read it from the database like here but if you're going to have lots of different items you can either have a drop down list with just one list of items or you could have like a double drop down list which gives you a broader category and then a moral detailed item so if you just want the single drop down list the video is do a single drop down list my video for the kind of double drop down if you've got loads of items I think it's called at the moment the ultimate drop down then that's the best video that you should watch in order to fill these in but this was meant to just be a quick one on how to go from a quote to an invoice voice in one click so hopefully it wasn't too long and it wasn't too short next I'm thinking of doing either CRM so what you'll be able to do is email everybody whose invoice is overdue with an email saying overdue invoice or I'm thinking of doing a food like shopping template one which doesn't look a bit different so you put in your meal plan what you want to eat for the week and then it automatically creates your shopping list for you so if you've got a preference on any of those let me know put it on the comments subscribe so you can see any new videos coming up and please share thanks bye