Collaborate on Make Bill in Excel for R&D with Ease Using airSlate SignNow

Make bill in excel for R&D

hello this is randy with excel for freelancers  and welcome to the invoice with profit in this   week's training i'm going to show you how  to create this incredible service invoice   complete with calculated labor costs calculated  item cost an amazing profit and we're gonna do it   all from scratch it's gonna be an incredible  training i cannot wait so let's get started all right thanks so much for joining me i've  got a really exciting training this week now   we've created invoices in the past but never  like this because what's more important than   an invoice knowing how much money you actually  made on that invoice that's what we're going to   be going over today i want to know the costs i  want to know how much we sold what did it cost   us what did our actual labor cost us the money  we invested in our technicians so i'm going to   show you how to do all that this week how to  create your own one we're going to do it from   scratch we're going to be starting with a blank  sheet and we're going to be creating this invoice   together so i'm going to show you every step  of the way every line of code formula format   and function all with you today so i'm really  excited on this training i bring you these   each and every tuesday absolutely free even this  workbook is free all you need to do is click the   links down below either with your met email or  your facebook messenger we're gonna get that   sent over right to you if you do like these  trainings there are many ways to support them   and keep them free each and every week for all of  us the first is which with our patreon campaign   our patreon members get exclusive benefits nobody  else gets such as early workbooks pdf code books   which display all the code all the icons and  pictures you see in these trainings come that   in the resources package you also get exclusive  details on our upcoming sales and recurring   information on that we've got lots and lots of  benefits there on our patreon campaign for just a   few dollars a month that's a great way to support  us so we've got lots of things for you there also   if you like these workbooks i do have a pack  of 200 workbooks and that's on sale for just 77   that's some great ways to support us and keep  these trainings for free all right we're going   to get started this is a sample so this is exactly  what we're going to build we'll be able to save it   we'll be able to create a new invoice we'll be  able to load previously saved invoices just by   entering the invoice number here we're going to be  able to track items we're going to have item types   we will also have service types we'll be able to  select labor for specific employees and then we're   going to be able to determine the cost of those  employees we're going to be able to calculate that   all in this amazing graph we'll be able to know  the profit we'll be able to know the total cost   and of course the total sales all of that is  going to be available to us on for each and every   invoice so we're going to create this live we're  going to have dynamic tax in total so we're going   to be able to create the total we'll show you  how to create this really cool invoice background   we'll be using conditional formatting some simple  formulas advanced filters so i've got so much in   this training all right so this is the sample what  i'm going to do is i'm going to save that in case   we made any changes and i'm going to put that away  right now and we're going to start off with this   blank sheet here now this blank sheet of course is  going to be our invoice now what else is in this   of course we have an invoice list that's going  to track all the invoices that we previously   created right we've got invoice items these  are the individual items that are going to go   on the invoices themselves right we also have  a list of customers we're going to be able to   create and add new customers forgot that to show  you in a pop-up and also that's going to be a   bonus coming up since i forgot to mention it and  we've got the technicians different technicians   how do we calculate their actual hourly  cost we're going to be going over that   that's really cool and also we've got an item  list a list of items right and the cost of those   individual items and the price that we're selling  those individual items and then of course we got a   cool calendar pop-up which i'm going to be showing  you in fact let's bring back that sample let me   just show you we've got a really cool calendar  pop-up and i wanted to show you that and a cool   customer pop-up we're going to be able to add  customers dynamically just like this so i wanted   to show you that that's kind of important and  so we'll save that that's going to add it so   we've got that cool coming up i'll close the  sample just wanted to show you that briefly   all right let's get to it on this invoice what  are we going to start out we're going to start   out with our first two columns those are going to  be for admin so i'm going to color those gray i'm   going to pin this here and i want to call those  gray those are going to be our admin columns   right those are going to be eventually hidden  right i've got a set of probably some formulas   that we're going to need and so i'm going to give  these a specific color i want to use some specific   cells for some formulas and some functions that  are going to help us moving forward and so we're   going to put those right in here and then what  i want to do is i want to color this blue for   our invoice right i want to create a kind of  a blue color here but on the header and then   our invoice is probably going to e through j e  through j and we're going to go all the way down   to let's say about 34 on those so i want to put  a background on those just so we get some kind of   framework for around our invoice so what i'm going  to do is i'm going to put a border around there   just so we know where our invoice is i'm  going to choose a little bit of a thick border   and i'll go with maybe a dark blue on this  and then we'll just go all the way around   that way we kind of have an understanding of where  invoice is going to go the rest of that i'm just   going to give it this blue background here give  you a little bit of a blue color we'll just go   all the way to let's say that's a bit too far all  the way to z is sufficient for our purposes here   and then i'm going to format those and just give  it a little bit of a fade out color not too much   so we're going to go probably with one of  our primary colors here and then down to   our second color here which we'll use so just  this is sufficient then we're going to create   our background color at that remaining so our  background color we're going to highlight all this   and then what i'm going to do is just those those  first two rows i'm going to hold down the control   and i'm going to continue the rest of it down here  and then also down here so that just kind of gives   it a good background here that we're going to  cover okay so now i'm going to cover that this   blue color here which is going to be let's call it  this lighter color all right now i'm going to give   it that primary background color which is this one  right here and then we're good to go on that okay   so we've got our background color and we've got  our our basic invoice here so we're going to put   that in there now what i'd like to do is give it a  title and give it some information so i'm going to   add some shapes so the first thing we want to do  is we want to add our main shape so i'm going to   insert a shape here and then that shape is going  to be for our title so i'm going to enter a text   box here and that's going to be for we're going to  call this service invoice so i'm going to do caps   lock service invoice and since that's the title of  it we're going to give that a bit bigger i'm going   to format that right i'm going to put no outline  on that and we're going to put no fill on that   we also want to increase the font here to about  let's say 22 and then we're going to make it bold   and italic and we're going to give it maybe one  of the darker colors here so something like that   service invoice okay that should be sufficient  here and we'll drop this up here we won't be   using grid lines here so i'm going to do the view  and we don't need those gridlines i want to show   that and what i also like to have is some company  address here so our company address is going to go   in the upper right so i'm going to insert shapes  here and then inside that one another text box but   we'll do a larger text box around here and that is  where we're going to have our information so we're   going to put freder's this is our our company  name garage and then a new line so i'm going to do   enter and then we'll do 123 main street and  that'll give it the address and then any town   usa and then just give it a zip code so that's  sufficient there we have a zip code there   and also what i want to do is put in a phone  number here so let's add a phone number there   and then what we'll do is we'll format that looks  a little bit big so we'll make it smaller here   and then we'll do 310-555-1212 okay so  that's sufficient for the phone number   now let's add the font we want to make  it a smaller font i don't want it quite   that size so we'll do it something about 10 okay  so we'll do a 10 we don't need 11. and then i do   want to make it bold i want to make it colored  right i want to give it that same color and then   we'll expand it here and then also i want to do it  italicized here and also we have too much spacing   around that so what i want to do is i'm going  to right click that go to the format the shape   and then we'll go to the text box and i'm just  going to remove any padding around that we don't   need any margins or padding around that so that's  going to help us it's not necessary and then just   increase that that looks good okay and that looks  good so now we've got it for now what we want to   do is we outset some logos and some information  here i want to put the invoice number so inside   invoice number and then that invoice number is  going to go in j so below that let's go down put   an invoice date and then an invoice date so that  invoice date will go here let's just say 131 right   and then we'll format that a little bit and then  what i want to do is i want to right justify   these but i'll do one more i also want to put the  invoice status here we're going to be creating an   invoice status so i'm going to take all three  of these and i'm going to right justify those   and i'm going to take all three of these and i'm  going to left justify those i'm going to format   this as a short date so that'll be fine i like  that here so our invoice number will go here and   let's say our status will go here this will create  a drop down list of statuses that we can choose   here we don't need a border on that so we can  again no fill here and then what we'll do is   no outline here all right so we're also going  to put a logo here and we also want a customer   address right we want to put that customer that  customer address is probably going to go into g5   the customer name and then six and seven will  be the address and then eight we're going to put   in all of our headers so we're going to start  our headers off right here so this will be the   description and then we're going to have the tech  here's going to be the tech technician we want to   know who that could be a technician or it could  be an item name so we'll put backslash item could   be either one of those previously back i want to  put the date if there's a service date on that an   item type on that let's capitalize that just as  we are item type okay previously before that i   want to do the item id item id i want to know  what is the id of that and then also i want   to know the invoice item row invoice item row  what does that mean that's going to be the row   where it's stored remember if we have a set of  invoice items i want to know that row three four   five or six where is it stored on our database  so we're going to put that directly inside here   all right so beyond the description i also  want the quantity what is the quantity   of how many of that and then i also want to know  the amount the reason amount and the total for   the for that line item also i want to know the  item cost this will be not be printed right the   cost will not be printed so it's off and i also  want to know the total cost so this is so the   customer will see this when we print it out and  the admin we will see this so i think that's going   to be perfect so i'm just going to give those  some nice borders some colored borders i'm going   to format those cells and using that same blue  that darker blue so i'm going to use that border   and to give it that a little bit thicker line here  we'll use this actually this is sufficient here   this color and then we'll go all the way around  with the outline i think that'll be sufficient   and then also i want to bold the font here  all right so that's good and then just a   centered and give it a little bit of color here  so we're going to center that and then i'm going   to format those cells and then what we'll do is  we'll use a fade color so i'm going to use the   fill here and then fill effects i'm going to use  our two primary colors here that we've been using   which is the darker one into the lighter one those  are almost the same here but they're a little bit   clue and then so let's go with this lighter one  here i like that better okay so there we go so now   we've got this little fade out here it's a little  more obvious okay so we've got our primary so i   want to put inside f5 i'm going to put here the  customer customer and we're almost ready to create   our buttons so that customer name will go in a  drop-down list right here we're going to right   justify that customer name the address and that  okay so we've got it primarily and we'll put some   lines around here and i also want the totals  to go in here so here right i want to put we   have three lines so i'm going to put the subtotal  here sub total then i'm going to put the tax here   now this will be temporary because we're going  to change that name and the total here is going   to go so we've got the total the subtotal and the  tax right here we're going to right justify those   bold them and then the amounts are going to  go here so these are going to be all amounts   we'll put those in here this will be a sum so  i'm going to put borders around here so format   those cells sorry it's off the screen bringing the  borders up here and what i'd like to do is i want   to have the left and the top border maybe the thin  thin board let's go with a thin border on this   and the top and then the middle not that right  here and right here okay let's take a look at that   i like that and then what i want to do is here  i want to have a footer message so i'm going to   put a border inside here let's scroll up a little  bit so you can see that what i'm doing here   format those cells here and then what we're going  to do is we're going to put that thin border right   up the top now this we can use a merge and center  so our footer message is going to go right here   and we're going to emerge and center this and i'm  going to go to the left and the top so we can put   something like in thank you dude thank you for  your business i like that now we just need to   bold it and italicize it make it maybe a little  bit bigger okay that looks nice so that's a good   footer we're going to be this tax line will be  dynamic based on the tax that we're charging okay   and then the rates here so we'll be adding that  in more it's going to add some lines in here some   break point line so we can separate those columns  here on the invoice so i'm going to format those   cells just as we've been doing and i'm going to  use this same color but we're going to separate   it with this dotted line here on the left top  not this one not that one but this one here okay   so i like that there we go that looks nice now  we got a good framework let's save our work   we got an idea of what we want to do i'm going  to add just to make things a little bit easier to   read i'm going to add some conditional formatting  here so i'm going to hold it so actually we'll go   to here so all the lines that we're going to have  and i'm going to add some conditional formatting   here let's hold down the control once again the  shift go here the control for non-adjacent cells   and then down here on holding down the shift so  now we've got two sets of cells and what i want to   do is i want to format those cells based on what  is located inside here and based on odd or even so   we're going to add actually we don't need to form  we can format let's add a border in here just the   center border that should be sufficient just as  we did previously and what i also would like to   do is add some conditional formatting in here so  conditional formatting new rules it's going to be   based on two conditions so using a formula we're  going to use equals and again two conditions the   first condition is c or and whatever row so  we're going to remove the absolute from that   should not be blank does not equal empty another  condition is must be on an odd or even row right   so so i'm going to do mod that's our auto hotkey  that automatically typed it in now this is going   to be for even rows because it ends in a zero so  for this what do i want to do well i just want to   add a little bit of a lighter color so we can  show those alternating i'm going to give it a   fill and maybe a maybe a little bit more of  a lighter color so we've got some colors here   but we want to know is to use some recent colors  so our recent colors are located right here in   fill effects so we're going to choose one of these  lighter colors like let's say this one here and   that's a little bit lighter color so that's our  lighted color so we can find that in the reason   and click ok now nothing's going to change on that  because we haven't added any data but as soon as   we start adding some data we can see that we have  a nice alternating row and that's what i want just   a nice look so we can alternate okay very good so  that's looking good i like the way that that looks   and what i want to do is just give it a border  around here because we're going to have specific   items that are going to be centered so  i'm just going to put a border around this   here there and now inside here what we're going to  have is we're going to have these databases so it   doesn't necessarily matter what the look is here  but we do want to make make sure it's separate   so i'm going to give this kind of a lighter  gray this is going to be for admin purposes   only we'll be able to hide these two columns so  our invoices coming together here we get an idea   of what we want but i also want to know we want  to be able to search an invoice so we want a user   input field so we're going to color that white  and we're going to call it search invoice let's   spell that right so our search invoice is going  to be here we can move that to the middle here   and then right justified all it wouldn't matter  okay what else i want i want to know the service   sales right so how many what's the total service  i want to get some total so let's put something in   here invoice totals so i want those totals as you  may have seen in the sample invoice totals here   we're going to merge and center that here we're  going to bold it we're going to put some borders   around it here so any border okay so now here i  want those totals to appear here so first is going   to be our service sales or i want to know how  many sales we need service sales what is the total   and i want to know the item sales how many items  did we sell i also want to know the service   cost what is the cost our actual cost of that  service our employees cost and i want to know the   item cost and i also want to know the total profit  what is the total profit that we made on this   specific invoice okay so now let's just give it  all borders here and then i'm going to use the   double line here on the top because it's a total  so we're going to use this double line right in   here and we're going to put it on the top good i  like the way that that looks that's going to give   us some consistency we can just kind of color that  a little bit of our lighter accent color which is   here to stand it out a little bit so we know that  we're going to be putting in now we're getting   we know the service sales our item type whether  it's service or item is going to be here so we're   going to base those totals but let's get some data  in here first okay so the first thing we want to   do is we want to we want to create a customer drop  down list now we know we have a customer list here   and i've created already a few named ranges that  will help us move things along so if we take a   look inside the formulas and name manager we see  that we have something called client name client   name okay our customer name that would work let's  change that i just want to keep consistent i'm   going to change that to customer name i just  want something a little bit more consistent   customer name okay and that's basically our names  on our clients and our customers so clinton click   ok and then going to close it so what i  want to do now is create a drop down list so   data data validation here but i also want the  user to be able to enter additional information   if it doesn't exist so i'm going to unselect this  right that means they can put in any value they   want there's no rules and the reason is is because  they may want to add a new one so what i'm going   to do here is going to put in equals customer name  and that's the one so now when we drop down list   we have a drop down list of customer names  that's customer names again taking a quick look   are in here this using an offset formula based on  b2 starting on our header row and then removing   one row down we're going to count all the values  where customers a and column a so basically we're   counting all those values to determine how many  rows that encompass our data as we add new clients   or customers we will automatically have that  ability okay and click close okay so i just want   to make a few changes we're going with customer  here so i'm going to call this customer id right   just so we have some consistency and i also want  to call this customer name although it's customer   name just wanted to make sure it's consistent  customer name okay so we have that there we   go and again i want to make a change i want to i  don't we're not really using customer ids in this   one but i had it for okay it's good i like that  customer ids all right so what i want to do is i   want to add some information here the first thing  what i want to do is i want to know what row this   customer is on this list now you notice they start  in row three that means betty white is on row   three well when i select betty white here i want  to know what row betty white is on i want that   three to show up here so the first thing what i  want to do is i want to use customer row that way   if somebody enters something other than other than  what what is already in the list i want this to   show empty so how are we going to do that well the  best way to do that is to use a match formula and   use if air around that so let's go ahead and put  view we're going to need that formula bar in here   so i'm going to put equals if air and then what  i want to do is i'll match i want to match i'm   going to look up this name and i want to  run a match based on that customer name   okay and i want an exact match so we're going to  put in 0. now i want the row that it's located   on our first row is row 3. so i'm going to add  2 to that if it's found it would return 1. but   when we add 2 it's going to return 3. now if  there's an error meaning it might not be found   i'm going to put in blank there okay so that way  3 is found betty white is for 3. so i'm going to   bring those then i'm going to left justify this  if we decide to enter something other than that   right that's not found this is going to be blank  when it's blank and a user makes a change here   i'm going to let these you know hey this name  doesn't exist would you like to add it if they   get yes then they're going to click a little icon  here and they're going to be able to add it okay   so we know now the difference between a name that  exists and one that doesn't exist okay i also want   to know what the role of the invoice is invoice  row i want to know just like we do the customers   invoice row just like we do with the customers i  want to know when they search for an invoice here   i want to know what row it is on just like we did  so here in our invoice list right here i want to   know that invoice id number one is located on row  three now we've got some named ranges in our name   manager and it's called invoice id here invoice  id so that is also an offset formula based on the   invoice id so when i use the match formula here i  can determine what rows on just like we did here   i'm going to imagine based on whatever they're  searching for here right so that's one way but   what i want to do is in this case i want to know  what row whatever has been entered here which row   is the current invoice when they search for it  we are going to find a different one we're going   to put that differently so the invoice row is  going to be a match based on this what's inside   right here in j1 and we're going to increase the  font on that a little bit so i want a little bit   bigger okay so let's do that equals if air and  we're going to run a match that match is going   to be based on this invoice number we're going to  use invoice id for that we also want a 0 want an   exact match and again i want to add in what row  it's on and i want to know so the first row is on   3 therefore i'm adding 2. if it's not found i  just want to show blank here so that's all i   want to hear here just blank okay good so we know  that invoice 1 is on row 3 if there's an invoice   that isn't available it's going to show blank  okay perfect i want to do pretty much the same   thing for the next one i want to know if they're  searching so this is going to be for our invoice   whatever is located here but when a user searches  for it i also need to know the row because if it   exists i need to load it so we're going to do  something called search row here search row and   we're going to use the exact same formula here  that we did here except we're going to be using a   different original cell right that cell that we're  going to be using it's not j1 we're not searching   that lookup value in j1 we're now searching for  it based on l1 right that way when they make a   correct search for correct we know what row it's  on right once we know what row it's on we can   simply place the id right in here right in here  and right inside here we can just move this id   into j1 that's all we need to do to load it  and that will load up because we know the row   all right so what else do we do i need to know  the next invoice number as i create new invoice   numbers i need the unique invoice number for every  single new invoice so how do we get that so we're   going to put next invoice number and we're  going to use the max formula for that but if   but if there's no invoices that exist it could  create an error so we're going to use equals if   air again in this case we're going to use the max  and we're going to use max based on the invoice id   but i don't want the maximum i want the next  available so we're adding 1 onto that and if   there's an error i want the first available which  is going to be number one the first available   invoice id if there's an error that means since  we already have one uh invoice here we know the   next one is going to be two okay so we've got  that already and the last one i want to know the   difference between invoice load and not what does  that mean i wonder if this could be true or false   now the reason is when this invoice loads right  we're going to be making a lot of changes we're   going to be making changes to to the item id the  technician right so when we make those changes   i want some things to load up when i add an item  here i want that item information to load here   however when the invoice loads it's also going to  be making a change here and loading information so   i really want to differentiate between those two  types of changes one change when the user actually   adds the information manually the other changes  when v we search for an invoice number and it   all gets loaded in automatically there's two types  of change so i want to differentiate between those   two type changes and that differentiates is going  to be located directly inside b6 okay so that's   all we need for now now we're ready to add some  buttons and then we're going to add some icons so   saving our work we also want to add a really  cool background to this so why don't we do that   page layout i've created a background and  we'll call it background here and i've got   this really cool background here that i found and  i'm just going to insert that now it's been sized   ingly so that it's going to end up directly  on that because i have size lines i've sized the   background ingly so that's cool and the  reason it's showing up here and not here because   there's no background here right if i were to put  in a background here like this it wouldn't show up   right so you'd see it's kind of differential but  because there's no background here it shows up on   the invoice but it doesn't show up here and that's  kind of a nice effect great so we've covered that   so far now let's go ahead and add some buttons in  so i'm going to insert a shape we're going to use   this rounded rectangle here and i'm going to put  it right here we're going to add some buttons here   we're going to get the first one just the way  we like it and then what we're going to do   is we're going to then let's go ahead and uh add a  style like let's say this style but i don't quite   like that background so i'm going to give it  a shape background that's a little bit similar   to our theme which is this one here we're going  to call this save and then backslash update so   this button is going to be used both for saved  and update and then what i want to do is i want   to center this here so i'm going to do in the  center here and then i want to right justify it   okay that looks pretty good but i want to add a  little bit more onto that we'll give it a width   and i want to give them all the same width  so let's say about 1.3 should be sufficient   and then what that's going to do is allow enough  room for the icon okay like that might be a bit   large we can go slightly lower 1.29 okay that's  pretty good so what we want to do is i'm going   to duplicate that because i want so i'm going to  use ctrl d that's going to be for our new invoice   then what we want to do is we want another one for  our print invoice and another one for our save as   pdf so i'm gonna move that last one up and then  i'm just gonna hold ctrl and then i'm gonna click   on all those and i'm going to go in to our shapes  here and then i'll just go ahead and line and i   want to distribute them vertically so they're all  safe okay so now we can rename them so this one's   going to be new invoice this one we will have  print invoice and this one we are going to have   save as pdf save as pdf i like that but i  need one more button to add a customer so   i'm going to duplicate that i'm bringing that  down here now we don't need any text on this   so i'm going to delete the text on that one  and i just want a square button so i'm going   to move it down here and then we'll just change  this to let's say 0.26 so it has both the same   width and the same height and now i'm going  to do is add a plus onto that keep that simple   i want to make sure we don't print this so i'm  going to right click and then format that shape   we'll go into the properties and we don't want to  print we're going to unselect print right we don't   want to see this button on the printed invoice  we just want it on the invoice for the user   good so i like that that looks pretty good we've  got all of our buttons lined up i'm just going to   left justify that or center it and make sure  that they're all lined up now they're lined   up now we're ready to add some icons so what i'm  going to do is i'm going to insert some pictures   and i've got some already saved up so i'm going  to click on this these icons here and then we're   just going to select all those and put insert  okay so we've got them all but of course they're   all size ingly so i'm going to change the  height to 0.2 and i'm going to reduce them all   down so for this particular new invoice this plus  here i want to add that here but i also also have   a main one so let's put this plus this is going  to be used twice it's going to be used once for   the customer and i'm going to duplicate that we're  going to use a smaller version for the new invoice   this is our little invoice icon we can zoom in a  little bit and take a look at that so we have to   strain our eyes and so we see our invoice here so  i'll use that for both save and update that base   icon so i'm going to duplicate that using control  d and i'm going to place that right in here so new   however for the new invoice what i'm going to do  is i'm going to use this icon right here except   that's a little bit big so we'll go to 0.13 on  that and we can add that so we've got our new   invoice now our save we're going to use this a  different icon also smaller than it is so we're   going to put that right here and i'll drop that  down to 0.132 so that they're consistent and want   to bring that above so i'm going to bring that  right on top i want to bring it right here on top   so we've got save update we're going to use that  check box on the upper right corner we've got the   new invoice in which we're going to also put that  in the upper right corner we have let's see we   don't need this cancel so we can get rid of that  we should add a delete maybe i'll be adding that   on to uh for patreon because i didn't add that  save as a pdf we're going to put that right here   and print invoice i'm going to be using that right  here so we've got premium voice so now all we need   to do is make sure they're centered here and then  i'm going to group them do the same thing for each   one of these here and i'm just going to group them  not center them because we have multiple icons   in there and the same thing for here holding down  the control selecting all the shapes and grouping   them together same thing for the print same thing  for the pdf here this one we can make sure it's   aligned and group it okay great so we have it i'm  going to do the same thing for this one i'm going   to use my selection tool i want to make sure  that both of these are centered here so oops i   did that okay so they're both centered vertically  and horizontally and then group them now with all   of those using my selection tool i want to make  sure that i am not sizing them i'm only moving   them with the cells so i'm going to do all of  them including this one here zooming out here   and then what i'm going to do is i'm just going  to right click here and select those again   actually turn off the selection tool then we  right click format all the objects go into the   properties move but don't size with cells that  way if we change the our column widths it's not   going to change those shapes okay good so i like  that it's looking really good now notice keep in   mind when you zoom in the background changes but  at 100 it's just perfect and that's what we want   okay things are looking really good and really  up we've got all that we do need to add an   icon here for our invoice so i'm going to go  into pictures and we're going to open up and   i've got an icon right here for that so we're  going to insert that right here this fretter's   garage this is the name i've gotta like that cool  icon and then what we're gonna do is i'm just   gonna place that directly inside here so we've  got our icon here make it a little bit bigger   so we can see it moving this over give it we'll  move things over a little bit so we can give some   space and then the service invoice we can bring  this over a little bit here center that one i'll   center that one both directions here all right  good it's looking really good we've got a really   nice invoice okay looking good our invoice is now  really professional we've got a good looking logo   we've got address we've got information we've got  all everything we need i'm going to increase the   font of these here all of these i want a little  bit above maybe we'll go with 12 and that's what   stands out on a larger invoice okay good i like  that it's looking really good we're doing good now   all we need to do is start adding some information  so that we can add in our items and then once we   add items we'll add in the totals and make sure  this okay so what do we have here so what i really   want to do is i want to be able to select an item  based on an item id and a drop down list and that   item id is going to be located here so it is this  now i've already created a named range for that   so if we go into folders we have a name manager  and we see something called item id so it is this   item id using offset so we're going to use that so  inside the invoice all we need to do for all the   all of them right here all the way up until  here in fact let's just draw a line so we can   differentiate we know exactly how many rows that  we're going to be using now of course there are   ways to create additional rows if you're asking  me you can create a floating footer we did that   in another training floating footer so that this  footer floats and now you can create unlimited   invoice rows however for this training we're going  to keep it relatively simple and we're just going   to go with a consistent number of rows here so i  want to add a data validation for this now data   validation is here this one we need need to be  strict on that data validation we do want an error   there now i want a list value now how do we know  what if we don't remember that well we can use   f3 to understand that and we're going to click f3  and what we want is the item name here clicking   okay so it is that data validation that we're  going to select so when i select something i then   want that item information to show up all the way  over here okay so that vba is going to take care   of that however what i really want to do is i want  to be able to type in if we have a large list of   names i want to be able to start typing in eng and  have that automatically come up right how do we do   that how do we get auto complete in there well the  best way to do that is to have the same values in   the column perhaps below and out of sight right  so we can do that so what i'm going to do is i'm   going to go into the items and i'm just going to  say let's just go with a 1000 okay and then what   i'm going to do is that's going to be our last and  i'm going to just then go all the way up and i'm   going to hold down the shift and copy that so  i'm going to right click and i'm going to copy   all those values now when i go into the invoice  list invoice here i'm going to go down to our   first available cell here which is here and i'm  going to paste i'm going to paste those links here   and now the links are pasted and now based on  certain formattings and values sometimes it will   automatically come up but if it doesn't right so  let's take a look it doesn't if it's two reasons   right if we take a look at our values we see we've  got engine oil and we've got engine cleaning right   so let's take a look at something like haircut  nice um haircut that's nice for engine we should   change that doesn't make sense for garage um  let's take a look at tire change okay tire   change so it doesn't come up but what i want to do  is i want to create a chain that's going to link   the bot this list with this value so how can we do  that well it's got to be chained and they can be   non-adjacent cells and what does that mean  is cells filled with something other than   empty could even be a blank so we see that this  is this has a value this has a value let's put an   x here and let's take that x and we're going to  bring it all the way down here to here and then   what i'm going to do is i'm going to put another x  in here and now you see that there's a chain right   there's a chain that goes all the way up and here  it's now linked to that of filled cell so now if i   type it eng oh oil it so now we see that it comes  up great except i messed one thing up what we want   to do is we have ids here i had to add names right  is the item id our item names are going to go here   so let's just update that data validation here  right not the name but it is the id so equals   right we can use f3 again it is the id right we  have both of them and i'll show you how those go   in so it's going to be the item id let's add  that in there that's what i want this is the   item id that we've copied down there that's what  i want so let's do ahead go ahead and do that now   eng right oil there it is there that's what we  want we want those item ids here brake pads let's   start typing those in brake pad perfect so now you  see how it auto completes because we have a chain   now it doesn't have to be x's it could be anything  it could be a space so even a double two spaces or   a single space if i bring it down here right and  i also add a space here that well is going to work   so let's to look at another one brk brk rep that  works also so this is called auto complete now we   can do it as long as we have the chain that  chain of filled cells here even with spaces   all right great so we have that but what do i want  happen now when i make a change here i want that   information i want the item type to come in i want  the if there's any date we won't have that but   i want if there's an i if it's an item right i  want that item name to show up here however if   it is a service such as these service i want that  to come let's fix that one other thing i'll let   them look kind of weird huh didn't it inside here  our items list we have here i wanted this haircut   won't help oil change will cause oil change and  then let's call standard called standard standard   oil change okay good i like that here and then  we'll call this standard didn't update my data   third brake repair okay good so now we have some  data in here for our garage i don't think we'll   be doing a haircut inside our garage unless you  have a barber shop and a garage at the same time   which is possible might be an idea all right  so i want this information to autofill here   including the cost including the total cost but  our totals well certainly those are going to be   formulas so we can do that let's add that in  here okay so let's write that formula but we   want to make sure both quantity and amount are  already there so equals if it's going to be an   and right one two conditions right what is the  conditions quantity should not equal empty and   also amount should not equal empty so those are  the two conditions okay if both of those are true   then only then do i want to multiply  quantity times amount otherwise show blank   and that's our formula okay so that's all we  need to do so i'm just simply going to copy that   and then bring it down to the last one i'm going  to paste special and then paste that formula okay   we want a very very similar formula both inside  our total cost so we can just copy that and then   make the appropriate updates of course it is not  this amount that we're going to be concerned about   it is this amount the item cost that is what  we're going to go so if the item cost and both   the quantity continued value then do i want  to determine the total cost so this one that   way if our quantity is 1 our amount is 50 and our  item cost is 30 only then do we want to show that   okay so all right great so if that's going to work  and i'm going to copy that down here that formula   and i'm going to paste that all the way down  here paste those formulas down here now we've   got our formulas but we do want to format those  we're going to use us currency for this training   so we will just copy those and then make sure  that we've got the currency based on that   formatting those as a currency here okay good i  like that that's looking good i'm going to center   this quantity down here and then we've got it  centered all right so it's looking good we've got   our formatting based on just the way we want it we  also want to know add in some data validations but   only if it is a service type so let's get to that  and then we can move on but now what we want to do   is we want to make sure that at least we can get  our subtotal in here and then we're going to build   our taxes our subtotal is simply the total here  so we're going to use equals sum based on all   of the values above in the total that's  it so very very simple on the subtotal   taxes will be adding in just a moment okay i'm  going to format those here also to make sure   that those are currency continuing on now what  we want to do is we're just about ready for some   vba we want to be able to add in those items so  let's do that it's going to be based on a change   when a user makes a change to anywhere from c9  all the way to c31 we want something to happen   but we only want it to happen when the invoice  is not loading meaning when b6 is false so that's   when we want to happen so let's write some in  we're going to go into the developers and do   some code writing now so based on the developers  if you don't have the developers tab you can find   that right here into the options right and taking  a look inside our customized ribbon and we just   want to make sure that the developer is selected  here you can also use alt f11 as a shortcut to get   you there inside visual basic now our visual basic  should be directly inside the invoice so this is   our invoice sheet this is where we're going to  be writing our code and it is going to be based   on a change we're actually making a change to  the worksheet so it's called a worksheet change   and that's what we're going to be focused  on right now so we take a look inside   the worksheet here we select and then we're going  to be based on the change so it's a change event   not selection change we're going to come to that  in just a little bit later for our pop-up calendar   but so what we want to do is we want to first of  all if the user makes a change to more than one   cell it's probably better if we exit the sub so  we're going to write some code if the target dot   count large is greater than one then exit sub keep  in mind that we make a change for merge cells but   we try not to use those as much possible okay so  we have that there so now what we're going to do   is we're going to focus on a specific range if the  user makes a change on a specific change so what   do we we're going to call this on let's see item  name item what's called item id change okay so if   not i'm using auto hotkey to automatically type  that so we're going to take a look we're going to   drop this down just a little bit and we're going  to bring this up just a little bit here and we're   going to focus on ranges c9 all the way to c31 c9  through c31 that's what we're going to focus on   so we're going to change this from c9 through c31  and i told you there's one more consideration we   have to make sure we do need to make sure that b6  is false and range b6 dot value equals false then   we want to do something okay on item id change  and then we're going to put into parentheses   but not on invoice load so when we run the macro  to invoice load this will go to true and then   it's going to go back to false okay when we get to  that macro of course i'm going to walk you through   that step so when we do make that change when  we're adding an invoice item what we want to do   is i want to make sure that there's two conditions  the one kind of changes and when the user deletes   it if the user deletes it that's a change but  in that case i want to clear everything out   but there's another type of change if they add  something if they add something that's another   type of change so what do i want to do well then  i want to determine i want to look up that item   i want to find what row it's located here and i  want to add in the type i want to add in the name   the description the default quantity the cost and  the price i want to add all that information in   so how do we do that well the best way to do  that is simply to i should probably change cost   and price i'm going to reverse these just a bit  because what i'd like to do is the price the sales   price is first on our invoice and then comes  our cost right so so you see what i mean here   i want to make sure that our our price is first  right our amount our amount same thing and our   cost a second so we should we reverse it and make  things a little bit easier okay so saving our work   so now what we want to do is we want to add that  information but first we need to determine one   what type of change the user has a user  deleted it or has the user added a field so if   range c and the target dot row dot value does  not equal empty then do something okay else   m uh let's say user deleted item clear row okay so  here we're going to clear the row here we're going   to determine if what row that item is located  on so the first thing what we want to do is we   want to determine that item row so we're going to  mention the item row as long now what we want to   do is we want to do on air resume next right and  the reason is because when we look for that row   it could create an error so we're going to wrap it  in on air go to zero and i'll show you what i mean   by that so the item row is equal to items right  that's the sheet making sure we have intellisense   item is the sheet name range now what are we  looking up we're looking at the item id that   is the named range already and we're going to find  it right i'm looking for it what am i looking for   well i'm looking for the target value that's the  value that the user just made changes and we don't   need to look in and look after and look in where  we're going to look at excel values and then excel   okay and then what do i want to extract that i  want to extract the row right i want to look for   that row uh item let's call this item row if found  if found it should be found it's a drop down list   found okay so that way if it's found how do we  know if it's found or not item row will not be   zero that's how we know if it's found so what we  can do the first thing what i want to do is i want   to remove some validation here now take a look  at this right this is either going to be for our   technician name we have technician's name right  here it's going to be for that or it's going to   be for item name so there's a if there's if it's a  service right if it's a service type it's going to   be a technician i want to put in that technician  name if it's not then i want to do it but if it's   a technician it's going to be a drop down list of  all the technicians otherwise it's simply going   to be the item name so what i want to do is if  there's any validation here i want to delete it   right so in column f the first thing i want to do  is delete it and that means because any time we   add a new data validation right data validation if  i add a data validation here and i use a list here   and if it's a source let's see we choose employee  name or technician name i think it's what it is   so what we want to do is add that in here so  here is this but we're going to do tech name   here click ok and so sometimes if it's a service  i want to be able to choose the technician who did   that but to do that we want to make sure to delete  it so anytime we decide we might add a validation   we always always must first delete any validation  that could be there so to do that we need to   delete the validation inside the cell so that's  the first thing what we're going to do so first   thing we'll do is we'll do range f and the target  dot row dot validation dot delete okay put delete   any validation if exists okay all right good  so we've deleted that just in case it exists   now what we want to do is i want to determine  if the item row does not equal zero so   if the item row does not equal zero that means  that no we know that that item was found then   do something right the item row does not equal  zero then do something so what is it that we   want to do well the first thing what i want to  do is i want to put in that item type where is   that item type right i want to put that item type  directly in d and where is it going to be found   it's going to be found directly in side c so all  we need to do is add that in there so how do we   do that we'll write just a little bit of code to  do that inside here so let's do this range we're   still in the invoice sheet and we're going to be  putting it directly in side d so let's do that d   and the item row we know it's not let's put it  in the target row target dot row dot value equals   okay and it's going to come directly from c in the  item row so we're putting that directly inside d   putting it directly there inside column d that  item type so all we have to do is say equals items   dot range c and the item row dot value this is  going to be the item type so now that we have   the item type sorry it's coming from b there  right b is our item type our item id's in a   our item type is in b and c of course is the next  one okay so now that we have the item type i'm   just simply going to copy this so that we can make  things a little bit quicker so we've got indeed   now what is going to go inside here of course  nothing is going to go into the date yet user   will put that in what about f right f is a bit  different right if we have an item if that item   is an item type remember we have two different  types of items we have service in which we want   to put in a technician and we have items so if  it's item i want to put in that item name here   okay so here's what we're going to do so inside  the invoice we're going to use an if statement if   it's an item then put that item name directly  in f all right so let's add that inside here   so it's going to use an if all right if here if  range d and the target row dot value equals item   then what do we want to do then we're going  to add in that item type so we're going to say   e of course we're putting that directly inside f  here f so we're adding that in right there so f   is going to be equal to whatever is located  in c and that is the item name c in the item   name so the item name is going to go directly  inside f and it's coming from c all right so then   we can do end if what if it's not what if it's  a service well if it's a service we want to add   in that validation so else service right this of  course this is an item this is called an item type   and this is the service type so service type  we want to do something a little bit different   first thing what i want to do is i want to clear  out anything that might be an f so range f and   the target dot row dot clear content it's just  clearing everything out now i want to add in   that validation so range f and the target dot row  dot validation right i want to add in a validation   here so how do we do that so validation dot add  and i want to add in the validation i want to   excel it's going to be a list type validation so  we're going to put in list and i also want to add   a stop on that so xl valid so it's invalid we have  alert alert stop right we want to stop if it's not   existing and then excel between right one between  those values and then we want to give it a name   formula what is that form it's going to be  equal to the name of the text and so it's   going to be equals tech and then underscore name  which is the same as our named range technician   and that's it so that's all we have to do so  let's take a look at that and see how that works   saving our work so now what we're going to do is  we're going to add in here our engine oil okay so   we add our this is correct right engine oil so now  let's add a service item with this oil change as a   service item check to make sure that our drop  down list let's do that one more time we have   let's do it one more time there's no  validation here which is just what i want   changing this to tire change which is a service  right now we have a drop down list of employees   that's exactly what i want okay very very cool  so we also want to continue if it's an item out   regardless i want to add in the description  here the description gets added in regardless   so let's go ahead and add in in column g so let's  continue on adding in throughout our invoice so   in column g what do we want to add in well then  let&