Collaborate on Estimate Bill Format PDF for Technical Support with Ease Using airSlate SignNow

Estimate bill format pdf for Technical Support

hello this is randy with excel for freelancers  and welcome to the one click estimate work order   and invoice that's right with just one click  we're going to be able to create an estimate   and then transfer that to a work order  and copy that automatically to an invoice   we're also going to have a drop down  dynamic add new list print email and   a whole lot more we're doing it all from  scratch i cannot wait let's get started all right thanks so much for joining me today  i've got a really terrific training today in   business we often have to create estimates for  our customers and then when we complete that and   get that estimate approved by a customer we need  to turn it into a work order for our staff then   once it gets complete we need to turn that into  an invoice today i'm going to show you that and   a whole lot more we're going to use the same  screen for that and we're going to just be   able to take certain items and bring them along  through the order process until it gets completed   very simply with a single click we're going  to use a lot of conditional formatting   we've got great formulas in here some good vba  code great work with shapes so it's going to cover   a lot we're going to do all of it from scratch at  least on this screen we've got some database set   up and some information we're going to do all the  coding for you i hope you do like these trainings   i bring these to you each and every tuesday this  workbook is absolutely free all you need to do is   click the link down below using your either email  or messenger and we're going to get that sent   over to you if you like these trainings i'm  going way above and beyond these trainings   in our brand new patreon account that's right  with this workbook you can also get a complete   beautiful pdf code book and that's completely  organized so that you have this code each and   every week in a pdf format you can read it study  it perfectly beautifully organized along with that   i'm going to be offering additional workbooks  and additional training beyond that based on   your suggestion so if you want a certain feature  or a function or you want me to fix something or   you want me to focus on an issue over training  i'm going to do that inside our patreon account   i'll create an additional training video a shorter  one plus an updated workbook each and every week   plus we've got a whole lot of benefits of that  i can't wait to share that with you that's with   our patreon it starts at just three dollars a  month i'll include the links down below brand   new patreon that'll help support us all right  let's get started this is a sample so obviously   we're not going to be using this but it gives you  an idea of what we're going to be trying to create   we've got some add new buttons save update delete  email print and order estimates and a whole lot   more all right without further ado i'm going  to close this sample one so we're just going   to save it and we're going to close it out i hope  we do get subscribed on this channel don't forget   to click the notification icon bell that'll make  sure you get these strings this is the workbook   we're going to be working off of so you can see  it's a blank screen so we're going to get started   on this but before i do i want to kind of give  you an overview of the workbook in itself in our   admin screen we've got some things that are set  up already it's going to help us move things along   and the cool thing about this one is it is dynamic  meaning you may not want to call them estimates   you may want to call them bids you may want to  call them proposals whatever you want to call it   you can put it in here in this order type same  thing with work orders they might be called work   request work order or whatever else you want to  call it you can put that name here and then also   invoices some people call them receipts or bills  or orders so whatever you want to call you just   put it down here so we've got that we've got some  dynamic vocabulary that's going to help us in our   application i've also got a list of job sites we  can assign a job site to a work order an estimate   or an invoice that's going to help us dynamic  taxing so that we've got a name set up here   and it's called tax notice the tax name up here  that is the name so if you want to call it gst   or whatever we want this dynamically to appear on  our invoice and estimates so we can do that also   we want to have a tax rate notice this tax rate  is also a named range up here so currently eight   and a half percent you can set it to whatever you  want i've got a list of service technicians here   that's going to be a drop down list on a work  order possibly on our invoices we want to assign a   certain invoice work order to a service technician  so we've got a dynamic list here and also i've got   some footer messages we can go ahead and center  this footer messages is going to help us with this   application so we can set different footer  messages whether it's for work orders whether   it's for specific invoices or even estimates we  can have dynamic footer messages i also want to   know which one is going to be set as default when  i create a brand new invoice which which one am i   going to use i do have some code set up here am  i going to use this for our default estimated   footer message or am i going to use this for our  default invoice this would be for our invoice   right thank you for your business so we've got  footer messages that are dynamic based on the form   that we're going to be using okay that's going to  be really helpful i've got an orders database this   is the database that's going to store our orders  we've got an order id an order date the type of   order is it a estimate a work order an invoice  we've got the customer assigned the service   tech assign if we've assigned the service tech  the job site a footer message if we've added a   footer message we've got the total amount and also  we've got the estimated id the work order i'll go   over these things a little bit later on so that  they're more clear and then also what we've got is   the order ids it is the individual items that go  on in order right so for example these three items   went on order number one this was an estimate  these three items were brought over to a work   order this is the type so these three items are  the same that are brought over to the work order   these three items were brought over to the invoice  so each one comes with a description it comes with   an item number a quantity an amount a total an  order row this order row signifies what order   we're going to be using here on the order we're  going to build that up shortly and then also we   want to know the let's go back here the row that  it's on and then also the items database i've got   a database of items that includes an item name the  name of the item the sales or estimate description   this is the description that the customer will  see we've got a work order or service description   this is the description that will go on your work  order so you print out your work order and it goes   to your employees it's a little bit different so  for example if we've got a 50-inch bathroom vanity   cabinet we might have a work description that  says and assemble and the bathroom vanity   cabinet so this is the work order description we  have a default quantity what is the quantity for   example certain things we like if we're putting  installing a bathroom we might have two stainless   steel supply lines we rarely just one  alone so we might be putting in two so we want   to set up a default quantity of two okay we've got  a sales price we've also got the labor material   cost i want to know that cost so when we do the  work we understand the total cost of the job   very important and i also have a customer database  just a basic database customer id name address   to phone email and status okay so let's get  started building this one out i do have some   information here that's going to help us we'll be  going over that shortly but let's get this update   the first thing what i want to do is just give  this a background color and this color is going to   change eventually but what we'll do is we're going  to start it off all the way let's go all the way   to y just a big one over here and we'll bring it  over here and then what i'm going to do is give it   the the the invoice we're going to format cells  so this conditional formatting is going to be   our base formatting and that's going to be for  our invoice so i'm going to give it a specific   color all right so we'll go into fill effects  and then we'll bring it over here and we'll just   bring this blue so i'm going to go with this blue  here and then a medium blue so it's going to just   fade down a little bit and then click ok then  on the row below we're going to do it just   a little bit below that right a little bit lighter  than that so i'm gonna again format those cells   and i'm going to fill this and then this fill  effects also fade effect then what we'll do is   we will go the lighter blue and then we'll  just set the background color and click   okay and then okay okay next up i'm just going  to color all the backgrounds go down to a large   row and just cover this color this that blue  and then what we want to do is we don't want   to color that invoice but we'll work on that  okay so now we've got our basic color here   and what i want to do now is go into our invoice  our invoice is going to start probably right here   on e3 so e3 is where we're going to start and  we'll bring it on over all the way to let's say i   right i so i want to keep because i want to have  one for the item name here let's see item name   here i want to have the description here i  want to have the quantity here the amount   the total here so that's what i'm going to want  for that so we'll bring it over here starting on   e3 all the way to i and then down we'll go  down two so let's say row 38 38 bring it   all the way down that'll give us enough for both  the totals and there okay so i've got that this   one we're going to color white so i'm going to  use this as a white color the white background   okay so i like that that's going to that will be  sufficient for us what i want to do is i want to   put the title here what is the title going to be  so whether it's estimate invoice so i'm just going   to put invoice okay it's already formatted here  a little bit for us to help us move things along   okay so that's going to be our invoice and i also  want to know here what is this but i don't just   want to know the invoice i'm going to change that  text in just a moment but basically that's the   idea but it's going to be dynamic right it's going  to be based on whatever the order type is now   that order type is going to go right here so for  example i'm going to this is going to be dynamic   whether it's work order estimator invoice is going  to be changed here so what i'm going to do is i'm   going to put something like let's just say invoice  here and then what i want to do is i want to base   this title on whatever is here but i want it an  uppercase so how do i do that so i'm going to do   so this i've already named a range i've called a  name range called order type notice i've set this   cell just to help us to do that all you would need  to do is just type in order type here and then   it automatically sets the name range so that's  relatively easy now what i want to do is i want   to set this dynamic and i want to do it equals to  2 but i don't want to set it in lowercase i want   to set it in uppercase so to do that i'm going to  add a formula upper all i'm going to do is just   going to send the uppercase of that so now it's an  uppercase that's exactly what i want okay perfect   next up what i want is i want the number here so  again i want something like invoice number here   but again it's going to be based on whether it's  a work order an estimate or whatever so we need to   again make this dynamic based on whatever it is so  to do that again we'll just use the same thing so   in this case it's going to be equal to order type  and right and then i just want to put in let's say   the space and then let's say the number and  then a colon in there there we go so now as we   change this to work order it's going  to automatically change here both of   them and that's exactly what i want i want it  dynamically placed okay so then again i want   to do the same thing i want the date here but i  want to know is it an invoice date an estimated   date so again we're going to do something very  similar to this so i'm just going to copy this   i'm going to paste it down here and this time what  we're going to call this order type and let's call   it date so instead of this i'm going to use date  so now what i want to know is the invoice date   and next up i want the service tech this won't  be dynamic service tech who's the service tech   assigned i'm going to put that in five there and  then in six what i also want to know is the job   site are we putting in a job site job site what is  the job site that's been assigned so now we'll put   on colon so that we make it consistent for each  one of them and then also we'll put the date in   here so let's just say we're going to put in let's  say this is invoice number one the date of let's   say five eight and let's say the service deck what  i want is the drop down list of service text and   i want to drop down list of job sites now i have  those set up to make things a little bit easier   so back into the admin let's go into the formulas  name manager and we take a look we've got some   names set up so let's take a look and run through  these all of them so we can see the customer email   based on an offset meaning it is dynamic as it  grows so if we tab over here we see that we have   a dynamic list called customer email based on this  email list as our list grows so does this we're   using the offset if we zoom in here to this offset  formula right here we see that's customer database   f2 one row down because we're starting on the  header row count a two columns count a we're   counting i want to know all the ones in b2 we  can use the customer name because that would be   required so we're counting all one's b2 that's it  that's all we need to do for the dynamics so we've   got a customer and i've got a customer name here  tabbing over customer name same thing i've also   got a extracts those have to do with our advanced  filters so we'll get into that little message i've   got a footer message notice the footer message  are based on the footer message names right here   also in offset pharma we've got an item cost  this is based on the item cost the cost of the   atom monitoring cost we've got an item name based  on our item name here so we've got a named range   called item name i've also got one called job  sites okay these are based on the job sites again   dynamic named range i've got an order id based on  the order remember these are the order ids orders   can be estimates work orders or invoices based on  the id i also have an order type notice the order   type we did go over that's exactly the order type  that we're using in these formulas here so we have   that print area we're going to be defining service  text of course we don't have i guess that's not   we don't have that actually i can get rid of that  but service text because we already have our let's   take a look at that oh we've got here i'll update  that f8 so they're going to update that the reason   that is a merged cell so let's update that to  make sure that we have that count a should be not   f we're counting a single cell so tab out of that  now we have because it's a merged cell notice the   service text f a through f g right but we only  want to count a single column so that's it so   check that and now it's correct so service  texts are dynamic okay and then we have the   tax name which we went over and then we have  the tax rate which we went over okay so that's   it for the named ranges so i want to add  both job sites and service technicians to our   data validation so back in the orders here's our  service text so i'm going to go into data data   validation here and i'm going to choose a list and  what is that list well i'm going to choose f3 so   we can easily select from our service text signing  down here service text that is going to be and the   same thing for job sites again i'm going to go  into data data validation lists here and we're   going to choose a list and then i'm going to say  what is that source again f3 we're looking for job   sites down here and job sites is the one clicking  ok all right so now we can select the service   tag here and we can select the job site here  perfect okay so we can save our work so far here   and also i'm going to drop this down because we're  going to be working on the formatting a little bit   all right so we've got that so what do we want up  here i want to put a customer i want to make sure   that the customer in name is automated there and  that's going to go into e5 so i'm going to put   in the customer that doesn't need to be dynamic  because that's the same thing so the customer   here and i want a drop down list of customer names  again data validation here list and then again f3   to make sure we have the right one and then the  customer name clicking okay click all right okay   so now we've got the customer name perfect i want  when i select a customer name i want the address   1 and address 2 to appear here so we're going to  get to that in just a moment what i also want to   do is i also want to have a header row here i'm  going to put that header row directly in row 9   so right here what i want this to be called  items could make this dynamic to the items   description and then next up i want the quantity  that's just a small column next i want the price   and then i want the total i also want to give it  this blue background and i want that condition   i'm going to be based on conditional formatting  as well so i'm just going to give it that blue   background and i'll show you what i mean by that  because only invoices are going to be blue so i'm   going to give it that invoice blue and then i'm  going to give it just some borders all the way   around here so black border will be sufficient i  also want to border around our entire invoice so   i'm going to put a border around here all the way  around just around just all the way around here   and we'll go with the outside borders here okay  so next up i want the items here and i want some   basic just in here now we want the totals here  i want to about three rows for the total one two   three so in this row here i would like to put in  the subtotal that's giving 37 sub total and then i   want the tax but that's going to be dynamic based  on the tax name and then i want the total okay so   i want that here then i want those so we're going  to do is you're going to right justify these here   and then i'm going to left just well we'll keep  those on the right because they're going to be   amount so i'm going to make sure that those are  accounting they already are and then i'm going   to put borders around these all okay so we're  just going to go the borders around this one   here i want the footer message i want that footer  message to go right in here actually let's bring   that up one more i'm going to bring it to 34  let's bring it up a little bit more because   that's the way i had it before that's going to  be more consistent okay i like that better let's   color this in blue and change the borders around  i'm going to format those cells sorry it's off   the screen and then what we'll do is we'll put the  border on the top here and then we can get rid of   these borders here okay so that looks a little bit  better i like that better here's already merged in   center that's going to be our footer message okay  for the message and go thank you for your business   okay so i like that and we'll put a border top  here format those cells sorry it's off the screen   and then i'm gonna all right good so i like that  again saving our work so we have now let's put in   some borders just a small very light border on  here vertical here and then i'm going to format   those cells and then we'll just use the dotted  line here and on all the sides we can probably   go with a lighter gray i would say not a dark just  so it's light let's go with the lighter a lighter   one here that's better here a little bit lighter  okay and click okay all right we're going to use   this border for black okay so i like that now our  invoice is getting built out it looks really good   we've got everything here but i want to put that  dynamic tax name well what's that tax name going   to be it's going to be equal to tax name and  what else i want to put in the parentheses here   and the parentheses and then i want to put in  the tax rate and what is the tax rate right but   the tax rate itself is not going to be formatted  right and i'll show you that and parentheses so we   got that but i don't want 0.85 right i want that  really that's showing the rate so what i'm going   to format that's we're going to use the text for  that and how we're going to format that well all   i need to do is put a comma here i'm going to put  in 0.00 and then the percent sign and then close   quotes so there we go i like that and then we'll  fix that up all right tax name bracket tax rate we   want to tax so we're gonna put the text so text i  want that text there then what is that text we're   gonna put in a percentage on that so how do we  do that well we do comma and then the quotes here   then what i want is 0.002 decimals then the  percentage then closing it and then close   okay then the close parenthesis all right that's  it so that's it tax 8.5 you can actually add a   probably one more space between that so we can do  that just a little bit of an additional space here   between the tax name okay that looks pretty  good tax 8.5 so we want the formulas what is   that subtotal going to be it's going to be the  total of all of these so it's simply the sum   we can use the autosum on this no problem with  that and then i just want to add in some all   the way here so everything okay great so now we  have the total so if i put in 50 here or whatever   then we're going to show 50 here if that tax rate  now i want the tax rate it's going to be equal to   that subtotal times the tax rate perfect and  then we have our total it's going to be equal   to the sum of our subtotal and the tax rate okay  so that's going to be regardless of whether it's   invoice estimate work orders we haven't have  okay so i like that that's looking pretty good   and now all we want to do is our item  names so we've got dynamic excuse me we   have data validation or items so i'm going to  highlight all the cells and we want some lists   so what do we want to put i want to put in those  item names list data validation here i'm going   to put a list and what are we looking for we're  looking for item names if we're not sure the name   f3 go to item name click ok and click ok perfect  so now when i have the item name that's what i   want okay so when i select an item name what i  want is i want to have that description appear   here okay we're going to get to that in just a  moment that'll be vba same thing here with the   address but what i also want to do is i want to  add some shapes i want to add some things i also   want to add in additional features here so what  i want to do i want to have a search order here   search order number here so we can search for the  order and here i want to have a footer message   footer message so that we can select which footer  message we we want and it'll appear automatically   okay so what is that footer message going to be  based on a data validation again we have that here   listing here going to be based on that footer  message so f3 we can find that footer message   right here and click ok click ok ok so these are  going to be in white so we're going to change that   background to white here and then we're going to  just surround this by some borders so that we can   get that cleaned up formatting those cells border  all the way around okay and then i'll put a dotted   line in the middle and then what we'll do is we'll  do a solid between them so i like that that looks   good we can search for an order we can search for  for a message and we can bring this down we don't   need to let's use the longest one which is this  one okay that looks pretty good bring it out a   little bit more now i'm going to put some button  sets here i want button sets six different button   sets here and then i want also some guides here  i want to know between the estimate the invoice   and the worker so we need to add some shapes  for that so let's save our work we're going to   insert some shapes on that and what shapes do we  want well what i'm going to use i'm going to use   this particular shape right here this one's one  i want to use this chevron here that's going to   help us out so to do that i'm just going to bring  it over here like this and that's going to be our   first one so i'm going to have that let's say call  this estimate but i don't want to call it just   estimate there because remember that's dynamic  right so how are we going to get that dynamic well   again it's going to be equal to whatever is in  what we have set up in our admin screen so equals   going to our admin and then we're going to scroll  over here and select estimate so that way the text   is automatically so that way if i can change  this to bids or proposal if i do this proposal   it's going to change automatic proposal okay  it's about to write this time so now we go   back to orders we see its proposal okay so  but we'll stick with estimates otherwise   i'm going to confuse myself here and then what  we can do is we'll just do estimate not plural   okay so that's a little more clear so it's  estimate so it's dynamics going to change   and i want this basically to give it that yellow  color so i'm going to use choose this yellow here   so next up i want work order so what i'm going to  do actually why don't we let's uh bold this out i   want to do a little bit i said that's good right  there i'm just going to increase the font a little   bit on that one we'll increase this to let's say  14 so it's bigger and then i'm going to center it   across make sure it's centered give it a specific  shape let's try something like about .25 to   1.15 that should be 0.25 that's enough for the  height and then 1.15 that should cover it 1.15   okay so we're good to that i like the way that  looks there i'm also going to create ones for   work order and invoice i'm going to duplicate that  i'm going to bring it over here to the next one   and then i'm going to call this work order and i'm  going to duplicate that again and we're going to   call this invoice but to do that right this one's  c6 right the one below in our admin screen that's   going to be our work order okay all right so to do  that we want to need to create this one a little   bit longer here so we'll do this one point let's  say 2 5 making sure that's a little bit longer   also remember when it go when we keep when we  add that formula again it's going to revert   back to its original size so we do need to change  it each time we change the formula right so okay   so we understand that let's make this one a  little bit longer and then what we can do is   also want to let's right click these and we'll  set the properties in the text so we don't need   that much spacing on the left and right and  so the text box notice it's got point zero   one we can go to half that give a little more  space okay that'll be easier so now we've got   and then the last one is we have our invoice  so that's going to be c7 again back to 14 here   and i think we're good to go on that okay i like  the way that looks let's move it over okay and   also what we want to do is we may want to put it  behind or in front but i think that looks okay i   want to color these specifically so the work order  i want to give this one here the invoice i want to   make this blue so i want to keep some consistent  colors probably this blue for the invoice okay   so i like the way that that looks all right we can  play with the colors a little bit you know i think   i in my sample i had i was probably using this  this one here for for the estimate a little bit   darker but let's go with the lighter one for now  and then what i want to do is basically when we   select these it moves along across and we're going  to do that with vba we're going to get into that   let's just make sure they're lined up right now  holding down the control button making sure that   they're lined up perfectly okay very good saving  now let's continue with our button sets over here   then we're going to add some icons so first  so again i'm going to insert some shapes here   we're going to insert just this rounded rectangle  here and then we'll just give it a gray i want to   give it a new relatively neutral color on here  okay that's going to be all right and then what   we'll do is we'll just do a little bit bigger  and then i want to give it this gray color so   again i want to give it relatively neutral just  go with this in this case this is going to be   add new so we're going to use this for add new  and i'm going to center that because i want to   put icons on both sides most of them are going  to be centered so this one will center that   looks pretty good so what i'm going to do now  is i'm just going to duplicate this and we're   going to create another one called cancel new  this is where we need a little bit bigger of a   column here so we can accommodate the buttons here  that should be sufficient here cancel new okay so   cancel new in this case we wanted let's add that  cancel new and this one in this particular one   we can format that a little bit to the right  here bringing that and then also what i want   to do is i want to save update so i'm going to  duplicate that bring it over here this one's   going to be called it's going to be using the same  button whether we're saving or updating orders   it's going to be save update and then again the  next thing what i wanna do is i wanna be able to   print the order so i'm gonna duplicate that ctrl  d to do that and then we're gonna call this print   order so print order next up after print order  what i wanna do is i want to add a delete order   so we've got to be able to delete it so delete  order and then i'm going to bring that down here   i've got some icons next up we're going to use and  then after that what i want to do lastly is i want   to be able to email that order our customer has  an email address so one want to be able to email   it turn it into a pdf and email it okay good i  like the way that looks let's line everything up   looks relatively good here and then we're ready  to add our icons okay good here and then good   here now let's make sure they're lined up not  only horizontally but vertically as well okay   good let's uh line that up here and then the same  thing here now we're ready to add our icons i've   got them saved up already so what we're going to  do is we're going to insert here and then pictures   here then we're going to locate the pictures it's  going to use all of these pictures so i'm going to   select all and click insert i'm going to set the  size down to about 0.2 on that except one of them   will make bigger because that's going to be our  icon bring them down here we can see them this one   here is going to be our icon so i'm going  to bring make this up it's our logo for the   sheet now what i want to do is add new we're  going to put that right here our cancel new   is going to go right here our print email it's  going to go here our print is going to go here   and we have our save and update and then our  delete okay so we're good on those i like   those the way it looks and then i think they're a  little bit small don't you i'm gonna hold down the   control here so we can select all and i'll just go  point two two make them a little bit bigger i like   that point two two is good and now all we need to  do is just center and group them so i'm gonna hold   down the control i'm gonna center those and then  i'm just going to group them and do the same thing   for each one of those centering them to make sure  they're vertically centered and then grouping them   and then we don't we're not going to be hiding or  showing these they're always going to be displayed   so we don't necessarily need to name those  it's an extra step i suggest you do it in your   applications but for this one we've got already a  lot to cover so we're not going to be doing it now   and then what we'll do is i'm just going to group  the last one okay so now we're ready to go now   we're ready to add some functionality this week  okay so we've created everything i really like the   way that that looks and you can of course you  can add a company logo here so we've got some   vba to get to here a lot of it but let's go over  a little bit here the order type is going to go   number one or number two so what i want to  have is i want to have an order type here   basically it's going to be based on this based on  this list here if it's an estimate i want one if   it's a work order i want two and if it's invoice  i want three and i want that type number to go   here that's going to really really help us once  we're inside vba so how do we get that well we   can just use the simple match formula that'll  get us there that's all we need is a match so   let's do that so it's going to be equals match and  what are we matching i'm looking up this here and   what is the range that i'm array that i'm looking  up here it's going to be based on this right here   and i want an exact match so i'm going to put zero  and that's going to do it's just going to assign   us a one so estimate it's going to change to one  and that's just what i want so i also want to know   when we're going to be loading an order i need to  differentiate between when we're actually making   changes the user makes changes here like this or  when we're actually loading the order from when   we enter an order and it loads all up i need to  differentiate between those two types of changes   so i want to true or false here so we'll just  put false for now that's going to change on   i also want to know the order id so the order  id whatever order is being displayed so if i've   got let's say i've got order number one i want to  make sure that we also need to order row here so   let's say i have order order number one what  is the order row well we can use again match   for that too so we can use equals if air just  in case it doesn't exist we're gonna use match   what am i looking up i'm looking up this order  id and i'm basing it on the order ids here   and we want an exact match but also i want the row  number i don't want it to return one i want it to   return in this case four so we need to add three  to that because our first one starts on row four   if there's an error i just want to show empty  so our return is four and i also want the next   order id notice i've got i want to know that it's  going to be 4 as our next one we can use max for   that we've used that before in the past so equals  if air max what is a max based on the order ids   order id but i don't want the order id i want to  know the one after the max of that if there's an   error why would there be an error there'd be there  if we have no data so if there's an error we have   no data i want to start it off at 1. i want to  set that default to 1. so our next order is id all   right i also want to know the row here i want to  calculate the row based on the id here i want to   calculate the work order row based on that so it's  all going to be based on this so what i'm going to   do is i'm just going to copy this formula down i'm  going to paste it here here and here i'm going to   paste those formulas okay and what i'm going to do  so basically when i put an estimate i estimated 4   here i want the road to show up right here so how  do we do that well it's based on that there is no   4 but what if i put in 3 3 is contained perfect  and if i want to put in 3 here i want to know   the row of that work order same thing here so  it's really the same formula it's based on that   okay so that's what i want there because i each  one of these orders like for example if i have a   an estimate i want to know if there's a work order  associated with that estimate if there's an uh   invoice i might want to know that there's a work  order associated with that invoice so this keeps   track of all of the associated documents for an  order an estimate a work order an invoice they all   go together so we need to keep track of all those  ids together okay we're going to clear out these   rows right now just so we don't have anything i  also want to know what message the footer which   is i want to know what row this is on so how do  i know that so that i can extract that and put   it right inside here so how are we going to do  that well if you notice on our footer message   our first one starts with row five so if we return  the first one i need row five so how we're going   to do that we can do that right here so i want to  know the row so it's going to be equals and if air   matching what are we matching i'm matching this  and three based on our footer messages which is   the no and then i want an exact match okay but  i again i don't want just to return one i want   to return the row and row starts at five so we  need to put in four if there's an error just   put in nothing that way we know that our first  footer message is going to appear on row five   also want to know what is the default estimated  footer message what is the default work order   message and what is the default invoice what do  i mean by that the default would be located right   here where is this check mark what is that is it  here right is the default estimated i want to know   when i create a brand new estimate what default  footer message should i put in here where is this   check mark located based on k so all we need to  do is use a match and look in column k so to do   that we can just use a match so default estimated  footer message equals and then if air we're using   a match again one of my favorite formulas what am  i looking up i'm looking up that check mark what   is that check mark it's actually a character what  character is it's character 252. i'll show you how   i know that in just a moment and where am i going  to be looking i'm going to be looking inside here   right here i want to know what row it's on right  but again it starts on row 5 right so we know that   so we're looking it up there and we're looking for  an exact match and i want to know and i want to   know plus 4 right because i want to know that if  there's an error i'm going to show empty okay that   way we're showing 5 it is the same one that i know  that air so all i need to do actually we just need   to actually change this to absolute here and then  we can drag it down now i want looking for this   check box right here how do i know that this  check box is there so when i go into insert   symbol and i'm looking for the check box here  i'm looking for this one right here you see this   wingdings 252 that's how i know what character  number it is and all i need to do is make sure   that this font is set to wingding so we look  at that we see the font's been set to wingdings   okay all right so i'm going to do the same thing  for this one so all i need to do is just drag that   down here and then we just need to make sure  that we actually add in matching character   252. but this time it's not going to be k it's  going to be this one's going to be column h i   l this one's going to be l l is of course  for our work order and then we have m   and that's going to be for our invoices so m is  for invoices then that's k l and m so we know   that our default invoice is located here thank you  that's the one our thank you message so it is that   one that we want to use and we're locating it on  the row but what if we don't want the row now we   can use the row or we can use the actual message  if i wanted the actual message i can use index but   the row is sufficient for now we could just put  the row if i know the row we can take that and put   that in here if we know that but what if we want  to put in the actual message there we could do   that with an index so let's say i want to do that  so i can do that index the footer message here we   can index the footer message and if we're indexing  it then we don't need to add the 4 on there   we're just going to be using the column 1. that's  it so that's gonna actually return that footer   message i think that's a little bit better so why  don't we do that again indexing we're indexing the   footer message what is the what is the array  that raise the footer message okay we're using   the row we're locating it here we don't need to  add the 4 in here comma 1 that's going to get us   our actual footer message so we know all we need  to do is place that right in here and then it's   going to automatically fill once we have the vba  working we're going to do the same thing for here   again index this repetition helps you learn  it for sure no doubt about that footer message   comma we've got the row now all we need to do is  get the column which is column one and that's it   okay thank you so we've got our footer message  we're going to save our work and now we're just   about ready to start some of the vba that's going  to help us navigate this application all right the   first thing that i'd like to do is i'm going to  color these buttons differently based on the order   type that's here so if it's one i want to put a  border around this and make it bold and i want to   make sure the rest of the ones or borders are  not bold and no borders around there so i want   to highlight our selected button here so to do  that we're going to get into the vba i'm going   to go into the developers visual basic alt f11  will get you there and i've got some module called   order sheet macros and that's the one we're going  to focus on right here and we're going to focus   on the order set buttons that's the one i want to  focus right now on and then we'll get to add new   so this order set buttons basically i want to set  those buttons based on what is selection so we're   going to start off with with the order sheet this  is the order sheet that we're going to primarily   do most of the work on i want to set a type  number i've got some defaults already set up here   i've got some dimension variables already set up  here order and i'll go through those as we need   them so the first thing what i want to do is we're  going to do a type number now the type number is   long here type number is going to be equal to  basically what is located in dot b range b1   b1 here dot range b1 that is the number it's gonna  be one two or three we'll call that the order type   number order type number okay so that's gonna be  it now with that what i want to do is i want to   run a loop so basically i want to run a loop  through all of the buttons now to do that the best   way to do that is to name our buttons something  very specific so we can do that right now so for   estimate i'm going to call this order type one  order type one okay for the second one i'm gonna   call this order type two and then obviously order  type three order type two and then for the invoice   we're gonna call that order type three that's  gonna make it a lot easier to work with them order   type three okay so now that we know this so if  this is going to be one we want this if it's two   and three okay now that we have those if you  can see that they're all been named okay so we   continue back on the mattress so we got the type  number so now all we need to do is run a loop we   have another long variable called for order number  four order number is going to be equal to one two   three basically we're going to loop through those  three buttons close our loop next order number   also a long variable so inside that what i want  to do is i want to determine what the current   type is what the actual type and that's based  on our variable type number if the order number   is equal to the type number then that's the  one we want to highlight then do something   else do something else okay so this would be the  current button current button and what does that   mean basically what i want to do and i'll show  you how we do that so let's say we're currently on   estimates we know this is one we know the button's  one so it's currently on one so that means the   type is one and when the button is one so what  i want to do is i want then color this button   one so that's all we have to do there is highlight  that button so how do we do that well we're going   to start out with the shapes dot shapes and then  we know it's order type right that's the name   we assigned to it order type and then also and  the order number because we're looping through   so this is how we use each one order in this case  number dot line i want to only increase that line   line dot weight the weight of that line i want to  make it three so it's a little bit bold right and   then what i want to do is i also want to make  the text bold so how do we do that dot shapes   in this case the same one so actually what we can  do is just copy and paste that we don't have to   retype it but this time we're going to focus on  the text in the text frame so we do that with   dot text dot text here text frame to in this case  we're gonna so dot text range dot font dot bold   equals mso true we're making it bold bold font  and then this case increase order width perfect   so that's what we're going to do for our selected  but what about for the others for the others i   simply want to make them return those so in this  case what we can do is simply copy this here   paste this down here and then reset it not three  in this case it's going to be zero so reset in   this case it's going to be reset the border width  for those that are not reset the border with   okay and then this one's going to be mso false  we don't want it bold all right so there we go   we've got it like that that's all we really need  to a relatively simple macro now of course when   we select buttons we're simply going to run this  macro that's this macro down here okay let's go   ahead and test this out we'll bring this down here  okay and then we can bring this up here going back   in the macro all i need to do is just run this  macro that we just created and we're going to see   that it's now got this border so basically what we  want to do is we've got a good border it's working   right it's bold but i want to actually make the  borders probably white probably let's go ahead and   shape outline and we'll make them white okay  so the custard you could really do any color   but we'll make it white okay i like that that's  looking really good and then what i want to do   is i'm going to probably move this one to the top  bring to the front that looks good i like and then   we're going to bring this to the back then this  one send to the back okay so i like gives that   gives that that nice effect feel all right good  so if i were to change this to 2 right if i were   to do that we're going to change this to let's  say work order and the macro of course is going   to take care of that we run the code one more  time i'm going to make sure that that's that   work order that gets highlighted okay perfect  so that's exactly what i want now theoretically   we could bring that shape to the top which would  kind of be nice but i'm not sure if we want to do   that in other words bring it we can also just move  them a little bit over there so they're perfectly   separated and then that we won't have to worry  about bringing them to the top or something so   just giving that that effect okay i like that  there that's working great what i want to do now   is create the macro that we're going to use when  we actually select it so let's call that's called   the macro select so that's one order select here  that's the one i want to work on now since our   macro is working just fine for this to do that  again we're going to focus on with orders in   this case what i want to do is i want to know the  application color that's the name of the button   that called this shape so to do that right if i  want to know the number if i want to know if i   want to extract one or i want to extract two all  i need to do is know the name which is this one   and i remove the text order type that's going to  leave me with one or two and then all i need to do   is determine what is here then i need to determine  if it's one then all i need to do is put whatever   is here if it's two i put here and three then i'm  gonna take whatever that text is i'm gonna place   it directly in b2 so we're gonna start off with  just that so with orders we're gonna do that we're   going to set the order shape which is already  defined as a shape up here order shape as a shape   that's exactly what i want set the order shape  it's going to be equal to dot shapes we're already   in dot orders application dot collar okay so all  that's going to do is set the shape that called   it i also want the order type the order type is  going to be equal to dot shapes again application   color in this case what i want to do is i want  to have that text frame i want to extract the   text so the text this is another way text frame  dot text range dot text that is the text of the   button it's estimate work order invoice so that's  going to extract the name easily enough right   so our order type is extract name so we've got  estimate work order inside our variable here   which is the order type which is a string variable  okay so what i want to do is i want to know if for   example if they select this if we're currently on  work order if it's equal to this already there's   nothing to do we're not changing in other words  if we're currently on work order and i select work   order there's nothing to do right we're not we  don't need to go to estimate we don't need to do   anything so we need to exit the sub so what i want  to check is if the text the text here which is   work order is it the same as what's in v2 if it is  then do nothing and exit the sub so we can do that   in defense statement if the order type is equal  to dot range b2 dot value then exit sub exit on   same order type on same order type okay nothing we  need to do we're not changing the order type so we   don't need to worry about that but if it's not if  it's different then we need to do we want to set   b2 to whatever the order type is so dot range  b2 becomes whatever the order type it equals the   order type set order type relatively explanatory  okay so now what i want to do is i want to run   this macro that's automatically going to highlight  these buttons that's the macro we first created so   i'm just going to copy this can drag this down  here create a little more space for us so we   can see what we're doing then i'm going to paste  it directly in here okay so now we're going to   color buttons ingly okay because we've  already added the oh as soon as we add this   this is going to change right as soon as we  take this as soon as we put this to estimate   it's going to change this number and once that  number has changed then we can then go ahead   and color the buttons ingly all right so  once that done we're going to color the buttons   now next up what i want to do is i want to clear  some information on here we need to add a little   bit more information what i want to do is i want  to track some information about this particular   invoice or work order or estimate i want to track  that it's very very important so i want to know   what's been estimated in other words if we're on  the invoice if we're looking at the invoice here   i want to know what's been i want that all the  estimated information to be here it's off the   screen so when we print it nobody will see it but  i want that so i'm going to put that right in here   so i'm going to put called this estimated i want  to know what's been estimated inside here i want   the estimate total in this case estimate totals  so i'm going to put down here probably the total   price and then in here i want to know what the  quantity was estimated quantity the estimated i   also want to know the price of what was estimated  and also again the cost what is the cost of what's   estimated i want the total price total price is  simply the quantity times the price of estimated   what that's going to do let's say the reason we're  doing this is let's say we're on the invoice i'll   change this to invoice so it's a little bit  invoice we don't need to add some conditional   formatting onto this tube and that's going to help  us okay so let's say we're on the invoice we're   looking at the invoice here's all the invoice  information i want to compare what was estimated   and i want to have all that estimated information  here when i can compare the two i can create a pie   chart that's going to do just that okay so we need  the total price right and then i want to know the   total cost and then i want to know the estimated  profit what is the estimated profit that we had   we know the total price we know the total cost  what was estimated so we can determine with the   estimated profit i want to compare that with the  actual profit so profit so we got the estimated   profit so this is all for estimated so all i'm  going to do is just put some borders around here   and bring that back up here put some borders  around here like that so we have that and now what   i want and of course we're going to sum up all  the prices sum up all the costs and some of them   and next up what i want to do is i want to have  the invoice so invoiced i want to know everything   that's been invoiced right now we have the invoice  information here but i also want to know the costs   associated notice the invoice just has what we're  selling it for but what about the cost we have to   keep track of the cost the actual cost that's not  on the invoice it's what whatever we're sending to   the customer doesn't have our cost associated here  but we do need to keep track of those costs and we   need to know what the cost was so i'm going to put  the cost down here okay so the first thing what   i want is the invoice totals just like we have  the estimate totals okay and then in this case   i want to know the item cost the item what is the  individual item cost and i want to know the total   cost so let's say we have the item cost here  we know the quantities here so we multiply the   quantity times the atom cost and we get the total  cost and again once we know what's the total cost   and we know our total that we're taking in we can  determine the total profit so that's just what i   want i want another profit how much profit did we  make so this is going to be all for invoicing so   we can just put some borders around here just like  we did so now we've got that so while putting i'll   sum the total price we'll use a sum formula here  lastly what i want is the invoice data i want to   know the item database so i'm going to put that  in here i'll show you that it's item database   and then row down here and they'll just put  database row all right so this column column s   is going to handle that what is that exactly well  that's the row that's associated every time we   save it it's got to be stored somewhere it's going  to be stored in here so i got to know the row is   it 9 10 or 11. so when we bring this information  into it we're going to take this row and we're   going to bring it directly in and we're going to  have it row that way i know if i need to make an   update i know what row to update if there's no  database row here i know to add a new one so   we have all that information okay that's going to  help us out and what i'm going to do is i'm going   to put some borders around here so it's going to  be basically going to come all the way down here   so i'm going to put some format the cells and put  some borders around here i'll put a solid border   on the left right and bottom and i'm going to do  the same thing here so we can keep track of it   and also here okay so we have some solid borders  here we're going to add some formulas and we're   going to be adding so a little bit of the  formatting but what i want to do now is also   doing some conditional formatting good so we can  distinctly uh see the difference between all these   columns and we understand what they're going to be  for of course i'm going to go over it again even   if you don't quite get it don't worry about it we  always go over it again so saving our work as we   do frequently we're going to put we want those pie  charts up here but what i want to do now is i want   to put some conditional formatting based on it if  it's an estimate i want to change the look of the   screen as you if you caught the beginning of this  you saw that it looks very different so let&