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 accordingly 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.
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.
Posts Related:
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 accordingly 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 accordingly 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 accordingly 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 accordingly 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.