Building Construction Bill Format in Excel for Financial Services Made Simple

Building construction bill format in excel for Financial Services

hello this is randy with excel for  freelancers and welcome to the construction   estimator in this week's training  i'm going to show you how to create   incredible construction or any type of  estimates with a very unique tab feature   i've never taught before we're also going to  be able to add clients edit clients on the fly   add or edit items on the fly and we're going  to have a unique mini dashboard along with   printing emailing and a whole lot more i hope  you'll stick with us so let's get started thanks so much for joining me i've got a really  fantastic training with a brand new unique   feature i have never taught before certainly we  have taught tabs before but never in this way   quickly easily simply i'm going to show you  how we do that and a whole lot more through   this incredible construction estimator this  template is absolutely free all i ask is for   your name and email and i'll go ahead and get that  sent right over to you through email or through   facebook messenger i create these trainings each  and every week or and soon to be every other week   but we'll certainly make sure to bring you amazing  content always and the best way to do that is to   subscribe to our channel that way you get notified  when you click on that notification icon bell i   certainly do appreciate it there are many ways to  support this channel this content training is free   however if you do want to support us a great way  to do that is through our patreon platform because   on our patreon platform i bring you even more in  fact additional training for example last week   or we had the shopping manager i think it was two  weeks ago shopping manager where we created this   really cool drag and drop shopping manager well  based on your request i added even more on our   patreon platform we now have the ability to add  different stores so we can create multiple stores   multiple different aisle configurations and with  the admin screen we now have can add different   aisles for different stores we've got we can  assign different categories on different stores   so it's really an incredible feature thank you for  your suggestions on that that is available this   workbook template along with the training that  coincides with the adding of stores on our patreon   platform so i hope you'll get that the links down  in the description excel for free and that's just   patreon is where it's all at all right so let's  get on this training because i've got a lot to   share with you so we'll go over overview and  then we'll get into the details of exactly how   i created this every step every line of code every  function feature and conditional formatting i will   bring to you so grab your beverage of choice i  will be creating this every step of the way as   we as i've created it and i'm going to go over  every detail with you all right so what we have   here is it's a relatively basic application in  the sense that we have an admin screen where we go   over some defaults we've got item categories now  what i like about this is these items categories   are dynamic so that means as the users add  these categories here we those tabs are then   automatically customized here ingly so if  we were to change something if we wanted to add an   additional one or we wanted to change this maybe  to an additional type of expense maybe you had   field labor you wanted to separate out your  field labor and your local or office label   maybe add some office let's do office maybe you  have office expenses associated with a project   as you see there we now have office here so office  is automatically updated inside the tabs and we'll   go ahead and bring that back to where it was which  was commission and but you can see how it's very   versatile in the fact that we can do that creative  so when we add that and automatically update it   notice that that tab automatically gets updated  and of course when we click on that we notice   that all the commissions are added per job one of  the great features of this is a drop down list and   it's a dynamic drop down list so we here have  here a set of items right these items are for   any type of item you want however when we click  on the equipment tab we see that it is equipment   right now the great feature of this is that these  are actually the same cells we're not changing   any columns we're not changing any rows in the  past our tab features has have hidden columns or   hidden rows but in this case we're not doing that  so actually i'm going to show you a trick it's   quicker it's easier and it is really beneficial  with a few challenges that we're easily able to   overcome so each one of these are the same cells  notice e9 right or let's say e13 in this case e13   we have a drop down list available of weekly daily  service right we also can auto complete that if we   have that so notice uh we can do weekly service  or anything like that just typing in the w even   though even if it's not here for example let's say  we have a brand new job and we go on to let's see   equipment right or let's go ahead and save this  job first we'll add a customer here a project name   here and then what we'll do is we'll just save  that now when we go into the equipment notice that   there's nothing here we've got information all we  need to do is to start typing it and automatically   comes up that's auto populate i'm going to show  you how to create that however in the same column   notice the same column e9 notice the same column  e9 right so the same exact cell here we have a   different list here if we type in bathroom it's  going to come up right so notice that came up   b-a-t-h let's bring that up again b-a-t-h there it  is bathroom vanity okay so notice we have multiple   multiple items on bathroom so that's going to  take a little while to come up notice we have that   so also what i want to do is also want to have  the ability to that autocomplete and a dynamic   drop down list so this particular drop down list  is one way when we depending on it's dependent   on the tab so when we click on the service tab  that drop down list is monthly weekly or daily   services if i click on labor that drop down list  in the same cell is hourly or supervised labor so   it's a dynamic based on the tavern which is  really helpful it takes large projects like a   construction job or construction and it breaks  it down into the project details and the types   based on a category very very important here and  of course we have the navigation we can print it   which is going to print it now we want to print  it we want all the items associated with that we   want all the items the equipment the travel and  commission we want that all separated very very   clearly whether we're printing it or emailing so  i'm going to show you how to do that it combines   all of the items on the particular estimate and  combines them all in a very organized manner and   of course we can create a pdf if we want to create  a pdf of that just by clicking pdf that's going   to create a very nice organized a very beautiful  project estimate and we'll show you how to do that   and of course printing and emailing it's going  to do the same thing basically when we create   an email it's going to create some default subject  and of course we have that same pdf here but it's   now been attached in an email format so we're  going to go over all of that i'm going to show   you every step of the way so make sure you watch  the entire training here all right don't forget   to comment below like it and subscribe right i  respond to every single comment okay so we've got   a set of item categories now these item categories  are based on you know whatever the user entered it   is those item categories that are visible in the  projects it are those item categories that are   also located here inside our item database so it's  the same ones it is those same item categories   that when we want to create add or edit existing  if we click here we see that we're editing the   existing right notice we have the item category  up here if we want to create a brand new one we   select on a new row and then we also have then  available to have a new one notice that the   category name items is default however if we're  on equipment and we decide to create a brand new   one here we click on here we see that equipment  is set to the default of course the user can   change that but it's nice to have that category  set as the default based on the selected tab   notice the selected tab will change the look  of it and there's no items on there however   so it's really really some cool really features in  here and so continuing on with the admin screen we   have a status right each of our projects can have  a status that status is available in a drop-down   list right here located in endline so we can  have all those statuses so we know the status   and then we have a per we have also in the admin  screen we have are some project defaults what is   the required profit margin perhaps i'm going to  do something with this inside our patreon platform   maybe i'll do an additional dashboard when we hit  or but i put this in temporarily because i want   to know when we're at or above a certain profit  margin when we reach that our profit margin can   be displayed here inside here our profit is 36  percent and that is because we're able to track   both the costs and the price of the job the total  cost of the job and the total price so i may want   to know when we hit a certain profit margin on an  estimate so we've got that here i want to know if   we're going to be including sales tax and if we  are what is the name of that sales tax and what   is the rate of that sales tax that is available  when we print it we will see that that sales tax   becomes obvious right here in the footer of that  we have a dynamic floating footer i'm going to   show you so that sales tax five percent is going  to be displayed in that footer we'll be getting to   that of course all of that and also continuing  on with the admin defaults we also want to   know if we're going to be including the date and  printout sometimes we don't want to use the date   so if we change this to a no we do not want to use  the date sometimes we use the date what i mean by   date i mean this date column right here right  when we print it out maybe we don't want to use   the date column also if we're going to be sending  it to our customers we certainly don't want the   cost associated with that inside the printout so  if we decide to turn this off no that cost is not   going to be in that printout i also want to know  the default project status when i create a brand   new project what's the default status writing a  bid so you can create your any status you want   and then you can have that status available here  and basically all that means is when we create a   brand new project what do we want to locate it  right here writing the bit all right and also   what is the default footer message that default  footer message is right here so we have as many   footer messages as we want the name and then the  message itself here and what that means is when we   print that out we want to know what is the default  message and that default message is right here   notice in the footer it says this estimate is  good for 30 days of the estimate so that can   be changed on one the printed and emailed out  it'll be visible at that point so we have those   defaults there and the footer that's it for the  admin screen relatively simple i've got a really   cool background that i'm using on both the admin  and the project and that background is basically   a picture that we can have when we change the  page layout so if i click on the page layout here   and i delete the background you'll see and if  i decide to add it in i'm going to put that   picture in i'll work offline i don't know why  that message comes up in excel kind of annoying   but i have that available for you and of course  i'll make that available on the patreon along with   all the other icons and everything else here i  have that available right here inside our patreon   platform for those of you who want to have your  additional so we got a construction estimator and   i have this background image right here and that's  the one i'll be using okay so that's going to be   kind of helpful all right so we see that and we've  gone over the admin screen we've got a project   databases where all our project information is  stored we have a project id we have the date of   that project i've got the status i want to know  the status of that project the client id the   customer and the client name the project name the  supervisor sales representative the footer message   used the total cost sales price and the estimated  profit so we're going to have all of that in there   and we're also going to be using data mapping  to locate that basically these data mapping is   associated if you haven't seen my trainings before  data mapping helps us reduce the code to just a   few lines when we associate specific columns  with specific cells meaning the date column   is associated with n3 right here on the project  so n3 and each one of those is associated with   that okay so that's how we map these column  data to the specific cells located right here   all right so we've also got an items database item  id the category which is really important because   we're going to need to know where to place  that we've got the item name the description   the unit type the item cost and the sales price  so that means when we make changes in here or when   we make changes on the pop-up it's going to pull  the information so if i want to edit that bathroom   vanity and i want to look here and i see that  items here and i want to add that in here but i   want to make a change to that item maybe i want to  change the cost of it i can just simply click here   and it's going to edit it maybe that item cost has  gone down to 199 so i can go ahead and update that   and i can update that right here when i save that  it is that information that's going to be placed   up here and it's automatically going to be updated  here if i decide i'm going to change the quantity   it's automatically going to be updated so when i  switch tabs here and go to equipment and then back   to items we want to make sure that that loads and  it's automatically saved automatically okay great   so that's going to come our original data is going  to come from the items then we have the project   items now we notice that we have inside this  project number four we have several items we have   equipment we don't have any service but if we want  to put service monthly service or we want some   kind of weekly service we can do that and also  the labor shipping and all the information that's   associated with that but we need to keep track  of them in a database right where is it located   so it's located right here and that means all of  these items are associated with project number one   if we take a look at project number one right we  have several items we have a service labor shop   shipping travel so all these are associated with  project id 1. if i want to locate that i just   type in 1 here it's going to load that  project id we see we've got several items   we've got for equipment we've got some service  items labor items we've got shipping items travel   so this takes away a lot of the confusion when it  comes to estimating especially when we have large   estimates we want to break down those estimates  into you know anywhere from five or six or seven   or eight different categories we can do that  very easily with this now you could easily add   this to any type of a project right project  estimate or project jobs or project scheduling   could easily be added we've created those in the  past okay but we need to store those and this is   where they're stored right so we want to know  the category i want to know the item id i want   to know the date when was it added in if there's  a date column the item name the description the   quantity remember the date here is associated with  this specific date right here so that's important   and i also want to know the unit right how many  units is it a pair each the item cost the total   cost the item price the total price the project  rows is associated and that means row 9 10 or 11   is associated with this row here 9 10 or 11. so  that means when i save that information i want   to make sure that it comes back into the exact  same row as saved one before so it is important   to save the row that's associated with that and i  also want to know the database row what is the row   associated with this if this is row 4 row 5 row  6 i want to make sure to say that we can do that   with the formula row okay great and then of course  lastly in our database we have a list of clients   client id name address city state zip phone and  email relatively simple with the clients when i   add or update a new client i want to make sure  that it gets sorted in alphabetical order so if   i go back to projects and i click on let's say  we want a new job and we we put in let's say   john smith and he's no longer in the list i  want to be able to add him very very easily   simply it says here a pop-up john smith is not  currently a client would you like to add this   client yes it's going to pop up let us know  we can put an address for that client whoops   one two three four five main street okay and  then we have la california and then just put   in a zip code i want that automatically to add  in automatically and then we can do that john   at gmail so simply click and save it's  automatically going to load that information   and if i decide i want to edit that it's going to  edit we're going to pull that up information if i   want to make a change very very simple and  i want to save that i want to make sure all   that gets saved to a database and that's going to  get saved down here so we have that new client id   that's automatically added we've got the street  and all the information that's added right here   and we can pull it up again very very easily  insert and i want to make sure that that name   gets sorted in alphabetical list so notice that  name is brand new name is here and it is sorted   ingly alphabetically so i'm going to show  you how to do that as well and of course we're   going to be able to add this really cool dashboard  where we've got a donut chart and a pie chart   we want to know the prices per category and that's  going to be based on the project that was selected   so we're going to get we got it's a very very  it's a kind of a tight application right just   this one feature but it's really so many cool  features features built into it the only way   you know how many features if you stick with us  so i know my trainings tend to be longer but i   guarantee they're going to be packed with great  content great learning new features that you can   apply in your own applications today okay if you  do want to learn how to build these applications   by yourself and of course sell them for passive  income check out our mentorship program in fact   i've got a brand new mentorship express which is  all the same content of my mentorship but it's   packed into five months where you get six hours  a week of training and it's a brand new low price   i'll include that link down below okay great so  what we're going to be doing here the first part   is how do we create this really cool dynamic tab  feature meaning how do we get these tabs created   automatically if we want to add a new one right if  i want to add a new one i want that automatically   to come in and automatically notice now that that  test is right here of course there's no items   under that but we could easily create that and  how do we want to do that if i decide i want to   create a brand new item for that test i could do  that and it's automatically going to be here very   very cool right very very dynamic very easily user  friendly if i delete that here i want the projects   tabs to be automatically updated here notice the  test is gone so how do we do that very very cool   okay so the first thing what we want to do is we  want to be able to create these tabs across it now   all these tabs have actually already been created  if we look into the selection pane there's maximum   of eight because we have a space constraint so if  we take a look let's say i think it's right about   here at an item button right about here group 41  i think this is that i should have updated okay   so if we take a look at this one right here see  this tab right here this tab has already been   created it's called category eight right so we  have maximum of eight categories maximum eight   categories however there's nothing on the eighth  one so that of course has been hidden it's been   hidden right here right so all we need to do is  basically show and hide these tabs ingly   and of course i'm going to show you how we get  the contents of those tabs a really really cool   trick how we get that the totals of those tabs  so how do we do that first let's go into macro   and see exactly how we created the position of  this won't matter because it is vba that takes   care of that position automatically so when we  add it notice that that test is automatically in   the right spot here so let's take a look at the  macro that gets created now that macro it starts   out when we make a change anywhere from c7 to c14  so we're going to go into vba and see exactly how   changing those cells can automatically create and  update those tabs ingly okay well that's of   course going to come inside our vba if you want to  get to the vba just click on the developers or alt   f11 as a shortcut that will get you there and  we've got several modules here and we've got a   client module an item module that's going to  take cover items project which is focused on   the project saving updating deleting the project  and the project sheet macros which is some of the   ones on the sheet that's what we're going to start  out so in the admin screen here just a few lines   of code when we make a worksheet change right when  we're making a change from c7 through c14 that's   where our categories are located we're going to  write a macro called category tab updates category   tab updates that's the macro that runs it's going  to automatically update those tabs it's located in   the module called project sheet macros and here  it is right here it is that first macro okay   so the first thing what i want to do is i  want to determine how many we have right now   i've got a named range i want to know how many  categories we have if we look in the form list   we've got a name manager we're using of course our  categories it's called categories right here that   first one here and we're using a dynamic named  range to create that using the offset formula   so we've got seven items in our categories  really what i really want to do is i want   to determine how many are there right how many  items are in that so we need to loop through all   those categories and create those tabs or display  those tabs and position them ingly okay so   what we have here is that particular name range  now with that name range what i want to do is   determine how many are in there so we're going to  create a long variable called category quantity   and what we're going to do is we're going to use  the application worksheet function count a count   a of course is the same as we would be in the  formula and i want to determine inside this named   range from the admin how many items are in that  and this is going to be seven right so now that   we know how many what we want to do is we want  to determine the width of it now if i look in the   projects right i want to know the width of each of  these tabs i want to make sure that they all have   the same width it's nice but i want to stretch  it automatically on these columns from column d   all the way through column l i want to make sure  that however many or however little they they come   across this and they're all equal sizes so what  i want to do is determine the width of d through   l all the columns that way if we increase the  columns or shrink the columns they're going to   go along with it i want to know how many of those  categories should fit on here and divide that and   determine the quant the width for each particular  tab so we can do that through just a little bit of   math that width of each of that shape is simply  going to be the width of those columns from d to   l and we're going to divide that by the number of  categories so for example if the width was 70 and   we had particular seven quantities we'd know we'd  have a width of 10 okay so a simple division to do   that i want to set that initial left position and  the initial tom position now remember all of these   have been created already it's called category  1 category 2 category three right so they're all   named already they're all they're either visible  or they're hidden but they're all on the sheet all   eight tabs are already on the sheet so to do that  what i want to do is i want to set that initial   left position we're going to set that initial left  position as column d and the top position based   on actually row seven so row seven is going to be  that so that's what we're going to set set that a   left is just based on column d7 or column d any  row would be fine okay so once we have that top   position and the initial left position we can  then loop through all of the shapes and make   sure they're positioned right so to do that we're  going to loop we're going to create another long   variable for the category number equals 1 2 8.  now we're going to go through all of them we need   to know whether we're displaying them or hiding  them and of course where they're positioned so if   the category number equals less than the category  quantity meaning we have eight let's say we have   our category quantity seven but we get to number  eight if we get to number eight we're gonna do   something else right we're gonna hide that  category otherwise we're going to display it   now to display it we would do the following so  we already have all eight shapes already so with   the category and category number meaning anywhere  from one through eight we're going to set the left   position to the left position the top position to  the top the category width that unique width that   we've already calculated and we want to make  sure it's visible what good is we're going to   update that left position i want to move it over  to the right that left position but how much   well it's current position plus we're going to  move it the width of that shape right the exact   width of that shape is going to move it over  to the right the exact amount that we want to   otherwise hide the category right otherwise  meaning otherwise let's say we're on number   eight we know that there's not eight categories so  we want to hide that eighth one so let's just put   those hidden categories hidden or non-existing  hidden for non-existing categories non-existing   okay all right so that's how all we have to do  to actually create those relatively simple but   now what i want to do is you notice that there's  no text in here we're not placing the text   that text has already been placed right and  notice the text is dynamic right so they're   linked to a particular cell because one i want  the text to include the item category which is   dynamic based on whatever the user enters here  and two i want to know the totals of that tab of   that particular tab and as we increase them i want  them to be also increased so this number items 46   17 is going to change when the user changes that  automatically as soon as they make the updates   now here's the tricky part and a great challenge  and of course we're going to show you exactly   how to do it here's the tricky part right as  we move to the tab this notice the data in   this all changes right so how do we get this  to save and notice that these are the same   cells right but what i want to make sure to do is  as soon as i move over we're going to save this   data all this information it's going to get saved  in this database right here it's going to see it   saved all the way to the end right so how do i  know and make sure that if we're on a specific   tab it is that data that's automatically getting  updated so this data here the the totals here   is coming from our database meaning it's coming  from here so we're going to get into that in   a little bit as far as how we create it but we  want to make sure that we're totally these totals   on the current tab are updating as we make changes  or as we notice that the tab the total in this   tab is automatically changing as we add items  automatically here so we can see that as we add   here a different item it's automatically going to  get updated very very easily okay so how do we do   that well of course we got to link it to a cell if  we take a look on here clicking here we see this   is linked to cell b17 b18 and so on and so forth  so let's take a look at those admin calls columns   a and b and see how we've linked them so the first  thing what we want to do is link this particular   to of course our admin we could just as easily  link it directly to here so we've linked this   i can just easily probably easier just to do  this equals admin whatever is located right   here we don't need an indirect formula for that  something for another reason so here's what it   is so i've got this basically these are linked  to exactly just to make it a little bit easier   link them directly to the categories in our admin  screen next up we have b17 all the way through b24   this is the cell that's linked directly to the tab  here and what i want to do is basically i want to   create a total and the total is going to be for  everything that we have for this category right   so i've created some name ranges going to help us  with that total and i'm going to show you how that   works so let's go over a few of those named ranges  because what we really want to do is we want to   total those totals based on what's located in this  database the project item database so if we go   into the formulas and then name manager we'll take  a look at some of those formulas now the project   items we've got one called project items category  it's going to be associated with the category   because we're only summing totals based on a  certain category that's very important and also   it's going to be so we're going to use this offset  to do that and of course we also want to know   for a specific project id it's got to be for the  selected project id and not for all the projects   we also want to know the total cost that's  going to be help help us out for some of our   graphs and we also have the project the  total price which is we're going to be   using so the total price so i've got these  four particular named ranges that are going   to help us in those formulas so the basic  idea was i want to sum all the total price   four items for project one right and i want  that total to appear directly inside here   now that's all good and dandy right so it's  very easy just using a sum if some if based on   this project id project two based on this category  the selected category now that's selected category   which we haven't gone over yet is going to appear  directly into b5 whatever category we select   b5 is going to take on that category so this is  the category so so basically what i want to do is   i want to sum all the categories and then what i  want to do is make sure however there's a little   trick so we're going to base it based on what's on  the database right but what if i do this what if   we're summing all the items based on the database  that's been saved what if i add a new one here   if i add a new item here we haven't saved it to  the database until i click save as soon as i click   save it's saved so how can i if the if all those  totals are based on the database how can i get   this up here we say certainly we'll just update  this just total this that's fine that's great but   what about when we switch to this tab right how  do i make sure this is updated so the idea is this   the selected tab whatever selected tab is  we're going to use this this total here this   column here l however for every other tab we're  going to use what's in the database as soon as   we switch tabs it'll save the database i'll  so i'll show you that but so that's the idea   whatever selected tab we are on it's going to  add in this total here is going to be based on   what is located in this column everything else is  going to be located what's in the database okay   all right so that's how we're that that's the  main idea behind this particular formula here   so let's take a look at this formula okay first  of all i want a17 a17 is of course that particular   item category i want that item category as the  first part right because i want to show that item   and then next up i want to space so we're going to  put a space in there and next up comes the total   and basically the selected tab we're using column  l if it's not the selected tab we're going to base   it on the database so here it is right here if  b5 equals a17 b5 meaning the selected category   equals a17 we are then going to add up whatever  is in column l so we're going to use the sum   we're using and of course we've wrapped it around  text i want to round up i don't want i don't want   i don't have a lot of room in these tabs so we  don't want the decimals right so the reason i want   to get rid of the decimal points i'm going to use  round up and i'm going to add that into the sum   so basically i'm going to sum everything that is  in column l and i'm going to round it up i don't   want any decimals so i'm going to show this zero  and i want it in this format okay so i want the   currency symbol so what that's going to do is add  up everything in the tab but only if b5 equals a17   right so in this case we're on equipment so in  this case right here it is b5 equals a18 equipment   that's the tab that we're on same formula here l  however however if it is not the selected tab i   want to pull the sum from the database and we're  going to use the sum ifs for that we're still   going to use roundup but this time we're going to  use sum ifs i want to know the project total price   that's what we're going to be summing in from the  database and i want to know the project based on   this project id located in n2 n2 is our project  id number two and i also want to know based on   the project item based on the category of a18  right so i want to know all of the items for that   specific project for the equipment and i want to  total that up so that way we're going to get the   total regardless so that means the selected  tab is always going to be dynamic based on   the items we add so the more we add the more this  number goes up right however as soon as we switch   it has already been saved that 815's already been  saved into the database because switching these   tabs is running the macro which i'll go over to in  just a moment that'll be the next macro we go over   it's automatically going to save these so i just  added daily and weekly service if we look in the   project item we scroll all the way down we see  those items daily weekly service right here   those two items just got added for project two two  items service right 20 those are the item numbers   so we have all this information saved in the  database so that formula the sumifs formula   now is going to total up the service and it's  going to add in these two items so it saved it so   it's really really cool because it automatically  saves it as soon as we switch tabs now switching   tabs if you didn't know already as soon as we  switch tabs and we're going to go into this macro   it's going to load up only those items  from that project or it's going to load   only those equipments from the product only those  services that's going to load up into this section   so it's very very simple we're just going to use  an advanced filter to do that okay but right now   so i wanted you to understand how we got to  this formula and guess basically to sum it up   that selected tab right that selected tab we're  going to use this sum every other tab otherwise   we're going to sum it from the database okay so as  long as we've linked these to the specific cells   that's how we get this dynamic content on these  tab headers and we can out get a sum of all those   very very easily okay great i'm so i'm glad  i got to go over that with you now let's move   on to the next one which is that selected tabs  now when i select the tabs what do i want to do   basically what i want to do is i want to clear  i want to make sure to one save any changes that   the user is making we're going to run this macro  and basically it's this macro right here which   is going to save that project the only difference  is i don't want this fade out message to appear i   only want this faded message to appear when user  clicks save although everything else is the same   so when they click this the first thing that's  going to happen is they're going to save it   the second thing that's going to happen is i  want to color these differently now we can't use   conditional formatting because these are not cells  these are shapes all right and i also want to make   sure this is bold and i want to switch the colors  notice that it goes from light blue to medium blue   however normally it's medium blue to white okay  so i want to change that and we want to change   that to bold and what i also want to do is i want  to determine the selected tab category which is   category here items and i want that to be used as  a criteria so that we can take all these project   items and we only want to load those items that  are associated one with this project and two with   this category okay and we want only those items  to appear as results and then we're going to bring   only those items and bring it directly inside  the projects right here okay so let's go through   that macro now and that's the next macro called  category select that is the macro that's been   already assigned to these individual tabs these  shapes here already have been assigned this one   called tab select so inside this macro what we're  going to do is first of all what i want to know   is i want to make sure that they've saved this  project right it's very important if i'm going   to be switching tabs i need to make sure to  save it and of course i want to make sure that   the project got saved and that means when we  click on a new project if i try to switch tabs   i want the user to say hey please save this  project before switching tabs right so all they   need to do is put in a customer click save and  it'll automatically be saved so that's important   and how do we know that well we would know that  if b2 and let's take a look at some of these   i've got a project load i want to know when we're  loading the project now this is important because   in one instance vba is going to make some changes  here as we load in those tabs vba is going to make   a change in another instance user is going to  make a change i want to differentiate between   those two types of changes this one b1 is going  to help me do that right when the vba is making   those changes loading it in this is going to go  to true and back to false it happens very quick   i also want to know that the project row what is  the row that's associated with this project i've   got a name branch called here a named range called  project id and i've got a project id located here   too so i want to know what row is associated with  project id 2. if we look in the project database   we see that project id 2 is located on row 5. so  i'm going to use a named range and we're going to   use a match to do that so we can just use this  matching based on n2 the project id we're going   to add 3 because we want to extract the row number  so that's going to get us i also want to know the   next project id we're going to use the max formula  of project making sure they're all numerical   and we're gonna add one because we want the  next one if there's no data or there's an   error we're just gonna revert it to one i wanna  do the selected item row i've got conditional   formatting here a few different rules that  selected row is going to change here as we   select something else so if we take a look at the  conditional formatting we've got three different   rules we're going to manage the rules and we're  going to use one for the select row based on b4   just highlighting it in black in black bold  font and then of course the blue background   then i've got one particular conditional  formatting very slightly white color very slightly   blue color it's very very slight for the odd rows  this will work and for the even rows this will   work notice that e9 right e9 is consistent that is  our starting row and it's also the row that we're   going to be using for the applies to notice that  starts and applies to d9 through l200 very good   so we've got that up there that conditional  formatting that's going to be triggered by   change in this so the selection change event which  we'll get into will put whatever the rows that's   been selected here we've got an item category  here now this and we've got the item tab here   now when we select an item tab it's like here this  is going to change to 2. the the macro that we're   just about to go over is going to change this  to 2 automatically and then we have equipment so   what i want to do is i want to take this 2 and i  want to pull equipment from it how are we going to   do that well we can do it through index now  we notice that we have a named range called   categories right that's the same named range from  the admin screen it's this one right here right   so if i if i know that categories is here and  i want to pull the second item from categories   because it's a 2 i can just simply use index for  that and we're going to extract that equipment   so using that formula index of the categories  based on the based on the number of b6 is going   to extract that categories so all we need to do  with our macro is simply do this get rid of the   word category remove it in our macro determine the  name of the shape that called it using application   color remove the word category and what's that  going to leave us it's going to leave us with   the one the two or the three i'm going to place  that number directly inside b6 so let's go and   go through that right now so certainly we want to  make sure that b2 contains a value if it's not let   the user know to save that project we're going to  turn off application screen updating temporarily   and we're going to then again i'm going to run  the macro that's going to save or update that i   want to make sure that we are saving that we'll  get into this macro in just a bit this is the   macro that saves the project what it will do is it  will save any new changes to new items in this row   automatically as i switch the tab and that way  it's saved to the database already so when i   come back we can easily load in those items here  they're remembered because they're saved inside   the database so this macro is going to take care  of that right before we do anything else then what   i want to do is i want to determine the category  number remember that category number is based on   the shape name here is the name of the shape  if we try to run this macro through this often   we're going to get an error why is that because  there's no shape that called it i called it from   right here right so make sure that when you use  application call you have to run it from a shape   that called it in so what i want to do is i want  to set that category number and as i said before   if i remove the word category and i replace it  with nothing using the replace command it's going   to leave us with that category number i'm going to  put that category number into the variable and i'm   going to take that i'm going to put that directly  into b6 that is the category number so now that i   know the category number i can automatically here  set that item so we know that's very important   because i want that category number because i need  to extract the name right from it that name is   going to help us in these formulas that was very  important we need to know the selected tab b5 is   going to let us know the name of that selected  category on that selected tab okay now what i   want to do is i want to reset all caps now what  we want to do reset all tabs is i want to set that   selected tab i want to change those colors from  blue to white from white to blue and i want to put   it to bold so the first thing what we want to do  is reset all of them back to their original color   which is this light blue to white and non-bold so  that's what we're going to do so for the category   number one to eight we have all those shapes  we're going to set that fill that filled to that   blue down to white and that's can be done right  here category category number the fill we want   to color gradient and to get this to get this  all i did was just simply run a macro while i   changed the color and it came up with this and a  little bit of other code i just extracted that out   so it just turned out to be gradient horizontal  two horizontal number two so that's pretty much it   and then the reverse was just one so relatively  easy and i want the bolt equal false right we're   taking every single shape and we're making the  bolt false right we don't want it bold and we want   to set back to that original color that original  color is this light blue on top and then white on   the bottom however for the selected one we want to  do something different how do we know the selected   one because i've got that into a variable called  selected category the number here if i know that   right the shapes the category and the selected  category this is the one we've selected this   one we're going to change up that's still the two  color gradient but the number is going to change   to one this particular one is simply changing  the horizontal position from top to bottom and   bottom to top we don't really need to change the  colors it's the same colors i'm just reversing   the colors from top to bottom and bottom to top  so this one will do just the opposite right it's   going to take that go from white to light blue  and it is that light same light blue that we're   using to color this right so if we take a look at  that format that shape we see that we've got that   fill here is basically just the two colors from  the white or actually it's quite light blue   to this light blue so it's just those two colors  so all i'm doing inside vba is simply basically   just reversing the direction using these let's  move that over here so i'm just reversing it   let's move that over here i'm just reversing it  inside vba right here i'm reversing this thing so   one of them is type one here and one is type two  so that's all we're doing is just like reversing   these two down and up down and up but we're doing  it in vba so it's relatively simple in that manner   that's why we only have to change one to  two and two one so this is the selected   category from white to blue and then also  we're going to take that font and make it   bold so that's the only difference relatively  easy to change those tabs right so very easy we   don't need multiple shapes we don't need to  hide and show shapes we just need to change   the direction since we're using the same color  and that's going to set that selected tab apart   from the other so it gives it that tab so that the  color is blended if you're going to be doing this   just make sure that the blue on the board below is  enough and want to make sure that the height and   now another good thing here's another thing that's  kind of tricky that you wouldn't necessarily know   really zoom into that now we don't want borders  the reason i don't want borders on these is   because to take a look there's no border on this  shape and i don't want a border because i don't   want a separation so all i've done is just lowered  this shape below it but you said there's no border   but how do i get this border that notice that  that does look like there's a border on the left   in the upper well that's actually a shadow right  so when i format that shape all i've done is given   it a specific shadow on that and that's going to  help out a slot so if we take a look in the shadow   i've given it a little shadow and we're using this  shadow one right here using the upper left shadow   here but upper left i think it's this one here  sorry it's a little bit off the screen so we're   giving that a shadow let's move that over just so  you know which shadow i use i'm going to bring it   over a little bit here so we can see it so it's  this one this one right here now it's on the   screen so it's this one right here so the upper  upper left shadow is what i've given it and we're   giving it just simply one point and one point so  it's very very small but it is the shadow that   creates that border effect and that way we don't  need to put an actual border because in excel we   we can control top left right and bottom border so  we can only control border or no border but we can   use a shadow so that's where we get this effect  so it gives us the tab effect otherwise we'd have   no borders at all so that's kind of a nice effect  without having to use borders okay i'll place that   down there but vba would be able to take care  of that for us we just want to make sure that   it's placed down low enough so that it blends the  colors it gives us a nice look and so that's all   we have to do so again it's the shadow we're using  not a border very very helpful to give us that tab   effect without having to use borders because we  don't want borders separating it we want that   blend to show it okay great so that's how i gave  it inside the vba that's how it's able to give it   that particular colors okay now what i want to  do is i want to set that selected item list it's   very very important that we know the list and  why is that important let's take a look here   we have a date we have a defined list inside here  notice that in this list we see that we have tile   saw rental we only have those equipment however  when we click on items same cell here right but   different list same cell different list right  how do we do that we can do that with a dynamic   named range if we take home the data and we go  into the data validation here we see it's called   item selected tab we have a named range called  item selected tab and that's going to come   directly from our items list so what i want to do  is i want to create a dynamic named range called   item list and it's based on the items and so that  means that whatever we've selected i want to know   all the unique items for this service so if i  click on projects and i click on items and we go   back into this items list notice that we have a  different list same named range data right same   exact name range it's called the selected  item list let's look at that one more time   right name manager and then we have the item  selected tab here same name we're using the   offset items l2 and then one row down count a  so here is a named range based on the results   of an advanced filter now what i want to do is  i want to take all of our items i want to create   i want to know only those unique items based on  the items and i want to have those appear here   notice that the selected category is here if i  change it right again we could simply do index   categories b6 actually i can just do just i  think it's just a little bit easier to equals   right let's do equals because we always know  that selected tab is going to be inside here   and right up here we don't need to based on the  number because i've got it right here in b5 right   so we know in b5 as b5 changes right we know that  item will change here inside it's going to change   the equipment right so if i know the equipment  that we tab and i want to know all the items   associated i want those results to appear right  here we can do that in vba with an advanced filter   we're going to determine the last row we've got  our criteria the category and we want the unique   items for that category it is this unique items  that make up our drop down list so we don't need   to change the data validation on that because the  contents of that dynamic named range will change   automatically based on the results of this  advanced filter so it's that advanced filter   that we're going to run right now inside to create  that selected item list determining the last row   based on the item sheet now if it's less than four  we're going to exit the set and then all we do is   create an advanced filter we have a criteria our  advanced filter is going to go from a3 through g   right here through g our criteria is j2 through  3 and our results are going to come into l2   and that's just what we've done here j2 through  j3 and then l2 that's where our results are coming   that this few lines of code creates a very very  cool dynamic name range so that way when the users   are in the equipment tab they only have a choice  of those items that are equipment if they're in   the shipping they only have the choice of those  shipping items it makes things a lot easier and   a lot more organized when you create these  detailed projects and we can do that through   that dynamic drain range and like i said just a  few lines of code okay now what we want to do is   i want to add the items from the selected tab  right so how do we do that and basically what   that means is if there are any items that are  associated with this project i want those items   to appear here and so those are going to come  directly from the project item database so what   i want to know is i want to know all the shipping  items that came again this is again i can just use   we'll keep that there but basically this is  the same thing project b6 is the same thing   as whatever's in b5 so either one would work this  is probably unnecessary so what i want to do is i   want to know all of those items from the shipping  on project 2 and i want those to appear here i   want to bring those items in i want to place them  directly inside here and so that's what we're   going to do inside this vba first of all what  i want to do is again remember i said project   i want to set b1 to true i want to differentiate  between when a user makes a change and when vba   is making a change so when i make changes here i  want this to go to true b1 to go to true so we're   going to start it out b1-2 and of course before  we finish we're going to set it back to false   and so what we're going to do is first we're going  to clear out all of the remember we've already   saved everything we've already saved it right  here all match up we've already saved all the   items in the project in that tab here so we can  easily clear it out and that's what we're going   to do we're going to clear out everything b4  including b4 b4 is our selected item row so   b4 we want to clear out we don't want to show  any selected row i want to clear out b4 and i   also want to clear out everything from d column d  all the way through n well you say y n right n is   going to be located we've got some data located  in n as well okay now we'll get into the data   that's going to save but there's two there's data  information located here and i should probably   group this all so we can easily move it i'm going  to group this here and then what we're going to   do is that we can easily move it okay so group it  and then we'll unselect this and then so basically   inside here we'll get to this in a moment  inside here is we have some data in here so   right in here i'm going to write in we have  right here this is our project our item id here   so i'm going to write in just temporarily item  id i wanted the id that's associated with the   item and i also want to know the database rows  i'm writing a database row now they're here   already here if you can see here but they're  hidden right and i like them hidden i want to   keep them and they're hidden based on the custom  format if i go into the more number formats you   see that i've got two or actually two different  semicolons actually you could just put one here   if they're numbers i can just put one thank you  and then so we can just put one semicolon to hide   them however if we want them displayed we can  just change them back to general format general   okay so it's going to display it and that means i  know the item id that's associated with each one   of these individual items are all the same right  if i change that right to shipping right you're   going to see a different item id appear here let's  click on that shipping here we see the different   item id is going to show up here of course it is  located 12 it's just hidden right but it's here   inside 12. okay so but i also want to know any  database row that's been saved what is the row   that's data notice this is 39 that is going to  be the row that's associated with this item if we   take a look inside the projects and we take a look  on row 39 we're going to find that particular item   right here so it is that item here it's located  on row 39 and here's the item id so when we save   that when i go ahead and click save it's going to  automatically fix that here all right we'll reset   that and set our titles it's going to be i want to  put these here there we go that makes more sense   put them up the top those variables they don't  belong there now do they no they don't okay   good so we'll just delete that and we can  just reload that tab we don't need that there   when saving that work that'll update that okay so  we'll just update that okay so what we want to do   is we want to make sure when i make a change here  i want to make sure that that item database is   saved automatically in down okay so to do that  all we need to do is just select a different   different number and we want to make sure the item  id and the database row if those associated are   going to be put in there again i'll go ahead and  change these to general too so you can see that   there are still custom general we can see the item  id in the database row that's associated with that   okay so we want to bring all this information  so the