Create Your Libreoffice Invoice Template for it Easily and Efficiently

Libreoffice invoice template for IT

okay finally should have thought about this earlier I've got the best numbering system possible for spreadsheets this is a template dashboard for a invoice that does ours obviously um I've got other spreadsheets that do things like creating solar systems so therefore this numbering system here is chronologically based using time and therefore it's Universal no matter what spreadsheet I open it will be in numerical order because it's in chronological order so therefore it's important to put the year on the front month to second date after that because if these were around in different orders well then later invoice will have a smaller number let's say the first of May if the day was at the front first zero one is a smaller number than 24 of March yet it is later on in the year so we don't want that so we put in this order 23rd zero three notice has a zero on the front there we don't want to have a smaller number also until we hit the 10th say October so we want to keep that numbers the number of digits the same and then we've got the hour and minutes all in that order to keep chronological slash numerical order and then we've got qte on the end that's part of the invoice number and I've got a drop down menu here tax invoice now if I want to go to tax invoice I've got the same number with inv but if I want to start afresh estimate I can start afresh and the number has updated because that's first met first option on the menu is the current time whereas the second one maintains the old one now the reason for that is because drop down menus have to be manually reset so as long as I choose the second option it's going to stay the same so I've made my estimate I want to keep the same number for quotation now so go to the second option quotation and if I go down to contract I go second option still maintains the same number all the way to the end until I've actually sent a knit receipt and got paid same number again and then if I want to refresh from the start I'll choose estimate and choose the top option and it's moved along one minute as you can see and the number has updated so to start afresh I choose the top option and to keep things going I uh with the same number with a different set of letters on the end to differentiate between them but see still keep them all related with the same number I'll choose the second option so how's this done well first of all let's have a look at drop down menus probably if you're not familiar there it is what's where am I getting all this information from well you go to data validity and you choose the cell range and this one references a sheet type called tables and u74 let's go and have a look at that pull out got lots of stuff in here um don't use all of these algorithms I generally cut and paste them into different spreadsheets and use them we push in here you push in here a big big big big big big there it is estimate there's that list there the cell range that I'm getting that from this is a different algorithm we won't worry about that but if I ever change this here it would automatically update my invoice number generator which is referenced from that table there see u68 which is where that list is from column U row 68 and so on and so forth so that's just cross referencing I want to change that to uh I don't know time okay go back to where I was one algorithm I'm not using uh time there we go now what comes next is uh this this here EST is T was where I'm getting St EST from that stands for estimate it goes into my number and that's coming from it's referencing dashboard F1 okay we'll go back and have a look at that that's F1 so that's where that's coming from so we go back to tables and now to change the q2e and this is a vlookup function so it basically references F1 and in dashboard and comes down this left hand column that's what vlookup is vertical lookup in column one finds out what it is what is it that the quotation and then it'll search this range here aq4 to AR10 which is inside the red square a aq4 up the top left and AR 10 down the bottom right and column two very important here column two it moves across to column two and finds out what's cross-referencing with quotation it's qte and it will put it in the cell there you go so now let's look at this formula here how did I get from time which is basically now brackets that won't work because now you've got to break it down into this number so we start by just going year and referencing the time month and referencing the time and day all these formulas are very basic our and minute that's as far as I want to go because I'm not going to do more than one invoice per minute now we need to get um change it to text why because text is uh we won't go into that one we'll go to this one text find out what that is send it to text and put it in the cell you can pull down the bottom right hand corner and copy like that and now I've got the same formula doing the same thing in all of the rows now what was different about this one well it's just it's the same thing it's got text there but it is um also putting a right function in there let's break that down into two two um just sort of columns before let's change that to text basically change that formula the text so it's like the others in fact a quicker way to do it would be just to grab that bottom right corner pull it up there it is now we want to get rid of the 20 on front of the 23 so therefore I go equals right Open brackets there's the cell I want to do right I want to do from the right two digits so put number two in there close the brackets and press enter so now we've got the 23 but I don't want that extra column so I've combined them into one formula where was it somewhere here we are now there it is obviously the the formula that's in the brackets within the brackets is the one that's done first it's like having the first cell and the right for a function is on the outside so therefore it is like it's being in the second cell along so if you're a bit of a you can do more columns but when you're used to it you can combine the two and we move right along so there we are we've got our two digits or have we know this one here we look at this column here anything less than 10 we put a zero notice how the zero is in inverted commas that makes it text if I just put 0 without the inverted commas it would be a number zero and then we do if it's less than 10 put 0 in text otherwise inverted comma inverter comma is nothing which is what we have here and all the way across and once again grab the bottom right and kind of pull it down Universal so now we have only the single digit numbers with a zero there index and three here in text everything's in text great now we go concatenate this is concat now we want to do these two columns together so this column and this column join together to make that number and because we've got a zero here that column and that column joined together to make that number so on and so forth if we look at that we've got the same thing there all the way down uh pretty much that's same thing again there you go oh we've updated a minute here 25th now then we just go concatenate again very easy because they're all text they're not numbers you have problems with numbers for example if I put 0 3 here it's going to turn into a three without zero in the front so we want text we've got our text there fantastic and we've got our vlook up here and we've got qte you know what that that's in the middle why because if I get rid of a space that I've left in there now I can close up that Gap but I like the I like the space you know so I'm going to leave it like that save it now what is this this is just concatenate again all three cells are oh yeah across the row there that's it and that's where I get my quotation number the fresh updated one and at the bottom here is the one that's residual uh this one here is referencing the cell here and because this has a drop down menu as well it can only be updated manually through the drop down menu so this number continuously updates and this one can only be updated manually so we have that situation as I described earlier where I want to keep the same number and change the letters on the end it stays the same number when I choose the second option which is over here concatenate but we've got qte on the end here we don't want the qte on the end no so we've got a left function here left dashboard that cell there M2 we want the first 10 digits only which is the number and then that's it so now we've got rid of the qte on the end and we can put a different signature on the end and concatenate a whole lot so now when I go over here the dashboard pull down the drop up change it first text invoice pull down go to the second option keep the same number now it's got iron V on the end and at the end of the job I go to receipt I've been paid and same number again and you know what I want to use this spreadsheet again and start again from the start and choose the top option which is always constantly updated and now we have a new number so and also on other spreadsheets the same thing the same formulas in there so they all coordinate with each other perfectly it's a brilliant idea and this here is just what I grab for PDFs so I'll just usually grab that lot there and go file export as a PDF and pretty much that's how it's done see you later