Streamline Your Logistics with Bill Excel for Logistics

Bill excel for Logistics

hello this is Randy with Excel for freelancers and  in this week's training we're gonna be building   a dynamic vehicle expense tracker we're gonna  have a single click menu at the top we're going   to be able to choose from vehicles repairs  and maintenance fuel purchases reminders   expenses and we're gonna have a dashboard  all built into one single application and   we're gonna build it from scratch right in front  of your eyes I can't wait so let's get started alright thanks so much for joining me today we've  got an amazing vehicle expense tracker we're gonna   build right in front of you this is gonna be able  to track all of your vehicle expenses we're gonna   be able to track reminders things that you want  to check for in the future well have a little   dashboard it'll cover any miscellaneous expenses  fuel costs and whatnot we've got it all covered in   this dynamic training I'm gonna do all that and  wrapping it all up into a single click tab with   shapes up at the top so we're gonna be able to  do different sections really a cool single click   tab feature on pictures so I can't wait to show  that to you before I get started I want to make   sure that you have subscribed if not please do so  make sure you click the reminder icon that'll make   sure you get these free videos each and every  week and if you do like these applications I'll   include a link down below you can get downloaded  for free but if you want 100 for just $37 I've   got an incredible zip package full of all of my  best training videos actually over 100 of them   all the applications in one single zip folder  and that's going to include a library so you're   gonna be able to single click the video single  click to open the application a really dynamic   library that's all included and it's only $37 so  if you haven't gotten that yet make sure you pick   it up while I keep that price low alright I want  to get started on this I show you how to create   these amazing applications each and every week if  you want to learn how to create these applications   and turn that into passive reoccurring income I  want to help you and that's going to be through   our mentorship program go to my excel mentor.com  I'm going to show you every single step on how to   create these alpha and turned them into income  that you can count on you won't get fired from   a job again you will never have to worry when  you take my mentorship of course I will ensure   that you have all the ability to create these  applications sell them on your own and that's   going to be coming from defining the application  all the way into designing your applications   we're gonna move into the development phase and  finally deployment where we're gonna show you   how to license and secure protected put it on a  website and create cash flow from your website   all right that's great that's an our mentorship  program I hope you'll join us there all include   the links down below so if you want to check  that out go ahead alright let's get started on   this week's training because we've got so much  to cover what I want to do is I want to create   a basically a single click application icons at  the top and then we'll have about six different   options so what I'm going to do is I'm going  to save those first two columns just for Adam   so you can hide them and this top we're gonna  give this a nice different fate just like we   do before but I'm gonna format those cells what  I want to do is I want to create a really nice   effect maybe a fade effect so we'll just use  the standard blue code that we usually do but   this one's gonna be very distinct because I'm  gonna create a really really unique feature in   this second row here and what that we're gonna do  is we're going to be able to give a single click   we're gonna have buttons and shapes and that's  gonna create more of a horizontal tab feature   where we can click we have horizontal tabs and  that will click button so what I want to do is   I want to put a button here here here and here so  we'll put different buttons so it's going to be   shapes it'll be really cool I'm gonna use this  one for conditional formatting I'm gonna show   you how we do that but right now what we'll do is  we'll just color all the way down maybe way down   just down here at a 91 or something like that  we're gonna go way down because what I want to   do is create a vertical so we're gonna hide and  show rows ingly so maybe on the first row   we're going to show different vehicles I want to  have multiple vehicles and I want to start that   off so maybe in d5 I want to put a vehicle list  here so this is gonna be our first tab vehicle   list so let's just say Car one so we'll this car  one I will just go down you know a few different   cars I'm going to change that so basically I  want this to have a car list and will include   maybe we're gonna go I want this first group to  be stuff from let's say for two we'll go down to   line 16 so I'm just gonna create a border around  that and that'll be our first group because I want   different groups so basically what I want to do is  I want to create different groups so basically I   want to create different groups here so our first  group are here and here and here and here and then   each what we're going to do is we're gonna hide  the rows and then we're gonna go to the second   group hide the rows and go to the second group so  this first group I want to call this vehicles so   let's just write that out vehicles that's going  to track all of our vehicles here so I'm gonna   merge and center that across and then what we're  gonna do is we're going to duplicate that across   each one section and each section is gonna have a  different name so we can track all the different   types of expenses for those vehicles and let's  make that a little bit larger so we can see it   okay that's nice I like that and I want to do  the same thing for for the next and the next one   I want to create a repair section so what we'll  do is we'll do this and then I want repairs this   one's going to be repairs and expenses okay  so one truck repairs and expenses okay but   not obviously this one's not gonna have a vehicle  list and then the next section what I want to do   is I want to have fuel purchases and tracking and  so what I'll do is I'll just copy this here and   then we'll bring it down here and we'll bring this  down we can we can extend this gray also so this   one's going to be fuel purchases and tracking  because I want to track all the fuel purchases and tracking and what about the next one I also  want to know information about reminders I want to   keep track of if there's an oil change needed or  if there's some kind of maintenance that's regular   I want to know that so the best thing to do is do  remind us who's going to be a little bit larger   though because I want to track multiple reminders  so let's go down about 260 here and I'm going to   format that cells put a border around that just  temporarily so we can keep track of the different   sections and we're gonna call this one reminders  just call it reminders merge the center again I'm   gonna just going to copy this here and then paste  it down here because I want to make sure it's the   same size and we call this reminders so we're  going to have reminders of different aspects of   our car maintenance that we want to keep track  of so lastly I want to have meter tracking and   then we'll put in reports so meter parking it's  gonna be the same as purchasing tracking means I   wanna track I want to know the meter you know for  how many kilometers did it go and what dates and   I want to keep track of all of that and so we'll  call that meter tracking and each one of these are   going to be a tab so that way we're just gonna see  only one section at a time and then last thing I   want to complete dashboard I think that's gonna  really help us so I want to know all the costs   for a particular car all of the different leaders  and the information fuel and repair expenses so I   want that so vehicle dashboard okay that's gonna  round it out now we've got our sections so what I   want to do is I want to display only one of these  sections at a single time and I want to use a tab   to do that and so make sure we have all of our  tabs on correctly we can make this one a little   bit larger I think we're gonna need some more room  for this so let's go down to say ninety all right   I'm gonna format those cells and I'm just gonna  bring it up what I'm gonna use this blue color   all the way down here and just that's gonna be  enough that'll cover our dashboard we'll give it   a little bit larger dashboard here so we've got  it covered okay I'm gonna form out those cells   again alright now that we've got our basics what  I want to do is I want to enter some shapes and   I want to basically have a single click so as we  click on each item here dead rows show and hide   ingly so let's give it a title of course we  need to need on our application and we also need   some icon so let's gonna call this vehicle expense  tracker okay and then I'll just merge the center   that give it a font increase the font up and then  I'll just use a specific font that I like okay so   we've got our vehicle expense tracker and let's  put in some icons and some buttons now so we can   build this out and see just how we're going to  do that so I'm gonna insert some pictures and   what pictures drawn I've got some pictures here  saved up in for this one so let's bring them all   in just for this application so I'm gonna bring it  everything but the cars we're gonna add the cars   in a little bit later on that's per particular car  we're gonna be allowed to show car pictures okay   good now I'm just gonna bring those Heights down  something a little bit more manageable alright   good so we have this we're gonna use this for our  buttons I want to be able to add vehicles we're   gonna use this also and I want to use days for  reminders this is for our button so I'll start   separating these here so we know exactly what we  want to put in I want to be able to add vehicles   I want to be able to show a dashboard I want  to show reminders so this is gonna be a very   comprehensive application so I can't wait to show  it to you and bring it to life right in front of   you I'm gonna use this for our vehicles and I'm  use this for our main logo here just so we can   see that that's nice give it not too much because  I want to save room for our buttons now I want to   create a background for the but okay so let's do  just that what I'll do is I'm going to insert it   basically a square shape is what I want for our  backgrounds and something like this and I'll keep   it about point and let's see let's point some more  and then make it a little bit a little bit bigger   point in 94 okay so we have that let's give it  a color that's a little bit different something   like that perfect so that's what I want I want to  duplicate I'm gonna have about six buttons just   that shape so let's duplicate that six different  times and we'll bring them slide and I want to   actually let's change the border on that I want a  border consistent with our theme here so I'm gonna   right click format the object and we'll give it a  border of let's see a line color solid line um use   the same color here okay that'll be good that's  exactly what I want let's duplicate it we want   one more button and so basically what I want to  do is I want to put these icons on top of these   buttons and use these buttons to control the tab  you can do this in your application as well so   we're gonna have six of them different total and I  want them to line up here ing to the columns   just put them up here line them up all the way  so okay so now we've got our our buttons here   and then on top of these things I'm going to put  the card the maintenance then I'm going to pull it   probably the fuel because I want to know the fuel  and then I want to pull it reminders and then the   last thing is the reports and I think this one  we don't use okay so that I've got that there   now let's sighs these ingly I'm gonna hold  down the control I want to bring them to the front   so I'm gonna do format bring forward and then  bring to the front and then I'm going to align   them let's give them bigger 0.6 and that's pretty  good right there okay and then what I want to do   is I want to bring them basically on top of these  buttons just like this nice and big and nice and   clear so we can see them ingly okay I like  that and now all we need to do is Center them so   I'm gonna hold down the control and I've got my  shortcuts here Center and center and I'm gonna do   the same thing here and then I'm gonna group them  right to their center and then they can act as   tab buttons it's gonna be really cool so when we  select these it's gonna open up those individual   tabs ingly so all we need to do is again  make sure they're centered and then we'll group   them so I hold down the control move and now we  can group them normally we'll name them I'm just   gonna group them now so that ways they stayed  together and then I'm gonna group them one more   time and do a single menu here so just using my  shortcuts here you can put your own shortcuts at   the top things that you use often should always be  at the top here and that's what I tried to do here   alright it looks good we're all grouped together  now and now all we need to do is just line them   up and I'd like to line them up ing to these  columns and I'm gonna bring them down but I want   to make sure that they're aligned to these columns  that looks good right there so I'll just bring it   down and then I'm gonna align them together just  like that and then I'll do the same thing because   that way they're nice and tight and all aligned  together and I'll show you why we're gonna align   them to the columns I think it's very important  as we align them ingly because we want the   column I'm going to use a conditional formatting  to show which one's open it's a really cool effect   and basically we're gonna use the row below and  conditional formatting it's going to show us   which tab is open so I think it's gonna be really  helpful something very unique too as well to the   art training here that's why I'm lining them  up ing to the columns you notice how J   is exactly aligned to this column and all them  in a double check I'm gonna line them and I'll   show you a trick to do that so basically when we  wanted to line up I want to align this column up   exactly as it is this looks good you see how they  were lined up perfectly perfect here okay we're   good so that's exactly what I want now once I have  all this on again I'm gonna hold down my control   and group and the second thing the first thing  you want to do is group second most important   thing especially when you have sizing size and  properties and always go into the properties   and move but don't slice that way if the columns  move these two shapes don't move so now I'm also   gonna raise it up and I want it exactly aligned to  disk right out see this row here I want it aligned   why is that because I'm gonna use conditional  formatting here here and here to show which tab   so notice it's a little bit too high I want to  bring it down right on the border there right   there so that way if we have a border here it's  gonna line up ingly right so I want to know   which tab was open as we click these if we click  this vehicles is going to show up if we click this   repairs and maintenance is going to show up and  so on and so forth for all six different tabs and   conditional formatting is gonna help us do that  so we're gonna be able to understand how that   works through conditional formatting so let's get  into that and let's show how we get this tab and   let's make this functioning before we add in our  buttons we'll keep these up here we're gonna add   in some buttons very very soon all right so how  do we apply that conditional formatting well the   first thing I want to know is what selected tab  should be open and let's just put that here we'll   call this selected tab and then I'm just gonna  put let's say five right it's gonna be five six   seven or eight it's based on the column II would  be five based on the column equals column right   so you see that's five the next one six and so on  so when this turns five what do I want to happen   I want to give it a special effect what kind of  effect let's highlight all of the cells in which   we want to apply the conditional formatting manage  rules and then create a new rule and I'm going to   go into format and I'm going to use a formula I  should say and I'm going to use a formula equals   call them what column is this actually equals  B 3 is equal to what column right column is the   one we want to focus on now what is the format of  what I want to do is I want to blend the bottom   of the button here with the top of the cell so  I'm gonna give it a format I'm gonna give it a   fill and a fill effects now the bottom of the  button is probably about this gray here I think   it is all double checking about this gray here  and then the bottom of course is the background   or so so I want to give it that blend like that  but I also want to give it a border I want to I   want to give you the border that same blue on  the left and the right and I want to make sure   the bottom on the border is no longer there I'm  gonna hide that bottom on the bar so something   like this let's take a look at that and see how  we did that's pretty much what I want the buttons   a little bit high I'm gonna bring that down just  a little bit like that that's what I want that's   the effect what I want right so when this changes  to 6 you see how it changes that's what I want so   it ties it in you see how that tab works really  nicely as we change it but that's exactly what I   want so as we click on the tab it's gonna give us  that nice blend effect it blends from the button   into the background and gives it a nice look and  notice how all the borders are the same they're   all lined up that's why we wanted to line up the  buttons with the borders you see how that works   right so as soon as we hit 9 it's gonna load in  that button and the last one 10 so double-check   that they're all working that's the effect that  I want perfect okay so we have that and we'll   have to adjust it ingly I'm gonna have to  remove this border down the line just so we can   see it but that's a nice effect I'm gonna do the  same thing here with the cars on when we select a   car I'm gonna want to know so let's give these  a specific border I'm gonna give this one and   we're gonna be able to add cars add pictures it's  gonna be really cool so we're going to give this   to Phil effect will use the same Phil effect that  we do the same theme as our what we're working on   here which is just blue in this case and we'll  reduce that and I want to do the same thing for   cars but basically what I'm going to do is let's  say we have about up to 10 different cars so I'm   gonna format these I'm gonna give it the standard  format Phil effects and I'll do vertical this time   I'm going to use the same colors but I'm gonna do  a vertical so I'm going to do something like this   and then we'll do light to dark dress now there's  going to be conditional formatting so when I   select a car here I want the reverse to go in so  let's actually create some borders around those   again it's just like formatting the cells and  just more colors I'm going to go into the border   and then use this same border all the way around  here and then what we're gonna do is we're gonna   do conditional format it's going to give it that  effect so we also have to know what the car row   is the selected car rule put that in six selected  car when we select a car I want those details of   that car to show up right here so wondering when  I select the car I'm gonna need to know so let's   just say we choose six right and what I wanted we  choose six I want conditional formatting to apply   to these again so let's do that let's highlight  all the rows go into conditional formatting and   we'll clear eight a new rule and this time we're  going to use row so this is going to be equal B   six row and then open and close parentheses format  what is the format again I'm looking for some kind   of a blend so I want to go to the fill effects  I'm going to use this medium to this light and I   want this to blend vertically into the background  so I'm gonna use this remember the right one the   lightest one is our background color so when we  do that we get that effect but there's one another   thing I want that border on the right to be gone  so let's go back into the conditional formatting   just like the board has gone up here edit the  role format going to the borders and remove   this border here now when we click OK and click OK  now we want that's what I want so when it changes   the car - it changes perfect so as we select on a  specific car it's going to change let's add some   fields well cut what we want to know about our car  well I want to know the car name and then we want   to know the plate number right the license plate  number we'll need that that's probably important   I also want the make and maybe the model and we  want to know the type of the car cuz I buy here   and seats if we're running some kind of a company  that might help us and then maybe some notes down   here okay that's basically what I want to do is  on highlight these I'm gonna write justify them   and then I give them I think we'll go without the  board and then what I want to do is just hold down   the control and then we'll even with the notes and  I'll increase that notes field that should be much   bigger and then format those cells and then we'll  do the same border colors we have this is gonna   give us a nice clean look all the way around it  and that's fine that's good enough and then notes   I want big something like that's a merchant center  left and upper and then we'll format the cells   make sure those borders are all set just as we  wanted them okay that's gonna fill out our thing   but I do want also so I do also want a picture on  that so let's set some room for a picture because   it's nice to have a vehicle picture so I'm gonna  merge and center this and call this the Hickel   picture and actually I can use the same formatting  so we're gonna format those cells same colors that   we do so just that Phil Phil effects and we're  gonna go into that same blue just so we have the   same theme colors and same as the vehicle list up  there I want to use the same type of film so good   so let's control these make sure they're centered  both and then bolt alright good that's gonna give   us it and then I give it a border around here  and I want to make sure we give it all the way   around format the cells and I'll put a button set  down below so give it a border here all the way   around and that's good and now I want a button  set probably I'm gonna put the picture in here   and so I want the button set down here so let's  again format those those put a border around this   also alright so what kind of button so I want  to be able to add a picture and clear a picture   we can use this same button set now I'm going to  ctrl C and control V because I want it outside if   it's ctrl D it's going to be inside okay inside  the group meaning control D will keep it inside   the group control C and V outside the group so  I'm going to put one this is going to be our add   a picture and then I'm going to control D again  and I want to be able to clear the picture using   these two buttons here all right that looks good  I'll just size them ingly so that they're   don't overlap and it should be fine find the way  this alright so we're gonna have ad let's write   in ad picture and then I'll give it a format let's  see format in the both center and then right and   then middle there that's what I want and bold and  give it a nice let me give it a little bit of a   more clear alright good so we have that and then  I'm just gonna call this clear now all we do is   we need to add the icon so I'm going to put this  icon here and I'm going to bring it to the front   of course we need to in front and then I'll do the  same thing with this also need to bring that to   the front too and this is going to be called add  picture okay I like that's nice and big buttons   nice and clear and that looks good so we have  that and let's just Center everything holding   down the control only now make sure everything's  nice and in the middle aligned middle here that's   gonna be everything and then we'll group so I'm  going to group these and then I'm gonna group   this then we'll assign macros that's gonna help  us add a picture to the car each individual car   is gonna have a different picture so it's gonna  be very helpful okay I like the way that looks   let's save what we've done and then I'm going  to left justify all of these here left justify   and again this one's left an upper alright it  looks good now our vehicles is clearing out so   also what I have here's some data I have a vehicle  list that's gonna help us move things are forward   I have an expense list with some data already  some dating here some expense types a fuel log   that's gonna be helpful I've got some reminders  here already set up for us and I also have some   meter tracking just some data so that's all the  data it's gonna help us bring this application   to life and I've also got some named ranges set up  for us all automatically to help move things along   cuz these trainings are long let's take a look at  some of the names so I've got I've got a dynamic   field for vehicles let's take a look at this it's  using offset and it's called vehicles and that's   going to cover all of our vehicle as as this grows  its going to grow as well now remember you want to   use your header row a three and then move rode  down why do we want to use a three because when   there's no data it will not return an error so  keep that in mind use the header rows in your   name range but make sure you don't include that  when you're counting or don't starting from that   really so when you tab out and tab back in you'll  see that the dancing ants part of the data okay so   I've got that what are their named ranges while  we're in there so I got I've got expense amount   that's a that's a gonna track all of our expense  amount I've got expense date that's gonna track   all of our expense date expense type and expense  vehicle again all tracking in our expenses because   we want to know what all the vehicles what all  the dates the amounts and all of our expenses   so those uses the same offset and named range of  course you can download it and take a look at it   but I've been out a few times before just want to  make sure these strings are each unique for you   we have expense types which I'm gonna get to I'm  gonna put those in and I did I didn't add those   in but I'm gonna add them in between a 19 and a 25  let's do that now 819 let's put some expense types   in there so we can you know I have an expense list  so let's just drop down 16 and we'll call this our   expense type I'm gonna copy these go into visit  middle and I'm gonna right click and paste those   in here so now when we go into name manager and  we go into expense type we see we have our dancing   apps and it's an offset so we can add more expense  types of that if we want we can add more so we got   oils tires repairs ok good good so we have a  list of system expense types gonna help us you   can add to that but this is the name of the car so  what else do we want to create we want to create   Rhys pairs and expenses so we also want to create  these tabs let's get these tabs built out so that   we can then build out our intersections using the  tabs how do we do that well we write a macro what   I want to do is when I click click here right  this is going to change to 5 right when I click   here this is gonna change to 5 and I want this to  display and what else I want when I click here I   want this to display but let's get our buttons in  so we can get all this built in so we know exactly   what we're doing what do I want with repairs and  expenses I want to track the repairs and expenses   I think it's really important that we track well  first we need to know what car we're tracking   so that's really important so in f19 we're gonna  track the car let's again call it vehicle and then   I want the vehicle here and then I want the date  what is the date of the expense and then next up I   want the amount what is the amount of the expense  that's very important I also want the expense type   remember we have expense types here so we need  to know what the expense type and we need to know   the vendor if there's a company that we purchased  it from that's also important ok so we have those   let's hold down the control right justify those  here and then what we'll do is I'll just put in   the field some white fields with the border in  fact I can just copy this here hold the control   and just paste it right down here ok good and of  course the vendors I'm going to merge the center   that I want a larger field for that and then also  again so let's put in some notes for the expense   it's always nice to have notes so merge and center  that here I'm going to create a larger field all   the way down to 28 merge and center that color  white give it some borders we can also set the   borders here our color that we're using commonly  so I'll set that and then we can just return our   borders here and then here ok so that's going  to be helpful now that we have the notes again   left just right justify this here so we have all  this information that's going to really help us   determine it but what about the vehicle we're  gonna use the dynamic we're gonna use the data   validation for that so let's create that because  I want to select from a list of vehicles and   what are those vehicles it's going to be equal to  vehicles and then just enjoy that so now we have   a drop-down list of vehicles and these vehicles  actually what I really wanted to do is I'm gonna   link these vehicles to this vehicle list so how do  we do that I'm just gonna copy 10 of them ctrl C   I'm gonna copy those and I'm gonna paste the link  so that way when the vehicle name changes so does   it here so in this list as we click this list  we want the vehicle information and the picture   to show up here so it's going to be multi tab we  have vertical and horizontal really cool really   different let's create some buttons some more  buttons we want to do is we want to be able to   add that vehicle right and we want to be able to  save that vehicle so that's really important so   let's click on here I'm gonna use this one also  here I'm going to copy this a little bit smaller   than this I'm gonna add a vehicle and I want to  also save a vehicle so let's put point to just   call point to so we have our button here and I'm  gonna copy this button here but I want to make   it a little bit smaller again I'm using control-c  so it's outside the group and I'm going to format   that let's put the height down at point two five  that's a little bit more picture and we'll call   this add vehicle keep them going to keep it real  simple and I'm going to duplicate this and we're   going to call it's a vehicle so all we're doing  is we're adding or saving pretty simple will   add up to 10 and I'll add this to the add and  then this is going to be to the save and then   I'll move these two buttons click on both and then  move them to the back all the way to the back and   that's going to bring those icons up to the top  that's what I want okay perfect so we've got the   ability to save and save vehicle and add vehicle  these two buttons are not going to show up at the   same time so we can put them right on top of each  other I'm gonna line the middle I'm gonna group   them and then we're gonna call it save vehicle  but and then another one we're gonna call this   add vehicle button again I'm going to Center you  can use this group them and call this add vehicle   ok so we have those we're gonna be able to add  and save them with these buttons and I'm gonna   hold the ctrl again on these two groups here and  then I want to make sure they're both aligned in   the middle and both lines and send them bring  it over here probably something up to the left   that's what I want here that's nice so this like  but what I want to do is I also want to make sure   that these buttons are now grouped all together  in other words when I click another one I want   to make sure that these buttons don't get messed  up so when I click on any other tab I want these   buttons to be hidden so we have to do that one  and make sure we do that to do that let's group   them so we're going to call this I'm gonna call  this vehicle group because we know it's a vehicle   group and so that way they get hidden and we'll  do the same thing for the each of the other tabs   what do I want I want these repairs maintance  I'm gonna copy this icon and I'm gonna put it   right here I want that - no it's repair zone I  cuz I'm gonna do the same thing for each one of   those copy the fuel and put it right here we're  going to be adding some fields for the fuel for   the reminders as well I'm gonna copy that and I'm  gonna put that right down here and then two more   of course we want to have the meter tracking all  the way down here under the meter and then the   last thing I want the dashboards here so we can  create a really nice little dashboard here just   a small one it's gonna gonna show one but you can  expand yours because this is gonna be long enough   video as it is okay so we get to see we have those  and so let's do that let's get this up and running   let's get these tabs up and let's get these fields  we got a few more fields that I want to add in I   also want to know the vehicle and let's just copy  what we've done here and copy that down here and   then just make some changes ingly okay so  in this case we have fuel we also have date on   this one I also want the amount but this time it  let's call this unit and I want to know the unit   of fuel is that gallons leaders whatever it is so  that's I think that's kind of nice quantity and   this is quantity how many gallons how many liters  how many you know so we don't need this Merchant   Center let's bring this down here and what else  do we want to know I want to know the station   it's like the vendor like gas station right where  did they purchase where did they purchase their   fuel I'm gonna copy this and then just paste the  format's in here so we can see the format's okay   so we've got that that's good I like that I'm  gonna do the same thing here just copy that all   right so now we have notes that's pretty much it  for a feel but I do need to add a button I want to   know if we're gonna save the fuel we'll just add  a fuel so what I'm gonna do is I'm just gonna copy   this button here I'm gonna bring it down here and  then I'm just gonna change the text of fuel much   easier to just create a button here and then let's  see I can make this a little bit shorter so we can   go in the format and make this about one inch  I like that okay what else I also want to do a   reminder right we gotta save I'm gonna copy this  and now we got to add some reminders and what do   we want for the reminders well for the reminders I  also want to table down at the bottom too so again   we need to know the vehicle and let's just again  let's make a copy of this just these fields here   paste them right in here bring this button up a  little bit so we have so in this case I want to   know the car but I also want to know what's the  name of the reminder name of the reminders very   very important and I also want to know the next  date where we have to the next date because we   have to know when that reminder should go off I  also want to know what the frequency is so let's   put in every something days okay so we're on days  and I'm gonna make this on the left right because   this and then this field this is not going to  be a field we'll just clear that out and we can   do that by just bringing this down here the last  thing is I want two other statuses that active or   inactive right that way we can make them reminders  inactive if we want to lastly what I really wanna   do is I wanna have a table down here I want to  know exactly all the pre recent so let's put a   table down here all the way down maybe to row 59  and I'd like to have yeah let's just put a table   in here and again we're putting some conditional  formatting so the foots the first thing I want I   want to know the vehicle where the reminder is  I want to know the name of the reminder next   date frequency and the status and next up the  last date so that's all the information that   I want to know on those reminders and let's just  format those will Center it again let's go ahead   and format those cells so we can get a nice  look on what we want film using the same fill   consistency that we have before and good alright  so we have that covered and let's go ahead and   give it some borders on that as well same color  using automatically that we have in the past all   right no it's not gonna make it too beautiful and  then some conditional formatting and some borders   on here and we're good to go on that table okay so  we're good on that and let's go ahead and add some   additional formatting just to give it some odd or  even row look so we'll create a new rule and use   a formula and then equals mod and I've got this  automated here so it's in my autohotkey fill it   up I'll use this color but maybe a little bit more  lighter than that okay so now we've got it and now   I'm just gonna add some internal lines here maybe  some dot it just so we can separate the fields   then we have our table build up sorry format  cells is off the dotted line here but okay so   you see how we can build out these tables really  quickly that's it that's for the reminder and   let's just change this obviously we're not this  is not a field but we want use this to reminder   mine dirt can make that button a little bit bigger  here and let's just go to 1.3 okay that's good so   saving the reminder what else do we need what  I also need to know the meter tracking so let's   do that here and again same thing we're gonna  be able to keeping consistent with our theme   gonna call this save meter tracking down here  meter tracking we want to be able to save the   tracking because we have all that in different  tables okay so you have all that in different   tables and let's extend that 1.5 okay that's a  lot big enough 1.7 all right I like that what   do we want to know for the meter tracking again  we want to know basically the same information   let's just copy this because we can use similar  fields and then bring it right down here and so   again we want to know the vehicle let's put in the  type of one of the type of tracking and the date   again the date that of our reading and also I want  to know what is the reading you know what is the   the number like easily it's a mileage or kilometer  or something we want to keep track of that alright   good and then again notes field let's copy those  notes that we have up here and then bring them   down here all right so we have notes and meter  tracking we're gonna save the meter tracking   lastly I want a dashboard here we'll fill that out  that'll be last because we have some information   but what do we want there I want to know again  some information when we have the dashboard I   want to know what car I want to display so let's  put that information right here I'm gonna paste   that and again we want to know the car but I  want to know the dates from what do we want to   show from - right - so from a specific date to a  specific day okay all right it looks like we've   got everything built up just the way we want it  I've got a our border here let's update that so   everything's consistent now we're ready so now we  get a nice idea of all of the information that we   want - now all we need to do before we winter tab  but just make sure that our groups are set what   do I mean by groups are groups of shapes like  I want these shapes to be here we have to save   the repair as an expense so let's need a button  for that let's use the larger button and let's   call this just save expense here bring this down  here expense okay so we have save expense and so   what I mean by grouping is I want to take this  and this and I want to group them together and   then what I want to make sure is I want to make  sure we give it a very specific name so let's do   that let's call that repair group so that way we  can hide that group ing and remember every   time I'll select all these groups and make sure  they're not the next one I want to call this fuel   group so we're gonna select those those group them  fuel group and then again same thing with each one   so we can set call call this reminder group and I  also want to do the meter group so again so that   way we have everything group probably and we'll  call this meter group meter group okay lastly of   course we also need to have the dashboard group  and that's just this one here so we'll just call   this two dash dash board group what I want to do  is I want to hold the ctrl down and I'll scroll   up and I want to select each group that we just  created and make sure they're set to move but   not sighs move but not sighs just selecting them  all everything that we have all those groups here   including this one just in case right-click format  and make sure we go into the properties and move   but don't sighs that's going to make sure that  they don't move okay we also need another one   for reminder I just remembered we have one for  reminders what do I need to do I want to check   the reminder so let's build that group out this  this icon of course is point two so we're going   to do the same thing with this point two point  two so what I'm going to check for reminders so   this time I'm going to duplicate addy and that's  going to keep it in the same group but of course   I still have to add this icon in this case I want  to call check reminders okay and then bring it up   here and then what I'll do is I'll bring this on  top here bring this button a little bit larger and   then what I'm going to do is I'm going to group  everything together so the best way to group is   just hold the group copy the group name ctrl C  ungroup it using the control again group whatever   else you want to add to it and then regroup it  again like this and then just paste in that name   again there now it's been added to the group and  all we need to do is bring this to the front bring   to the front all right now it's brought to the  front and then what increases button okay so now   we can check for reminders now we can set the  reminders we can save it it's gonna be really   good okay we've got all that move it a little  bit over there and then right justify it okay I   like the way that that looks now let's move on so  now we have all the groups set now we're ready to   put in our tabs now we're ready to program these  tabs it's looking really good let's zoom out a   little bit bring this up so basically what I do  is when I click this I want to display 4 through   16 when I click the maintenance and repairs and  expenses I should say I want to display this and   hide everything else and so on and so forth so how  do we do that well we do that of course in a macro   so let's get into that and show you just how we  do that into the developers and Visual Basic and   I've got some just some modules that I created  here it's called top tabs just there's empty   but that's gonna help us move things a little  bit quicker so the first thing I want to do is   I want to write a macro to hide all I think that's  really important because one macro hide everything   and then individual macros will show just what we  want so let's do that for sub hide oh and what do   I want to do the first thing I want to do with  sheet 1 I want to basically hide information now   there's gonna be added a vehicle picture ok so  basically when we add a picture I want to bring   in a picture for example let's just say we have  a vehicle picture like this one right here I want   to place that right here and size it ingly  we'll do that with a macro but basically I want   to hide that picture too I don't want that picture  to show as well so or actually what we can do is   delete it and then we can show it up every time  we camp so this is going to be called we're going   to give it the same name all the time and it's  going to be called vehicle picture so vehicle   and vba will do the naming of that automatically  so that's what we're gonna have and so we want to   make sure that that also is hidden - or at least  hidden also so let's go ahead and write that with   Chi one first thing I wanna do dot shapes the  pickle pick dot visible equals and they're so   false so we're gonna hide that picture but what  if it's not doesn't exist if the user hasn't added   it could create an error so let's wrap that in  on-air resume next and then on-air go to 0 okay   so that's going to help us automate that what else  do I want to do I want to hide the vehicle group   too so let's let's copy this and just change the  name because there's the vehicle group we're gonna   double check those names to make sure vehicle  group that's what I think it's called let's double   check that to make sure that we have the right  names bring this down a little bit click on our   vehicle group and check the name and we have here  its vehicle group that's exactly what I want also   we have different add vehicle buttons I want that  so a vehicle group we want to hide I want to hide   this button the add and the save too so let's do  that so again with shapes dot shapes add vehicle   group I want that hidden equals Emma so false  and what else we also want to save one so let's   copy this and write in save right because the  save vehicle that's another one so save vehicle   that's the one behind it right we want we want to  hide that one I want to hide everything no matter   what and then we'll display it ingly so say  vehicle we will also want that ok so we have that   so we're gonna hide those and of course these two  buttons are part of our group so we don't need to   worry about that what else do we want to hide well  I want to hide all the other groups as well what   are they well they are repairs group right repair  group I think we call this repair this group we   want to hide those shapes that's important so  again all we need to do is copy this and just   change the name to repair so ctrl C and then V  and call this repair re pair we see we can see   it up there so it's nice to have both of those  at the same time and then again same thing we   will these are kind of self-explanatory so again  fuel as well so we want to do fuel and we also   to do reminders group okay so we'll change this  to fuel and then the next one to reminder script   okay so that way we're gonna hide those groups  ingly we're gonna hide everything and then   we're gonna even hide the rose to reminders and  what else we also want the dashboard I think we   have one for dashboard and we have meter tracking  - so meter tracking we're calling that meter group   so and then dashboard so let's call it change this  to meter and then the last one to dashboard so   we can get all those hidden ingly and then  we'll show them as the user selects the individual   tabs dashboard group Falls allows the last one  the Duster is just a single icon okay so we have   everything hidden but what about the rows I want  to hide all the way from 89 to all the way up to   four I want to hide everything right eighty nine  to four so let's do that right now dot range were   already in sheet 1 dot range 4 and cool quotation  qualification and 89 what are we doing hiding the   entire row dot entire row row dot hidden equals  false okay so we're gonna hide everything that's   important alright so when we do that is gonna  hide everything that's fine that's what I want   to do but obviously we need to show one you know  when you show a single one so let's write it in   the other Macker that's gonna actually show those  are much easier so what do I want to do the first   thing I want to do of course is our first one  it's called we'll call that vehicle so let's call   sub-tab vehicle that is our first tab that we want  to display and how do we do that well again let's   make sure we first run the macro called hide all  we want to run that macro all the time and then   what we want to do is I want to displace certain  information but not only do we want to display we   need to set the tab right I need to set this to  5 this would be 6 7 so let's first set B 3 to 5   again with sheet1 we want to set B 3 dot range B  3 equal to 5 and then all we need to do is pretty   much copy and paste this macro into the others  and it'll be almost the same except for a few   minor changes ok so the first thing we want to do  then what did wonders I want to display the rows   ingly again so how do we do that dot range  what grows 4 through what was 16 I want to show   4 through 16 display that make sure those are now  hidden so 4 through and quotation colon quotation   and 16 dot entire row dot hidden equals false a  false I want to make sure that that's not hidden   right this should be true hidden equals true okay  obviously we're hiding it okay this one's false   right it's no longer hidden what next now I want  to display those groups and shapes right what we   need to say well in case if this is I'll actually  write a macro to show that but I want to display   the vehicle group and I want to display these  two buttons so we just have to copy and paste   the ones that we created here vehicle groups save  and make sure those are MSO true so paste those   in here and that's going to be C true so those  are going to be true we need to now we want to   make those visible so we just copy that paste  it in here and paste it in here okay so now we   have that visible so what else do we want to  do probably what I want to do is application   screen updating that's gonna make less flicker so  application dot screen updating equals false that   helps remove the flicker or reduce it I should  say and then at the end we'll just make sure to   set it to true application screen updating equals  true okay so let's run that now this macro I want   to assign to this first button so this one I want  to assign it to the car and I want to hold down   the ctrl and click the button behind then I'm  gonna right click assign the macro and then I'm   gonna paste it in tab vehicle so now when I click  that let's fix this name add vehicle group that's   gonna be at vehicle button right it's just a  button not a group button and the same thing   you're saved vehicle button missed bill those are  just buttons not groups and then the same thing   if you want to update that add vehicle button and  then save those just buttons okay so let's reset   that reminder I think there's no ass on that okay  and reset that okay good now we've got our name so   now when I click that it shows only that and hides  everything else alright very good let's get this   spelled right here alright so we have that now  we've got the first one working now the rest is   super simple to add so let's go ahead and add I'll  get all those tabs functioning here let's extend   this down a little bit so we can make it very nice  okay so let's move in and get those other tabs all   we need to do is really copy and paste on bring  this back over here and so what are we going to   do we just want to build out the rest of the tabs  so what are those tabs so the first one we want   to know expenses so the red next one is going to  be repairs so we just have changed that to remove   repair so we just have to change that to repairs  and then in this case it's going to be column 6 so   we're gonna change that to 6 this is going to be  17 through and believe it's going to be 29 let's   double-check that to make sure that we're on 17  so will unhide these and then we'll go down here   we want to unhide 17 through 29 so that's perfect  and then I want to make sure that the instead of   the vehicle group if this is going to be called  repair group repair recruitment want to display   that and then we don't need these buttons in fact  on this one we want to add we want to only want   to show the same when we're actually adding one so  we can keep saved vehicle false until we actually   add one alright next up what I want to do is we  got there pair group we're showing the display   let's take a look at this and make sure that  we have take a look at the group name and that   looks good so we'll just now click on here hold  down the control assign it to the macro and then   the macro is going to be the tab repairs click on  that and now click on that and it looks very good   we just need to update the conditional formatting  here in fact what I want to do is I want to assign   in this conditional formatting so what I'm gonna  right click format the cells and hide the reset   this up and down and just like that it's gonna  work perfectly you see how we reset that lower   border all I did was right click format the cells  it would look like it was on turning on and off   and that's all we have to do and that says the  border just right so now we have cars now we   have repairs and expenses now all we need to do is  add the fuel very very simple here so all we need   to do again copy and paste most of it and just  make the ing the updates so paste it down   here we'll call that sub-tab fuel this is going to  change from 7 this is going to change from 30 to   40 to 30 to 42 and then of course this is going to  change to fuel group that's it so all we have to   do so once we add in all it's really really simple  once we start adding in the first one and then   it's just copy and paste so then again holding  down the control selecting on both the back and   the front icon assigning the macro fuel click OK  and then click the fuel and now we have the fuel   purchases alright the next thing we want to do  for reminders same thing all we need to do again   copy this reminders is a little bit larger of it  right so it's not 13 we're gonna change this to   8 change the macro name to tab reminders and then  of course the button gonna be changed to reminder   and let's just double check on how many cells how  many rows that is reminder group how many do we   want let's unhide our rows clicking unhide cleaned  down now we've got in this case we're going to be   unhiding from 43 to 60 so let's just change that  from 43 to 60 and then we're gonna run that let's   just take a look again the sign that Macker here  clicking on that again not that one clicking on   that right click sign in macro and then that's  going to be reminders okay let's click on that   looking good also check for money as I updated  that button just wanted to make sure we and now   it's checked for reminders in the icon there I  just changed that out okay so that looks good   that's just what I want to see for that perfect  what next little cube we just have two more to   do and then we'll round up this specific header  tab reminders again what we're going to do copy   and paste that change this to nine in this case  right we have seven eight nine and this time we're   calling this meter meter and then of course meter  group and of course we're updating the rows we're   going to change that this one's going to be 61  through 73 61 through 73 we can write the next   one too while we're at it the last one is going  to be the dashboard so let's write that up copy   that again paste that we're gonna call this tab  meter dashboard now and take a look this one's   going to be 10 and then we're going to update  that one to dashboard group perfect now we can   assign those last two so let's take a look at that  right click format the cells sign the macro tab it   over we're calling this meter click OK and then  the last one dashboard right click format sign   in back row and then dashboard okay now we have  our dashboard that looks good we have our meter   tracking that looks good reminders that looks good  everything's showing up don't think I don't like   sometimes as I like see how the cursors here I  want to select like when I've clicked this I'm   going to select this tab when I select this I want  to select inside the vehicle so let's do that now   let's add the selection right inside so the first  one would be f6 onto that so all we need to do is   go up here and then just say dot range f6 and then  I'd select onto that dot select okay so I'm gonna   add the same thing but I'm just gonna change  the range on each one of them so and then this   one what would it what would it be for this one  I'm gonna make this one f19 so let's update that   to F 19 for the repairs and then on the fuel we  also want the same thing but different cells so   let's take a look at that that way it selects the  first one and it's kind of a nice thing this one   of course would be f32 so we'll change that to F  32 and I believe this is going to be a two-part   training series because we're going really long  on this this one's going to be F 45 F 45 on the   remin