Unlocking the Logistics Invoice Format in Excel for Research and Development

Logistics invoice format in excel for Research and Development

in this series of videos i want to show you how to create your own invoicing system i'll start off by describing how to create the invoice itself in terms of formatting and structure also how to keep a customer database and how to keep a record of invoices and you'll see here that wherever we have an invoice that's overdue it appears in red but if we say it's paid then the red background disappears so it's a good way of tracking your invoices you can also see over here when the invoice was emailed and you've got a link to the invoices as well so you can view them now in other videos i will also go through these macros that i've created you've got a macro that will save the invoice as an excel file a macro will save it as a pdf file a macro that will automatically email the invoice to the customer a macro that will add the invoice to the record of invoices and the last macro here will clear the current invoice so that you can start a brand new invoice and it will also automatically generate the next invoice number for you if you want to learn how to create this invoice from beginning to end including all the macros run by these buttons then follow the link in the description of this video to the playlist that contains all the videos in this series okay so now we've designed our invoice what we want to do is to add the invoice details to the record of invoices now the record of invoice is going to record the invoice number company invoice amount date issued and date due so that is just the date plus terms in days and then if the invoice is overdue so if the date due is in the past this part of the invoice row becomes red but if i select that it's paid we'll get rid of that red background over here we're going to leave space for the path to the invoice as saved as a pdf and then eventually the invoice saved as excel file so you've got those two options depending on how it's been saved and also we're eventually going to create a macro that will display when the invoice was actually emailed now we're going to automate that record of invoices with this add to record button so we'll just create our invoice click on the add to record button and it will automatically appear in this table so this is where we left off last time as i said with the invoice designed and we've already got a customer sheet but we're going to create a new sheet called record of invoices and i'm going to put in my column headings okay so i've got my column headings now we're going to apply some conditional formatting to columns a through to f and we want the background of these rows to be read if the invoice is overdue we have to ask three questions basically first question is does the row contain an invoice number so we don't want to format blank rows with a red background color second question is is the date due in the past and the third question is is the invoice not yet paid so we're going to have either blank or yes in this column here so if it doesn't contain yes then we want a red background to the row now the first job is to select all the cells that we want to apply the conditional formatting to and i'm going to go down to row 1000 so i click into a2 i go up to the name box i type colon and it'll be f1000 then ctrl shift enter i'll select down to that row then on the home tab of my ribbon i'm going to go to conditional formatting new rule and i'm going to use a formula to determine which cells to format and as i've explained three things need to be true it says format values where this formula is true so with three rules i've got to return one true result to do that i can use the and function and i'm going to say is a2 not equal to an empty text string now at the moment a2 is locked on both column and row but i actually only want it locked on column and that's because when this formula is copied down to the rows underneath row 2 i want it to look into a3 not a2 need to lock the a because the condition always relates to column a comma the next rule is is the date due in the past so i'm going to say is e2 which i'm going to fix on column but non-row less than today's date i can use the today function to return today's date comma and then the last rule is is f2 also fixed on column not equal to yes and yes it's a text string so it goes in quotation marks close the bracket and then i'm going to format with a red background click on ok and click on ok the next step is to have a drop down list in this column the paid column that just allows us to select yes if the invoice is being paid so i'm going to select from this cell down to f 1000 ctrl shift enter and to add the drop down list i'm going to go to data in the data tools group i'll go to data validation allow a list and i just want yes as my one option click on ok now the next step is to automate the process of transferring the current invoice details over to the record of invoices i don't want to have to do this manually i just want to click on a button and it to all be automated for me now we can do this with a vba macro and because we're going to be using macros we need to make sure our invoice is now saved as a macro enabled workbook so to achieve that you go to file save as and you need to make sure that your file type isn't excel workbook but it's a macro enabled workbook now when you're dealing with macros it's a good idea to have the developer tab on your ribbon and that doesn't appear by default so to get it to appear just right click on one of the other tabs on your ribbon and go to customize the ribbon and then tick this option here developer and then your developer tab will appear on your ribbon over here you've got a button for the visual basic editor now that should bring up the visual basic editor and the first thing i want to do is to create a module so if you go up to insert up here and go to module and in your module you can store all the macros you're eventually going to need for this invoice system now we're going to call this macro record of invoice so the first line of code is sub-record of invoice open bracket close bracket and that will create an end sub line for you we'll just create some space between those two lines now we've got to declare a variable for every piece of information that we want to capture from the invoice so we'll start with the invoice number i'll say dim invoice number as long then i want to store the customer name so i'll say dim customer name as string then i need to capture or store the invoice amount so i'll call that amt and store that as currency then i need to store the date of issue and i'll store that as date and i also need to store the term 30 days 60 days or 90 days and i'll store that as byte and then i'm going to say what i'm going to store in those variables so to make this clear i'm just going to close down this project explorer and move this over here so you can see which cells we're referring to so the next line of code is going to tell excel what to store in this invoice number variable it's essentially going to be the value in cell c3 remember these cells are merged so the actual value is in c3 you can see that if i select the cell it says c3 up there invoice number equals range c3 and i've got to do the same for the other variables customer name equals range b10 amount equals range the amount is right at the bottom of the invoice that's in i-41 date of issue is in range c5 and term is in range c6 the next thing i need to think about is where in the record of invoices i'm going to place this record now in the first place that's going to be in row 2 but if there was already in something in row 2 then i'd want to insert the next record in row 3 and then 4 5 6 etc so i need some way of ascertaining which row is the next available row now to do this i'm going to declare another variable called next record or next wreck and i'll store that as range and then because range is an object i need to set it we'll say set next rec equals now i'm going to reopen the project explorer and i want you to see the record of invoices is sheet 3 and sheet 3 the code name for the sheet is the most reliable way of referring to that sheet it can't be changed by the user and it can't be changed by changing the order of the sheets so that's how i'm going to refer to this sheet so next rec equals sheet3 dot range what i need to do is go right to the bottom of the sheet and then back up select the last cell in range a and then use the equivalent of control up arrow key and that will get me to the next available row i'll show you what i mean if i had a value here and here now i'm going to go down to the bottom of the sheet to row 148 576 and then if i do ctrl up arrow key you can see it takes me to the first value that it finds in this column then i'd want to move down one row and i know that's the next available row in my spreadsheet i'll just get rid of those so first of all i need to refer to that last cell in column a which is a one million forty eight thousand five hundred and seventy six and the equivalent of control up arrow key in vba is dot end excel up and then i need to move down one cell so i'm going to use offset to move down one row row offset one column offset zero now in that cell in column a i want to put the invoice number so i'm gonna say next rec equals invoice number so remember that's the string that's stored in this variable here in the next column i want to store the company name so it's a next rack dot offset no rows but one column that would move me over to column b and i'd say that should contain the customer name so now i can begin to copy these lines of code and i'll say next column across that'll be column c needs to contain the amount next column column d needs to contain the date of issue the next column is going to be date due and that's the calculation is date of issue plus the term okay let's see if this macro works so it'd be on the invoice template sheet and let's play the macro and if i go to my record of invoices you can see that those details are now stored now let's change this invoice date here to the first of the first 2022 so the date of recording is the 10th of february 2022 so this invoice is going to be overdue once i add it to the record of invoices and i want to see if it comes up with a red background and you can see it does until i go here and say yes the invoice is paid the final step is to create a button on the template to run the macro that we've just created so on the developer tab go to insert under form controls click on the button button draw your button and then choose the macro you want to run from this button record of invoice to change this text you can just click into the button i'm just going to write add to record if you click outside the button and then right click on it format control you can change the alignment to left aligned you can do things like change the margin so i'm going to change the left margin to 0.5 click on ok and then if you wanted to add a little button you go to insert illustrations icons if i type add insert change that to 0.85 width and height you can drag that onto the button and then you want to group it so right click on the button then hold down shift and click on your little icon shape and format group and group and now if i right click on the button i can drag it around as one object so let's see if this button works putting an invoice number of 1000 date today's date i keep these details the same let's say change the customer add to record if i go over to record of invoices you can see it's added that record now these other columns we shall be utilizing in the other videos in this series they are going to require other macros to fill in this information a link to the pdf a link to the excel file and also a date and time stamp for when the invoice was sent okay that's all i'm going to cover in this particular video hopefully that's useful if it is please give me a thumbs up subscribe and i'll see you next video [Music] you