How To Create A Dynamic Employee Skills Matrix With Projects In Excel [Masterclass + Free Download]

hello this is randy with excel for freelancers and welcome to the dynamic skills matrix in this training i'm going to show you how to create this incredible skills matrix complete with dynamic skills adding the ability to add skills adding employees by team or by name we'll be able to create projects filter out projects and we're also going to be automatically be able to create ad staff based on the optimal staff just with a click of a button it's going to be an incredible training i cannot wait so let's get started all right thanks so much for joining me this week the dynamic skills matrix one incredible training i've got so much to share with you this week we're going to start out with of course what is a skills matrix i was not sure a while back but i want to done a lot of research on this and i'm going to share that with you well certainly a dynamic skills matrix or a skills matrix in this case is a framework used to map employees skills and their levels it's also a grid that contains information available about that skill in the evaluation that's what we've done here we've got a list of employees a number of skills that we can add and then we can evaluate it and what i've done is i've taken this particular skills matrix and i've wrapped it around a project manager so that we can take those skills and we can wrap them inside a project manager creating projects so we can find out who are the best employees for a specific project what that would cost and what is you know and all the evaluation so there's a lot to go over of course in this training even if you don't want to create a skills matrix there's so many excel skills that you're going to learn i have so many tips tricks and techniques to share with you including we're going to go way beyond the matrix we're going to be covering conditional formatting lots of great unique conditional formatting rules a brand new sum product formula i'm going to go over with you and we're also going to working with shapes and pictures so we can add them dynamically as you see here in the pictures for the employees we're going to be adding those i've also got sorting through vba advanced filters project database management data mapping and a ton more so you won't want to miss this training i'm going to go over every single line of code every formula every conditional formatting in every format so you don't want to miss a moment grab your coffee grab your tea or your beverage your choice and we're gonna get started i bring these to you each and every tuesday absolutely free even the workbook is free all you need to do is click the link down below using either your email or your facebook messenger and we're gonna get that sent over to you right away i just asked a few things you can do to help us out that'll keep these trainings for free always just go ahead and click the subscribe button and don't forget to click the notification icon bell as well that'll ensure that you get these trainings to you each and every week and get you a little alert also smash the like button that really helps us out and comment below also i have 200 of my most amazing workbooks and i put them all in a single zip file and i'm going to make that available for just 77 to you that comes with complete a complete 200 workbook library and what that means is you can get a single click to open that workbook or single click to open the training video associated with that it's a great tool and that helps us out it's just 39 cents per workbook so if you pick it up that will help us out because i want to get into this training i've got so much to cover let's go over the overview of this application why it's important for companies um and how it can be used and of course all the things that went into this training in this workbook this application all right so basically what we have is a list of employees below here and we can we can display their hourly rate or we can display their staff position right whichever one's important to you if we choose the hourly rate we're gonna then be able to get a total estimated labor and that total estimated labor is going to be based on the project days and based on the number of hours we've got in our admin screen here i've got the work day hours so we're going to be able to determine the project cost we can also have a labor budget too so if we have a budget it's going to help us determine the cost okay we also have this matrix basically what we can do is we can rate their skill for example fred here freder's on appliances he's got a skill of nine a one and one between one and ten and this why is this called dynamics because you as a user or product developer or as you present this to your customers can make it fully dynamic to them meaning we have dynamic levels right i have it on 1 through 10 but you can create whatever kind of levels you want in a description so the way we have it is 1 through 10 one being little or no skill all the way up to 10 highly skilled proficient you know so what we want to do is we want to rate our employees level based on that we want to know what skill now i mean in my sample here i'm going to use the construction industry so my admin screen i've got a list of skills these are all dynamic meaning when you get this workbook you can put whatever you want in there so make sure you pick it up so i used to be in the construction i used to run a construction company before my excel days so we're going to use that as a sample it's something that i'm familiar with so we've got all these trades let's say you're building new houses right you want to build a new house or you're remodeling houses you may have all of these trades that you want and you've got a list of maybe 50 employees each one has different skills so when you're building a house you need to know who to use for what job and that's why i've done this skills matrix skills matrix is great great because it maps out not only here the skill that you have here and your employee and then it adds a grid so you can see who and then i've had some conditional formatting a one of course would be a very low skill while 10 would be the highest skill available right so we know peter parker here he's got a skill of flooring a very high he's an expert flooring and he's expert on drainage i don't know why you'd have to play these numbers are random obviously but you get the point right so this way you can rate it now i've also done here this these two rows right here this is the minimum skill level so for example let's say you've got a job that requires appliances right a lot of appliances right and you know you need a very very high skill level eight and you need five employees five minimum five staff each with eight right so if you have that right so notice this is green that means we have that we have one here two here three here four here and five so we're covered that right we have eight we have five employees with at least eight right if we go down here we can see but here notice this is seven we need seven employees with a grade of seven or higher but we don't have it that's why it's in red notice we have one ten one nine one seven here a few nines but we don't have seven of them right we need seven of them and we don't quite have that we only have six of them right if we only have six one two three four five and six notice we only have six employees with a skill level of seven or above so that's why it's in red yellow would be exactly the same so not only do we understand how many of our technicians have the ability we see in the areas in these areas here in curtains drainage and drywall we have a lower skill level especially kurds so we need to know we to make some employees likewise if you've got a project let's pull a new project that doesn't have anything let's say we decide we're going to add some skills let's say we want to let's say we're going to be doing some framing so we want to do a kitchen let's see we're doing a new kitchen so we're going to add some cabinetry in there we are going to add some let's say some lighting right we need to put lights in our kitchen and we need to do a few other things maybe we're going to do a little bit of a plumbing right we need a new kitchen sink so we got these skills now i know that this project i know i'm gonna need about five staff on this project and i'm gonna save that project and i know the project let's just say five days right or whatever the days and then i'm gonna save it again so i've got these but now i've got a whole list here my employee list i've got all these employees i've got all these different skills how do i know which employees to get for the job right well with our skills matrix with this kind of all we need to do is just click generate and it's going to automatically generate the best staff for the job based on all those skills really incredible too really powerful so we know and now let's see we decide okay we've got great we've got uh five we've got our what if our budgets let's say six thousand dollars we're over the budget here right so our budget our budget six thousand our estimated labor is six thousand six hundred so we may need to make adjustments for that but we we realize here that leslie here she's got the highest skills at eight ten and ten so she's scoring a 28. she's got all those skills covered right we can also put in you know our skills here if we want to larry's done pretty good but we see that greg and tina doesn't have the skills but that's still the highest these are the highest available so we may want to make adjustments to that but this kind of tool gives us and it shows us the true power of excel and when you learn all the skills in creating these as opposed to just downloading this and start trying to use it what if you learn the skills that help you make these workbooks right you can then create these and put them as part of a larger project and sell them for passive income so not only do i want to teach you excel i want to teach you the tools tricks and techniques to be successful with excel so that's why these videos are so important because not only learning these applications and downloading them using them but learning how to build them your scale itself so that's really critical so that's what we're going to do today so we get an idea right so what if we want to add a team let's say let's say i've got a new project here and i know i've got teams i want to add team one i can just click adding a team or maybe i want to add an individual employee maybe i want to add individual staff i can do that here simply adding different staff if they haven't already been added clicking adding right maybe i've got a maybe i got a bunch of employees and i like all these employees as a team and i want to create a set of teams so i've got this this list of employees and i like them i want to create let's just call this electrical team right we can create an electrical team here right and automatically this electrical team will be safe so i call this electrical right if i spelled it right nope i didn't of course let's try that again electrical okay so we've got actually now i want to save this as a team it's automatically going to be saved as a team so the next time that i want to use this team on a new job all i need to do is simply click team and then add in electrical here and then click add and it's going to add all those employees based on that so we can create teams super powerful we want to list out projects by completed right maybe we only want to show those completed projects maybe we only want to show projects as a certain type so we can do that or we can list all of them that's going to be using an advanced filter so we've got advanced filters here i've got data mapping here i've got so much i want to get right to it so that kind of gives you an overview a rough overview of some of the skills now we're going to go in to this application in full detail and show you everything that you have you notice that so here we've got some project but notice there's nothing here there's only one here right so why would there not be any additional fields here because we haven't added some skills so as soon as we add it if we try to add the same skill of course it's going to say with the skills already been added if we tried a different skill it'll let us add it and so as we add the skills it'll add the skill grades according to for each employee automatically so we've got a lot to cover on this all right so let's get to it before we do first of all we've got project information here right we can save individual project information so certainly we need a basically a table to save that project information that main project information now for every single project here's the main information we've got the product description we've got the number of required staff on there we've got a budget that we can put in if we have a budget so we have a lot of information on it so we got to save that inside a database so once we click save we need to save that main information we're going to save that here in something called projects so project 14 we have all that information here's the one i just saved project 11. so that main information gets saved but as you'll notice we have other information that must get saved i've got to know the employees that are on that particular project i've also got to know the skills that are assigned to that specific project so we have two other databases or lists or tables however you want to call them that are associated with this project so each project has its id for example this particular project is project id number 11. so inside opposed to our main one we have two sub trainings sub databases here for this training and that's going to be called project skills list so number 11 has two different skills landscape and masonry notice we have a project id of 11 landscape and then i've got some other information here so we have a minimum skill level a minimum staff the column and the database row here so basically it's this so if we start adding i know i need a skill level in this case of let's say five and i need two staff to have that skill level and i've got another skill level let's say of eight and i need two staff to have that so i've got that covered we've got you know three at 8 or 48 so we're good if i change that to 10 it's going to be a problem we don't have enough skills for that but if i update that and save that project it's going to also save this information here inside the project skills down here we now have those numbers here inside the landscape so that's all saved inside here so that's covers our skills so these skills are tied to a specific project i also of course have those employees i've got to save those employees who is assigned to this project right whose skills do i want to show up and i've got that inside here our project employs so again going down here to project 11 we see here 11 we have all these employees assigned to this particular project each employee comes with a name a rate and there of course where they are located these are from formulas right so we've got a rate so let's go up here we've got here employee rate how much per hour and their position this information their rate and their position is going to come from another table now also another table in this side skills we have to know what skills each one is i need to know that fred is at level 9 on landscaping and mark is at level 1 on landscaping right so to know that that's going to be saved inside the employee information so this is our employee information we have an employee id an employee name their position their hourly rate if they've been assigned to a team right that what team they've been signed by their email this could help for some automation in case we have project we want to email them a picture associated with them notice we have the picture a picture file name and then of course we have all the up to 20 different skills here 20 different skills and then we have a total skill rating and i'll be going into that a little bit later on inside this training okay so we're going to go with that so we've got all the information so this particular skills matrix is going to pull information from all of those sources all four of those sources it's going to pull this information from our projects table it's going to pull this information from our project skills table it's going to pull this information from our project employees table it's going to pull this information from our employees table so also we'd be able to increase increment those if we want to update the skills on any i forgot to show you this so we can update that in case we have a change or we can downgrade them we can't go beyond beyond one obviously but so we can change those skills levels just with this or just simply by entering the number that works as well so that's no problem so we've also got conditional formatting also we can show the different positions or we can show the rate just by this it's going to alternate between their position or their hourly rate in case you want to see both of them great so we kind of covered the overview let's take a quick look inside the admin screen and see what that is now we have our list of skills this is fully dynamic so as i change them here they're also going to be able to change inside the employees they're linked so if we take a look at this their link we see that this is indirect admin column c and column minus four what do you mean by column minus four okay so we this is a good way to do it so we've got appliances this is located on c4 right so basically whatever's in c4 here we want to put over here inside employees but i don't want to put c4 c5 c6 c7 because that's a you know a little bit long so how do i i know it's c so how do i get 4 5 6 7 right basically it's always going to be c so if i know this is column 8 right then what is column 8 minus 4 well that's 4 column nine minus four is five so that's a great way of getting four five six all the way along so if i drag that i know that the existing column is eight and i want to extract the number four i have to subtract four if i know this column is 9 and i subtract 4 i'm going to get 5. so we can use that along with an indirect formula to pull that so again indirect admin column c this won't change right column the current column minus 4 will get us in this case 4 in the next case 2. and then of course it's true because it's going to be a1 style so once we have that all we need to do is just simply drag that all the way over to all of our skills and it's going to automatically update very very easily so basically we're turning from a multiple row multiple rows single column table here into multiple columns here inside employees so that way anytime we make a change here it is automatically going to be changed here on our employees so we've linked up that inside our admin screen i've also got the project statuses and now notice the project status of all stats this is going to include two named ranges why is that because when i assign a project staff excuse me when i pri when i assign a project status i want to choose from a drop down list a dynamic drop bonus based on these what do i mean by that here's a product status project status here completed estimated progress right i don't necessarily want to show all statuses though that's not going to be helpful i only want to show however inside this drop down list when i want to list specific projects based on their status i want to show that including all statuses so maybe i only want to show completed or maybe i want to show all of them right i can do that with this so if i create two named ranges based on this single column i can do that and the same thing for project types so how do we do that if we look in the formula name manager i'm going to drag this over here and we see that we have status here under the project status here and project status with all so project status is basically a named range a dynamic named range using offset formula zooming in you see e5 then three commas counting a e5 through e23 so we're simply counting all those of the cells now we're gonna do the same thing for project with all except in this case we're gonna start out with row number four not number five what that's going to do is allow us to accomplish the all statuses and we've done the exact same thing for project types we've got two under one project type containing just the types and another one with the all right so we've got two of them here notice the dancing that's around that here it's going to allow us to do that so we have both of them here available to us so we can use a single column one that contains just the data and one that contains all of them all we need is two different named range and then we have employee positions here and this allows us to employ and then we have our levels that we have right you can create your own levels and i've got a few other things here very simple i've got the workday hours this will allow us to calculate the actual skill amount total estimated labor amount right if i know this project's going to take five days and i know that they're working eight hours a day all i need to do is take this hourly rate here and multiply it times the number of hours times the number of days and that's going to get us our total estimated budget and that's just what i've done in here so i've taken this here and i'm giving it a name called workday hours then once inside our skills matrix we take a look at the total estimated labor we see if b6 equals one and why is that important and i'll show you that now basically notice that i only want to show that total estimate if the hourly rates have been listed because i need these hour alerts they would not be listed if i click staff position right then we have the positions right in that case this particular option group notice we have an option group here and we click on the format control and we see that this tied to cell b6 so b6 is either going to display 2 or it's going to display 1. right if i click hourly rate it's going to display 1. if i click staff position it's going to display 2. so i only want this total estimator to calculate when we have displayed the hourly rate if it's displayed stat position we're not going to show anything take a look in this labor's nothing because i'm just showing there i don't want to show that however if the i've clicked on hourly rate this is going to be one and then we can continue on with the formula if it equals one then i want to show that formula w3 which is the number of project days and i want to multiply that times the workday hours and i want to multiply all that times the sum of f9 through f68 and that's basically all should probably make this 99 using enough here right so that is basically all the employee rates the hourly rate so that's all we need to do to simply calculate it if it's anything other than 1 right 2 in this case show nothing so this particular form is going to allow us to create the total estimated labor for a project this really helps us out if we know we've got a budget of 10 000 and then we know we've got a little bit of room in our budget to add an employee so maybe we can do that all we need to just click greg perkins and add him here and that's going to increase it so now we're a little bit closer on the budget so adding them is very very easily but we see greg doesn't really have the skills necessary here so we may want to add more okay great so that's a really really helpful let's get into some of the conditional formatting and then we're going to move into the vba so i'm going to highlight that and go into the home i'm going to drop this down for a moment we're going to be working with conditional formatting and manage rules and we take a look we've got several rules here the first of which is this one here when i select a cell what i want to do is i want it to go green if you've seen that in the past here i want to select it and i want it to go green automatically right just like that so how are we going to do that well the best way to do that is just simply use conditional formatting now i need to know both the row and the column of this when i select a cell vba is going to tell us that this is row 10 and this is row and this gives me that and the level column is 9. so when i select that cell we see that our row is 11 and our column is 8. if i know both the row and the column we can use conditional formatting to apply that to only the cell that contains in that case that has a row of 11 and a column of eight so that is with conditional formatting so when we manage those rules we see here we've got those using the and inside our formula to determine which cells format we're going to do done b9 will be the row b9 must equal the row and b10 must equal the column and we're going to format with that green here and then when we go in here we see that it's going to be applied to 2 all the way from g9 through z99 that is how we automatically it is vba remember it's vba we'll get into that in a little bit vba that places whatever column you select into b9 vba that places whatever row that you selected into b9 and column into b10 okay great i've also got notice that we've got a really cool graded schedule this one here is going to let us know we've got a graded color scale so when we edit this rule we see that we've got a color white all the way to green so this is going to give us our gradient it is a two color scale that we're using our minimum is going to be one right we only want to color cells that are one and our maximum is going to be 10. that is the same one we could just as easily link use a max formula here if we wanted to and we could use or link this to a specific cell here inside our a1 so we could link this to our you know 1 here or whatever we want to do we could link it and make it even more dynamic i've just set it to 1 to make it a little bit easier to see the less formulas so that is an option we also have 10 right so we know the maximum and i want this maximum color to this green if i were to change the colors here it would automatically change inside that so that is going to give us our color code so that way the more skills an employee has in a specific skill or trade the darker the green is going to go and the lighter the white so notice that's how it works relatively simple on this one and then i've got another one here let's take a look at this one and this one is for conditional formatting notice how those rows have alternating colors so basically what i am doing is i'm going to color those even rows of color of white so e9 but i want to make sure that e 9 e 9 the first row in our applied to e 9 here 9 does not equal empty and then the mod of row 2 equals zero this is for even rows and i'm going to give it a format and we're just coloring it white i'm giving it a fill of white color that is it so that's all doing why is that because that way all of the other rows are colored this green i want this dark green and if we take a look back inside the conditional formatting here so we have this nice color so see this how it goes to white white but but not here because there's no employees here right so the one of the conditions was two conditions one i wanted it for only four even rows and two the other condition e needed to contain a value that way those two in this condition these aren't met that's why it's given this color now we could make them all colored if we wanted to there's more conditional formatting that i could be doing on this i may do it in fact we may be doing that on our patreon if you like the screen you want to see me add something don't forget we've got a brand new patreon and that what i'm going to do is i'm going to be every single week i'm going to take this application i'm going to build on it but i'm going to build on it based on your suggestions if you want me to add a feature you want me to fix something or maybe you want me to focus on a specific app i'm doing that inside our patreon every week i'm creating a brand new video and a brand new download in five it starts with just a few dollars a month so check out the patreon i'll include the links down below okay so we understand the conditional formatting i've got a little bit more conditional formatting for the employees here so when we can go into conditional formatting and we manage rules we see a few of them i also want to know in this case let's take a look at this e9 does not equal empty the e9 is of course the first row in our table notice there's no dollar sign before the nine it's not absolute and that means that it could be for every row that it's applied to if we look under the format again we see that we have some borders here and some fonts here and so that's going to help us out right so let's take a look at this one what do we have i've got this colored border take a look at this like all i want is this dotted border but i only want to show this particular border that's it this border only for cells that are don't have there so what does that mean you see that's only for column e 9 through e 94. notice this dotted line here and there's no line here but only here that's how we do it we haven't applied but only for those with the call employees within column as soon as it ends there's no employee there's no dotted line here so we want to create this dynamic table looking using conditional formatting so we've done the same thing for this one here but this one here is going to be again e9 this one's going to give it this lower border lower border here but i want this lower border to be applied for both e and f columns e and f therefore when we look in the applies to we see e9 through f94 so it's going to be applied to both columns this one's just a single column i also want to when i select an employee i want to highlight that row and it when i do that vba is going to take that particular row that we've selected it's going to place it in b8 so we click here we see b8 has that selected it's given that format basically it is a fill of this fill effects we've given it two colors here and also we've given it a font that font is bold and that font is white and that way as soon as we select it it is vba that's going to take that and put that directly inside this row right here b8 b8 is going to take on that as soon as i select it ba is going to take on that it's also going to display this x and allows us to delete employees so if i want to delete employee i can do just that easily removing employees so many features on this so we can do that too and all i can also of course add and delete it but if we want to delete a skill we can delete a skill too very very easily so very very hence hence the word dynamic because it is a very dynamic skills matrix meaning you can change any aspect of it you want of course when you get it you can even update it even more but i've made it pretty dynamic as it is so many features in this okay so we've gone over conditional formatting for both the table we have a similar conditional format here for odd rows here now take a look at this i've got some conditional formatting first of all what i want to do is i want to sum assuming that e9 is not blank i want to sum all the skills so let's go into a project with a lot more things so in this case what i want to do is i want to know who's got the highest skills right in this case i want to give them a green flag those have medium or average skills based on all the other skills in the project i want to give it a yellow flag and those with the lowest skills i want to give them a red flag so the first thing we have to do is sum up all the skills using sum and only if of course e9 contains a value that's going to sum them all up once i've summed them up i can then add a conditional formatting and if you'll see here we've got icon sets here so if i want to add an icon all i've done is simply added a conditional formatting using this icon set that's just what i've done inside the conditional formatting so i've added an icon set here and it's going to apply to a9 through all the way a99 that's it relatively simple so when we see that we see we can even judge it this is automatic right so it's based on percentages so we can change that if it's of course if it's greater than 67 based on all the weighted average of all those it is going to be higher now we can base them on numbers or we can base them we can use a formula but i've just used percentage those with greater than 33 is going to be inside the yellow flag and less than that is going to be a red flag so we're basing it on third percentages third and third and third so those in the highest third are going to have the green flag those in the middle are going to have the yellow and the lowest and third is going to have that red flag letting us know who is where you know who's got the highest great okay great so we've also got conditional formatting here again what i want to know here is i've got all these skills down here now in this case notice this is called minimum skill level so the minimum skill level is seven now i need seven staff with this minimum skill level i need seven the minimum staff at the skill level i know that i need seven staff that have seven but that's not i don't have that right so it's colored in red i need eight with eight now i barely cover that that means i've got exactly eight i'm not going above but in this case i i need two staff with two well i've got more than that obviously there's a lot more staff with two so that's no problem but if i were to change it to let's say 10 and i would to change this to let's say eight obviously i don't have eight staff with that have a 10 score so it's going to automatically change we're going to use conditional formatting for that as well so we go into the manage rules we see three different rules and there's two different conditions and so let me take a look at this formula here and of course we're using am because there's more than one condition what i want to do is i want to count all of the cells with values from g8 i want to know all of them i want to use make sure it's greater than zero meaning i only want to count those with values what does that mean it means we don't want to apply this let's pull on another project here if there's nothing here i don't want to apply them to things without the values here i only want to apply it to project with values so back in this project again so i do want to do that so we want to make sure that the first condition is that there's actually values inside that column so that's the first argument right we want to make sure that's greater than zero the second argument is here g7 meaning g it's going to go for every column but it's always going to be row 7 inside our range g7 does not equal empty right i want to make sure that we have a minimum skill level column seven contains our minimum skill level so that is required we cannot apply conditional formatting if we do not have a minimum skill level so that's all i also need to know how many staff that we need at that skill level if we don't have that minimum staff which is in column eight then we cannot apply this so also g every column on row eight that we're is inside our range also has to have a value okay so those are all these three are required or we cannot apply it what else then in case it's red what i want to make sure is then i want to count if making the count count all the numbers within that column g if that number is greater than or equal to whatever is in g7 right so that's we're going to count if i only want to know how many skills are above for example let's take this column curtains here right i want to know all of the skills that are greater than or equal to the skills meaning i don't want to get this is 4 this is 4 3. i don't want to count that i only want to count 10 9 and 7 greater than or equal so this is going to be counted this is going to be counted in 19 and the last one this is going to be counted so i only want to count those above or equal to the seven skill level and i want to make sure however how many we count however how many we count let's escape out of there however many we count of that that are greater than or equal to that skill level i want to make sure that that number is less if that number is less than ga meaning let's say we've counted in this case 7 there's not that many skills that have seven or above right so it's less than that what do i mean by that let's just take a quick look at it close it up so we're looking at this column right here column j right i want to know how many are equal to or above seven so we've got one two three here these three right here right we have this one here four we have five here and we have six here notice there's six right six of six employees have a level of seven or above but i need seven i don't want six i need seven so if it's less then we know to color red so inside this we know it is less if it's less then g8 less than the number then color red so in this case it's less right in our yellow it's simply everything else is exactly the same in this case it is equal that means the same number of employees that are at that skill level levels equal in this case eight so that means we exactly have eight employees with eight or above with eight or above we have exactly eight so that's going to be colored yellow and in our green we have simply greater than everything else is the same except we have more in this case like let's say column anywhere any column that's green we have more than that number of employees there are more than eight employees more than six employees with skill level six or above so this is greater than whatever's required so that's all we have to do to give it that nice color so we can quickly see which skills in this project are going to require different employees employees with greater skills or we know that these particular projects especially curtains and drywall we don't have enough employees with the right amount of skills obviously we don't have 50 employees but you get the point right so i'm just trying to give you that but of course if we have you know 10 or we change this to like say five staff maybe we do let's go with two staff there we go so we do have two staff so if you change this we know the number of staff that is required to have it so it's very very helpful okay great so we've gotten to cover really good over the conditional formatting as far as the selected row we also have the same thing under projects right the same thing when i select a project i want that project it's going to be based on here our project selected project row which is located in b2 so that's going to be conditional formatting same as what we have in place okay great so let's take a look let's get started in the vba and let's show some of the things that we have when i select a particular employee i want this x to show this is a specific shape all i've done is add an icon onto this on a background of a shape so we say it's a group of two of them here and they're together and i've assigned a macro to this so it's basically two shapes here one picture and one shape and i've given it a macro i've given this a name called delete employee button and if we drill into one of the shapes and we click assign macro we see that it is a macro macro called project remove employee okay so let's take a look at some of the vba that is going to help us with this so we can assign this let's zoom back out to 100. so let's take a look so we can display this and also the vba when i make a selection i've got another x here it's going to allow us to delete a specific skill on a project so that's the vba we want to show you right now and that's based on selection change so we're going to go into the vba and take a look at it inside the developers and inside vba we go alt f11 will get you there and as you see i've got several sheets here but we're going to focus on the skills matrix sheet skills matrix sheet that's the one i want to focus on and i'll just zoom over and let's bring this back to the left here we're over the right and i want to look at instead we're gonna this is worksheet change but i want to focus scroll down and focus on selection change this is when we make a change to a worksheet this is where we make a selection change so this is what i want to focus on the code here one of the first things you want to write in order to avoid bugs is target dot count large is greater than one this first line of code here is going to help us if the user selects a large section of cells or at least greater than one we want to exit out of the sub keep in mind that if you have merged cells you might need to change this number anytime you have shapes like when we select notice that we've got some good group here to show up we've got something here to show up and we've got when we select a skill something here so the first thing when you have these specific shapes that are only for a single purpose only to delete an employee only to increase or decrease a skill level or only to delete a skill the first thing you want to do is on selection changes to clear or not delete but basically hide those shapes on selection change so that means whenever i select something else it's going to be hidden so that's what i want something else is going to be hidden so the first thing we want to do is hide all those shapes but we only want to hide them if they're currently visible so first of all if shapes delete employee button this is the one for deleting the employee if it's equal true meaning it is visible if it's currently visible then hide it using this line simply going to hide that shape we're going to repeat this step for both the delete scale button and the level change and that basically means automatically when we make any selection change regardless any first thing we're going to do is hide all of those if they're visible only if they're visible that way we don't need to run the code if they're not visible so once we do that we can then move on now on selection of a project when we select a project we want to do a few things if the user makes a selection from d7 through d99 and that's going to be here d7 through d99 but only if there's a value inside d if there's something that's empty there's nothing we want to do so in this case what i want to do is i basically want to take find that project and i want to place the the id located in b3 and the row that's been selected in b2 so we do just that with the following code so first of all we want to make sure that d contains a value right if they select on a cell that contains no nothing in there there's nothing we can do so we want to make sure that d in the target row the row that we've selected is not empty next up what i want to do is i want to dimension a variable called found project i want to look for the project that we've selected i want to look for this name where is john's kitchen i need to know what the id is sometimes we put the id in a hidden cell in this case what i'm going to do is just look for the name and i'm going to look for it inside projects so i've created a named range called project name we go into the formulas and then name manager and we look inside the project here called project name so i've got this dynamic and this is basically all the projects so what i want to do is i want to use the find command inside this find i want to look for the name i want to find where is that found once i find the row if i know it's on row 5 using vba then whatever is located in column a and the row it's going to extract our project id i'm going to take that project id i'm going to place it directly inside b3 once it's in b3 then i need to determine i want to load that project i want to bring all the information in and that is going to use i need a row for that and we're going to use the match for that so basically i'm going to take that project id which is another named range just like project name same thing if we go into the name manager and go into this time project id we see that we've got a dynamic named range just like the name except this is focused on column a so again i'm going to use a match formula match whatever's in b3 and the project id and then i'm going to add 3 because i want the row right the first one starts on row four so i want the row that's going to automatically extract it so now i have the actual row project id three is located in row six if we look in our projects we see project ids through once i have that i can load up the information so inside our vba the first thing we're going to do is we're going to determine found project as a range because i need to find that project we're going to set that range found project is going to be equal to projects named range project name that's the dynamic named range i just showed you i'm going to look for something inside that what am i looking for the target value the target values the project name that the user selected in this case here when i select starbucks build that project value is called starbucks build i'm going to look for that inside this list once it's found it's going to determine the row that it's been found on so the next thing is if found project is nothing this means it's not been found right if for some reason it's not found found is nothing it didn't find anything we're gonna let the user know project has not been found and i guess we could exit the sub out of that right so we don't need to move on if project's not been found we cannot load it up okay assuming that it has been found else here right what i want to do is then add the target row remember i want to place the row using conditional formatting inside b2 add selected row number okay so we add that selected row number in b2 i also want to add the project id remember i said once it's been found we can then take the row of that and whatever's in column a of that found row and we're going to place that directly in b3 and then we can run the macro which we'll get into in just a bit called project load that macro is going to load up the project but we'll get into that in just a moment because i want to continue on with all the code inside the selection change event now we also have in on employee selection right when i select an employee i want a few things to happen right i want to know that row that we select it's going to have to go into b8 i also want to display this delete employ button so those two things are going to happen so that's what we do here if the user makes a selection on this time e through f anything through e through f and we need to make sure that e contains a value if they select something other outside that nothing is going to happen so we need to make sure that if they're going to make a selection anywhere between e and f e9 through f and we want to make sure that e contains a value then we can know that we have it selected in place so that's just we've done here e9 through f is nothing and e contains a value in the target row then do something first thing take that row that we've selected and put it in b8 add selected row add selected row so once we've added selected row then we want to work with this the delete and play button so this time show delete employee okay we'll call this group because it's a group of shapes right there's two shapes so with delete employee button i want to display where do i want to display i want to display it in column f the left position of column f and the top position of column f and then i want to make sure it's displayed it's currently hidden remember we've hidden it if they select anything up here so now we need to display it mso visible equals true that actually displays it and that's going to when so as soon as user displays it it's going to display directly in column f and of course the macros that's been tied to that so that's all we have to do on employee selection on still selection something very very similar now all i've done with the skill selection is created another shape but this kind of it's kind of a rectangular here in this shape we see it's a parallelogram that we've added and then i've added that x onto that so in this case what i want to do is based on the selected column i want to display it and i want to move it over notice the columns here column i but i want to move it over because it's kind of this particular column it has the text has been moved over how did we do that well we use this here the angle counterclockwise that's how we get that text to show up if we click format the cell line we see that we have automatically this text is located here notice it's angled 45 degrees to the right so that's how we do that here if we want to change the degree if we wanted to so i want to display this x if the user makes a selection anywhere from g6 all the way to z6 but i want to make sure there's actually a value so that's how we can execute this g6 through z6 and i want to make sure that row 6 using cells row 6 and whatever the column they've selected we need to make sure that that is not empty so on that selection again i want to take the column also using conditional formatting and place it in b7 this time we have conditional formatting exactly the same as we have but this one's based on b7 so have conditional formations i select something else it's going to be gone all right so we have that here so then all you want to do is display that delete skill button on the left of the column and over remember the column because of the text i don't want to display up here i want to move it over so i'm going to move it over 37 pixels to the right to do that we can set the left position of whatever column they've selected and moving it over to the right 37 and also want to display it exactly on the top of row six and making it visible okay great now what about on skill level selection on let's put skill level skill we also have another group here take a look at this if i make a selection here in fact i want to make sure if i make a selection here i want it to show up i also want to make sure that in that column we have a value so i'm going to double check that row 6 and whatever column they've selected i want to make sure there's a value this shouldn't show up i'll fix that i don't want it to show up it's going to create an issue and see that debug i don't want to create an issue because there's nothing in the target column so we're going to fix that up right now i'm going to show you how we do that because i knew that was going to happen because there's no skill to assign it to so how do we avoid that well we make sure that inside that column it said row 6 contains a value so we could do that here so let's take a look inside back inside here here where we were right on the skills matrix here inside selection change and we're going to focus on this area here skill selection so we know that g they're going to make selections and we want to make sure that e contains a value right we need to have an employee and we need one other condition and cells what is the row that row is row six what is the target column target dot column dot value does not equal empty that's how we're going to avoid that right so now we want to make sure that we have that so now if we look at that here see nothing's going to show up only when we select an actual cell that has a value right so now we now there's nothing we can do so that's exactly what we only want that displayed when there's a value in row six of the target column and then i want to display this group this group is called level change group that is the name that i've given to the group of those two shapes which is just simply two triangle shapes and so here we have b9 i want the target row remember i mentioned that to you we're going to place that row in b9 and we're going to place that column in b10 so we've done just that here b9 right and b10 take on the row and the column when we make a selection okay and after that we get that'll trigger of course the conditional formatting then i need to place this group here so inside the code we see we do that then what i focus on is with the shapes that level change group i want to place that on the left it's going to be based on that target cell but i don't want to place it directly on that cell i want to place it move it away one column to the right so we're going to use the offset function we're not offsetting it any rows above or below but i am going to offset the column i want it one to the right if i were going to place this to the left it would be negative 1. now i want to place it just i don't want directly on i want to move it a little bit more over to the right so we're going to add plus one pixel onto that now the top position here is also i want to move that top one one column to the right but i want to place it a little bit higher than that so we're going to subtract two last thing is we're going to display it as true now that's great so we know how to do the selection change okay let's take a look so that's all the selection change based on of course the selected employee the selected skill or the selected skills level cell here now what i'd like to do is show you exactly how we can add new projects save projects and load projects exactly on a selection change so the first one will be loading the project how do we load the project or how do we save a project and of course we're going to get into then generating the optimal staff so let's get into the vba focusing on those projects and that's the project macros so we're going to start from the top and just move down here we've got some variables that are going to help us out for the macros inside this module so let's go over some of those variables we have the last row as long we're going to be using advanced filters so i need to know the last row last results row when we get those results from the advanced filter we might loop through those results so we're going to use the result row for the loop and i want to know what row that project is on and i want to know what column we're going to run through the project columns we're going to use data mapping i'll be showing you that in a moment so we need to run through that i also need to know the skill database remember we have a separate database based on those skills and i want to know this column for that and i want to know the skill row and the skill column what column have we selected what column as we move through this we're going to need to know the skill row and the skill column and i want to know the last skill column right if i'm going to be looping through i need to know the last skill column what is this notice because they're dynamic our columns are different sometimes our skills are going to go all the way to here sometimes we're going to only have three or it really depends on the type of the project so we need that skill column as dynamic here and i want to know the last skill column so the employee database row i want to know where those employees if i need to save that information the employee database column employee row and employee column in the last employee row those are all long variables i have a few string variables we're going to be using the picture file as a string that file particular file is going to be that file that we're located here that is the file name i also need to know the folder what is that folder where are employees located if we see here i've got my employee names pictures located in a specific folder called employee pictures now if you want all these pictures i'm going to include them also on our patreon any icons resources or pictures i put down also on our patreon so even for just a few bucks a month you can get all these additional resources that go beyond the workbook so i'll make sure that that's available for you for our patreon members as well okay so we have our folder this folder we need to decide as a string variable and then the folder combined with the picture file itself is going to be our full file path so those three variables are here picture file the picture folder and the picture path a string i also want to know the project id as a string and the employee picture is a shape we're going to loop through those pictures adding new pictures remember we saw each individual employee picture so we need these pictures as shapes all right great so let's go the first thing is what i want to do is i want to load the list of projects right if i load if i check if i even if i just double click on this it's going to load those pictures if i have a filter like completed right i need to know only those projects that were completed or those in the estimate i want to know only those it's going to allow us to filter it by this type so what we want to do is we need a macro to do that that's going to actually load it so i want to do that but what i want to do is going to direct come directly from our projects notice we have all of our projects what i want to do is maybe we're going to filter by type or maybe we're going to filter by status right so we have those two filters by here here we have all statuses or we have all types so we need to filter that and we're going to use an advanced filter but what we can do to make it easy on us is use a formula formula based on this if it's all statuses meaning there's going to be no criteria if it's all types there's none but if we do select type i want to know just those types so how do we do that well it's going to come inside of the criteria and we've got two criteria set up one for comm one for type and one for status and notice commercials showing here because i've selected commercial if i were to change this to all types our criteria is going to change to nothing so how do we do that well it's a simple formula inside the skills matrix if the skills matrix equals admin g4 then show nothing what's in add in g4 well admin g4 you see is our all types right all types if it's equal to all types then don't create any criteria use a blank same thing for status if it's e4 use all types so otherwise show it otherwise if it's not if it's not all statuses or if it's not all types then i want to know exactly what is in here like in this one industrial right i need to know what's in here so that's just what we've done here if the skills matrix d5 equals g4 meaning the all types then show nothing otherwise show what is in d5 and we've done the exact same thing for status except this is based on e4 if it's all all skills then show nothing otherwise show whatever's in d4 once we have this criteria set up we can run an advanced filter based on our project names right we can just use our project names and project types all the way to status or we can use all then what i want to do is create it so i only want to show those projects names that have the industrial notice that we see project 7 and project 12. project 12 is industrial and project 7 here is industrial so i only want to show those two using our results and i'm going to take those results and i'm going to bring them directly inside here first clearing out all the projects and showing them here and that's just what we do inside the macro so the first thing we want to do is we want to inside the skills matrix i want to clear everything out including the selected project row i want to clear that selected projects based on b2 so we need to clear that as well i also want to clear out everything from d7 through d99 so we're going to clear all those existing projects then we're going to focus on our projects sheet primarily so with the project sheet this is the name that's been assigned to this one here i want to determine the last row of that then what if it's last row less than three we can exit the sub we're going to run an advanced filter and that advanced filter is going to be based on original data if we look inside our projects a3 all the way through i right so in this case a3 through i in the last row that is our original data we're going to run an advanced filter based on that we're going to copy those results and it's going to be based on that criteria that criteria is located in m2 through n3 notice m2 through m3 and one i want those results to go in a single cell called q2 then i want to determine the last row of those results based on the last row of q if that last row is less than three then exit the sub that means there are no results if there are results we're going to take those results from q3 through q in the last row and we're going to bring them over directly into d7 and the last results row plus 4. why am i adding 4. if you notice our results start on d7 d7 our results are starting right here inside there d7 is where i want that first one to go so if our first one is in d7 here but inside our projects our first result here is in three i need to add four to compensate for that difference so that's just what i did here add four and that's going to bring all the project names so it's gonna list of project names okay great so we have that that's the first one we go through that's going to load all the projects in and we want to run that anytime we add a filter in so that filter anytime we make a change event on d4 through d5 is when we want that macro to run and that's focused on defense so if we go into the skills matrix and we've already focused on the selection change but this is actually the worksheet change on d4 through d5 if the user makes any change to these cells i then want to run the macro called project load list that is the exact macro that we just went over okay great so what about a new project new project simply when i click here a new project i just want to basically clear out the project id clear out the selected row clearing out any skills or anything else and i want to clear out all the fields associated and that's it and hide any shapes so that's all we're going to do here and i also want to clear out any of the shapes that remember we had the employee name shapes or if we select a project we have lots of different names i want to make sure that each one of these shapes are cleared out now if we take a look inside this i'll show you how we create these of course but keep in mind that each one has the word employee picture in it each one and then there's a number assigned to that 10 9 so every single one has an employee picture so if i want to clear out all these pictures only not all the shapes what i'm going to do is i'm going to loop through every shape in this sheet i'm going to look for only those that contain the words employee picture and i'm going to delete them and we do just that and remember we've defined this as a shape employee picker as a shape already we know it's a shape because we've dimensioned it up here employee pick as a shape once we've done that we can then loop through every single picture in that sheet so for each employee picture in skills matrix shape we're calling out the sheet here and all the shapes in that sheet if we're going to check each one if the name of the shape contains the word employee picture using the in string command if that's greater than 0 that means that that employee picture found then we can delete that shape that's it all we have to do that's going to delete all those then all we need to do is simply clear out a bunch of cells i want to clear out everything of course clearing out all of the skills all everything just basically everything gets cleared except of course we have the formula here that doesn't get cleared out so it's going to go all the way to z and on down so all the way through z and then i also want to clear out some information here we've got information here i've got a database row here this is important we're going to clear this out i'll go into detail what this is but i do want to clear it up that's the employee project database rule basically it's the database row of this right so each row is associated with it 40 41 right i don't want to have that row and i want to put that in there and that's going to come in handy in just a bit okay so that's all we need to do is relatively simple we're not we're keeping all the buttons here in other projects i'll change over the buttons but this one we're keeping it simple new project clearing that out okay that's all we need to do and then if we want to load another project just selecting a project it's going to load it in alright so what if i want to make a change and i want to save this project if i want a description i want to save this project i want to click save project now we can use the same macro whether we're having a new project and we try to save it of course it's not going to last because we do need to add at least a name before that that save or update project macro is now what we're going to go into right now that's the next one here called project save or update okay so we're focusing again on the skills matrix if j3 is empty right i need to make sure that we actually have a specific i need to know is it is there a current id or not i need to know differentiate is this a new project new project meaning there's no project id or is an existing project and b3 would tell us whether it's an existing project or a new project so that's the first thing we need to determine and what about j3 let's focus on the j3 we need a project name before we can assign it a project name right so if j3 is empty it's going to as you saw that message saying please make sure to assign a project so j3 if that's empty we need to make sure that there's a name so first thing i want to do is let the user know to sign a name a name will be required before we can save a project okay now what we're going to do is we're going to turn off application screen updating and we're going to turn the calculations to manual this is going to make saving a lot faster because we got a lot of data to save and load here so we want to make sure that we turn those off the only important thing is when you're using these before the end of the macro we need to make sure that we're turning those on again that's just we do down here okay so once they're turned off we can then focus on whether it is a new project as mentioned before or an existing project b4 is going to tell us whether it is a new project if it's empty or if it's an existing project if it is a new project we're going to do a few things i'm going to determine the project row which is going to be the first available row here in this case 16. and i also want to do is determine the next project id and that's located in b5 we're going to use the max formula as you may have seen before called maximum of project ids make sure that your project ids are numerical to use this formula if there's any text in there it will not work so i'm going to take all the existing ones i'm going to add 1. i want to know the maximum number plus 1. if there's any error meaning maybe we have no data at all i'm going to default it to 1. so that's what we're going to do is we're going to take this 15 i'm going to place it directly inside this b3 and i'm also going to take it and place it in the first column here inside the a and that's just we do with the next lines of code so the project row is going to be the first available one b3 is going to take on the next project id also a and that project row is going to also take on that project id that's it for the new ones existing one oh we're going existing project meaning it's already been saved we're making an update to a previously saved one just like we did here we want to update that all i need to do in this case is simply determine what project row located in b4 and save that into a variable so we just know here project row is equal to b4 existing project row so we have that now what i want to do is i want to determine the project id is going to be located for either whether it is a new or an existing one i want to get that project id and i want to put it into a variable so once we have that we're going to use now data mapping if you haven't seen this before then basically what we're going to do is we're going to map the project name to j3 notice that column b is all of our project names well that gets mapped here to j3 project type here p3 so i've mapped those out inside here so p3 so each field is mapped out so if i want to save the information from this tape from these fields to this we already know what row we've already determined all i need to do is look in j3 and place it here look in p3 and place it here so we've done just that and we're going to loop from column 2 all the way to column what column is this well this is equals column this is column nine so we know that we're going to loop from two to nine and that's just what we do here from two to nine and i'm gonna basically take whatever is inside this range here project cells row one in the column whatever is that range that range can be j3 p3 w3 whatever it is inside the skills matrix whatever's inside this field and place it directly in that project row and in that project comp on the project that's going to save all of our data in just three lines of code great so now that we've updated that way that's going to save all the projects we're done with this now what we also need to do is i need to save the employees and i need to save the skills the skills get saved in this and the employees get saved in this so we need to do that as well so first of all i want to update the assigned skills so to do that like let's say we decide we want to add a skill this is project id number seven we've got a single skill on this so if we look inside our project skills and we look for project id seven we see that it's got a single one flooring right but i wanna add more on to that i wanna add two more on that so how do we do that well all i need to do is just click masonry or click anything add a skill click another one it won't let us add the same one twice which is what we want and then we add another one let's say welding and we click add a skill so now i've got two more skills that i need to add if i save that project now i need to determine the last one and then if we take a look in the project skills we see now that we have mason and welding inside the database so when i load this project up i know to load these two and i need to load this one also here the original one located here flooring so i want to save these two ones but i need to determine has it been saved before or not right so what i need to do is i need to track the database what row is this one if we look down here we see the this one assign project skill database row 37 46 and 47. what do those numbers correspond inside the project skills take a look at this 37 row 37 row 46 and row 47. so they're saved here so we know here's how we know if i decide to add a fourth skill to this project just like this here then i know if i look down here and i see before saving it right oh it's been saved to get saved automatically i forgot once i added it gets saved automatically that's better now it's 48 so as soon as we add it it gets saved so that database row is going to be saved down there so we do this four skill column equals 7 to 26 7 to 26 starting out in column 7 this is column 7 all the way to 26 which is z right i want to look and see what we have so how are we going to do that if cells row 6 skill column if it's empty then exit four meaning basically it's going to loop through all these as soon as it's empty it's going to exit out so i want to updating all those so if cells 100 skill column equals empty then it's a new skill although almost all right so how do we do that so if here's row 100 if it's empty then it's a new skill right brand new skill this is going to be if it's existing it's not so if this has already been assigned a database it's an existing scale but if it's something i just added this one's going to be empty so we're looking in row 100 and the skill column determining is it new or is it not so that's how we do if 100 equals new then we need to do something all this is for is if it's new or if it's an existing we're doing just one thing so what are we doing if it's new well what i want to do if it's a brand new skill i want to add some information in i want to add in i want to add a project id i want to add in the database i want to add the column and i want to add the database row so we're going to do those things here so the skill database row is going to be with the first available one we need to find the first available one right what's the first available one in this case it's 49 right so we need to get that row so after we determine that row what i want to do is i want to determine the database row and place that directly in there so we know the database row and i'm going to take that database row and i'm going to place it directly in row 100 and a column that's going to be place database row great so now what i want to do is i want to do those three things inside the table those three things are only for new skills a is going to take on the project id now you see why we converted it to a variable so it's easy to use so column a is going to take on i need to put that project id in the first column here i need to put the skill column in column e although we may not be using this in this training but future trainings and the database row still having it there is very helpful and the database row so i want to put all that directly into here so e is going to take on whatever skill column f is going to take on the row we're using a formula that way if we delete a row it will remain consistent we'll show the actual row that is the database so that's all we need to do for new skills what if it's an existing all i need to do in this case is extract the database row from wherever it's located in row 100 and the scale column we're taking the existing scale we're just putting that into a variable so now i have the skill database row whether it is existing or whether it is new gonna have it in a variable then what we can do is add the rest of the information all three lines but the skill the minimum level and the minimum staff i'm going to put that in the next three columns b c and d you can see b c and d we're going to take that information and that information is going to come from row 6 row 7 and row eight so here in row six we've got the skill level here we've got the minimum skill level and the minimum staff so if i add information to this here it is automatically going to be saved because it already has the database row so if we look in this here and i put fives in everything now 55 5 here and then 5 here so then we can then update it as soon as we save it it's going to save those values inside those project skills so if we scroll down we see the five has been already added in both in 46 47 and also in 36 so updating it we know the database shows so we've just updated the information using these lines of code great so that covers the skills but what about the employees and now that we've saved the skills we also need to save the employees we need to know which employees were saved that's going to be saved inside here so i need to know of course the project id the employee name the rate and the position now the rate of the position that could change so i really don't want to save it what i'd like to do is take a formula and basically put it directly from now we know each employee inside the employee table we have a rate we have their position here so if it changes here what i want us to do is i want to reflect it inside here that way the reason we want to put it here is much easier when i load that employee name and i bring those results based on a project id i can also bring the rate and the the rate over or of course the position either one so what i'd like to do is take a formula an index formula based on a match based on employee name and i only wanted to have it for those employees i only want that formula here when we add an employee so how do we do that what are we going to use a formula so what i'm going to do is i'm going to take a formula up here based on whatever that employee name is if that formula is up here every time i add a name to this table i can then bring down this formula that formula is basically we're going to index the employee rate which is a named range based on the employee table and we're going to match it based on the employee name i also have the same thing for the employee position basically an index employee position so if we take a look inside the formulas name manager we see that we have employee rate here this is basically a dynamic named range based on the employee hourly rate we're doing the same thing for the position and all i need to do is find the row based on a match based on this employee name here and it is that match that we are going to run that index match formula based on the project employee so it's indexing the employee position based on a match of that employee name so if i bring that formula in that vba will take that because i don't want to put that formula for every single row if there's no data i only want vba to copy that formula when we add it so we're going to add that project id we're going to have the name we're going to bring down the formulas and we're going to add in that row that is located on that's the database row using a formula using that row formula that's all we need to do for employees and how do we know if it is a new how do we know if it's been added or not well we can look all the way to here if it does not contain a bro this employee database row is based on the row inside this table that i'm showing inside the employee based on this so if we look in row 55 here we see craig nelson on project seven if we take a look inside the skill matrix we see that craig nelson here is based on project number seven so we know that say that's how we can know so let's go ahead that we're going to update the assigned employees but the first thing what i want to do is i need to know the last employee row we need to loop through all these employees so i need to know the last row based on column e so we can use the formula for that and that's going to be the last employee row based on e 99 and x up this is going to give us the last employee row last employee row once i have that i'm going to run a loop so we're going to use the employee rows going to be 9 our first row to the last employer row and then i want to look into column am and see if it's been added if this is a blank column am we can bring these over we don't need so much space in between the two of them right if we're looking in column am if it's empty we know it has not been currently added to the database so we then just add a new so same like we did so if it's empty that is a new project employee we want to get that database row based on the first available row here what i want to do then is add the inside inside am right we need to add that row once we've determined what row we're going to place it in we're going to place that database row right here in column am i'm also going to take that project id and i'm going to place it in the first column project id again that named range is helping us i'm going to place it inside e i'm going to set the database row here and i also want to bring over those formulas remember those formulas in c1 through d1 i'm going to paste them inside whatever database rows paste in those formulas that is d called the rate and post formulas we've got to hear rate and position let's put that position up position position clear it up okay so position formulas we're going to copy them over and bring it that's what's going to help okay great so that's only we only need to do this for employees that have been added to a project new if they're existing all we need to do is simply extract that database row located and call them am employer then we have it there okay great so all this is for whether we're new or existing the rest is automatically for both new and existing we're going to take that employee name you know only one employee name we're going to put it in column b that employee name is located in e and the employee row it's going to bring over that employee name to our project employees here in column b that's it that's all we need to do continuing on with the code that's it if now i want to do check if b2 value does not equal empty meaning we have a selected row if b2 does not equal empty meaning we have what i want to do is select it and that's going to automatically reload that project once we add it so if i decide to save it what i want to do is reload it based on this so all we need to do if i want to add employee or delete an employee just save the project and it's going to automatically load up back into project 7. it happens very very quickly so we just basically want to select update the project name now also what if i decide to update this to a project name i also want to make sure that this project name gets updated here so let's say we change this to let's say kitchen update right and i want to save the project i don't want this to show project 7 anymore i want to showcase an update so clicking save project we now see that d7 is equal to that updated project so we're going to update that name but i only want to update it if i know that we have a selected project row in b2 just to make sure so if b2 does not equal empty then d and b2 value the project row d in the project row equals whatever the project name this is going to update that project name automatically and that's all we have to do and then turn screen updating that's it okay so now we know how to do new project we know how to do save or update the project making sure we have a project name but how do we load the project remember what i told you when we click here we're going to do is we're going to add that project a row the selected row we're going to add the project id here using that find command we went over that inside the selection change here just to review we went over that down here in the selection change when we're adding it we found the project and the last thing we're going to do is run that project load so that is the macro that we're going to go right now that we haven't gone over called project load and the first thing what i want to do is i want to clear out all the associated cells right if we're loading a project we need to clear out all the data located here it means all the employees all the skills all the skill data and all the information above we need to make sure that that gets cleared out so we can do that with just this line of code then i also want to clear out the employee pictures using the same line of code that we did before when we click add new project using this going through every single shape in the sheet any shape that contains employee picture and deleting it once we have that what i want to make sure is that in order to load a project the most important thing is we need to have a project row and a project id so if the project is missing or incorrect this project row is going to be empty if there's any error so we need to check to make sure that b4 is not empty if it's not empty we can move on but if it is empty we need to let the user know to select a correct project so if b4 equals empty message box please select the correct project to load and then exit the sub there's nothing we can do unless we have b4 as a value first thing we're going to do again after that is turn off calculation screen updating add both of those to make sure that this thing runs a lot faster because there's a lot of data that needs to come in i'm going to put that project row put that into a variable from b4 and then i guess we're going to do that reverse data mapping this time what i'm going to do is i'm going to determine that project row and then what i'm going to do is once that project was found i'm going to say okay inside whatever is in that project row in column b we're going to say put it in j3 whatever's inside the column two put it in p3 so it's reverse data mapping we're taking whatever is this and we're going to use row one data to do just that those ranges inside row one so the range is going to be the project row one in the column so this is where we're going to find the range here and we're going to take the range of the skills this sheet here this particular sheet here we're going to bring that in directly from the database and come from the projects from the project row from the project column this way we can add all that information with just three lines of code okay great so that's going to basically bring all this information in here but we still haven't added our employees we still haven't added our skills yet so we need to add that and of course we also haven't added our data in here this is going to come from the employees so in this case when we're loading up i need to load up all the employees all the skills and i need to extract all of the data that's associated which is the employee's skill level and it's going to come directly from here so we need that we're going to be basing loading information from the employees information from the project employees information from the project skills and we're going to bring all that into our dynamic skills matrix so how do we do that well the first thing is the project skills and i'm going to run an advanced filter right so what i want to do inside our project skills is i want to determine exactly the last row and i want to bring all the only the results from project 7. so if i link this in the criteria to whatever's in the skills matrix b3 we have our criteria already then i'll need to determine the last row run an advanced filter so that only those results for project 7 come in i can then bring these results and loop through these results and bring them in here and that's just what i do right here so first the last row if it's less than three go to no skills i'm gonna run that advanced filter from a3 notice we're on column three is the headers the header names must be exact as they are inside the criteria and the results once we have that our criteria here is in j2 through j3 we're going to copy it through l2 through p2 we're going to determine the last row of our results here using column l it's going to last row in this case our last row is 6. so assuming it's less than 3 then go to no seals it's going to skip all this and go right to here but if we have data we are going to set our initial skill column as 7. right this is where we're not using this we could use this but we're not using this because what i want to do and i'll show you why that's important is that basically if i delete let's say if i delete this here then i only want three to show up so that's why we're not using the column so all i need to do is start out of column seven and then just basically go in order seven eight nine ten so whatever they're here it allows us to delete one very easily so i want to start at seven and then just increment the column eight nine 10. so that's all we're going to do so basically i'm going to loop through these skills starting at 7 8 and 9. so we need to set that initial column and we set that initial column right here skills then what i'm going to do is i'm going to loop from the result row from 3 to the last result rule starting in three going to the last result we're going to loop through that i'm going to adding this adding this and adding this and adding all this information and we do that through the skills matrix row 6 row 6 of course is our skill name row 7 is our minimum level and row 8 is our minimum staff so we just add all that in bringing this information in to our specific rows row 6 taking on our skill level row 7 taking on our minimum level and row 8 containing our minimum employees staff at the skill level so we're just bringing that data in each individual one and so after that what i want to do is i also want to place that database row in row 100 right so i want that row because i need to know it's been saved so inside row 100 i also want to place that database row 36 47 46 and 47. that database row is going to come directly from here inside our project skills here 36 37 46 and 47 so that's going to come from column p so that's just what we do here p is going to go inside directly to row 100 in the skill column and each time we're going to increment that room we're starting it off in column seven so we've got to increment that column going from eight to nine so incrementing that column so that's how we bring in all the data and then what i want to do is i want to make sure we calculate that that's very important to calculate that okay because we have formulas in here those formulas are going to be very important so once we have that now we want to load in the project employees we're going to do something very similar for the employees the employees we're going to also run an advanced filter and we have a criteria also based on b3 also in j and i want those results to come through l through m and n in this column so i want the employee name the employee rate and i want the database rule but what if it's not what if remember we have a toggle here what if we want to show the staff position right what if i don't want to get the rate so how do we dynamically change that right based on the selected here remember this this particular one if we take a look inside this again format this is based on b6 so b6 is going to roll b6 is going to change from 1 to 2 based on that so if i know that b6 is 2 means position i can change the header on that criteria inside the project employee so that's the the results here criteria is only going to be project 7 the results i want to show either employee position or either our rate so how do we do that if the skills matrix b6 equals one then what i want to show is project employee c3 otherwise show d3 what does that mean so look inside here project employees i want to show either c3 employee rate or d3 so we're going to show those results are going to be based on whatever's so look then now if we escape out of there we see its employee position as soon as i change that to hourly rate it's going to run a macro that macro is going to load that entire pro project again but this time it's based on hourly rate so if we look back inside the project employees we see now its employee rate so this header is dynamic based on whatever the user selection so the results are also going to be dynamic based on whatever headers used now keep in mind that we must use the same header either basically we're using this header employ rate or employee position that is why it was so important to bring in this information directly using a formula from our employees here rate and position here inside into this so we automatically have the most accurate data here then all we need to do is bring the information here so this is called a dynamic results header based on an option it's very simple and that way our data can show then it's easy then all i need to do is bring the same columns over regardless regardless because it's also going to change now if we take a look inside the skills matrix also inside this we have very very similar if b6 equals 2 we're going to put in position otherwise hourly rate so our header here changes also based on what's the leg is so simply change that over and again all i need to do is then assign the macro to this so that means every time user clicks we're just gonna assign that macro project load to this that way when you click it it loads the project so we have a dynamic column here based on just a few different formulas so that way our advanced filter when we run our advanced filter it's going to automatically show either position or rate so inside that we don't need to change anything inside the code our criteria's j2 through j3 which is that project id and our results l2 through n2 again the last results rule is going to be based on column l then if it's three we're going to go to no employees we're just going to skip all that and go right to here so we do have in place all i need to do again is just bring over the employees and that's going to be basically coming from l through m right i want those results employee name employee position i want that and i want to bring that directly in to right here e through f bringing that over inside e through f and the last results row our results row start on row nine here but in here they start on row three so the difference is six so i need to compensate for that by adding six here that's it i also want to list the database rows those employee database rows i have to come over so i'm going to bring those over directly inside here inside am so they're going to come inside am just as we said here okay so now that it's going to come directly from and it's going to come directly from here and just bring in this database row and bringing them directly inside here that's all we're going to be doing relatively simple on that part okay great so now what we want to do is we have to add the employee pictures we've added the employees here but haven't added any pictures the best way to do this is to use a sample if we take a look at this one here this is a sample shape it's called employee sample picture again all this is is just a circle basically like that and i put an employee picture inside here if i were to do it manually i'd click the format picture sorry it's off the screen and now what we're doing is we're filling it with a picture and we're just basing it on a file so we click the picture file and we would do that put in a picture as a background filling it with a picture but we can also do that with vba and we since we have a folder here full of pictures we've already mapped this folder using the admin screen we already have accurate five picture file names located inside our employee here so if we have all that it's very easy to add a picture with just a little bit of code so we can do that here first thing what we want to do is we want to determine the last employee row is equal to last row i want to set that last employee row right we need to loop through all these employees so i need the last row in this case it's 17. so we've got that down now what i want to do is i want to determine the picture folder remember it's in our admin screen and i want to add a backslash to that that is going to give us a picture folder and put it into a string next up what we're going to do is we're going to set a loop right i need to loop through all these employees starting at 9 going to the last row continue on so as we move through 9 i want to make sure that we actually have a database row so if we take a look in here i want to make sure that we have our employee database where if i'm going to pull the pictures this file directly from the employee i need to know what row those employees are on right is it roll three or four i need to extract it from g but the first thing i need to do is know what row so we can do that with a formula inside the skill so here we've got a formula basically we're running a match based on employee number whatever's in e9 and an employee name and we're going to add 2 because i want to look for that row notice the employees start on row 3. so the first employee is on row three that's why i'm adding two if the first employee is found and i'm using that format i'm going to bring that formula down all the way down here and wrap it in an if error because if there's no employee an error would be found so we just want to wrap that if there's an error it would show empty so that's going to set us an employee database i need to know that employee database so as long as there's a row here then i can extract that picture file name directly from our employees located in column g here so we want to make sure if range a n in the employee row equals empty that means there's no database row so we can go all the way and we'd skip to the next employee assuming that we do have a database row then what i want to do is i want to check to see make sure that there's actually a picture in column g of the employees database sheet that is where the picture file would locate as long as that's not empty we can move on we're going to set the picture file the file is going to be g in that employee right that's the picture file name we're going to combine that picture file name along with the picture folder for a full file path and that is the picture file palpable right there here the full picture file path once we have that i want to check to make sure it is accurate because i don't want to create errors so the best way to do that is use the directory so we're going to use the directory of the picture path vb directory if it equals empty then also we're going to skip to go to next employee which is going to drop down here okay assuming that it's not empty that means we do have an accurate file path we can then add that in so what i'm going to do is i'm going to take the shapes employee sample picture that is this shape right here this small shape i want to duplicate that employee sample picture now keep in mind the name of this called employee sample picture it is very different than the name of this employee picture notice it's spelled out because remember when we're clearing out a new we need to delete all these pictures i want to make sure that i don't delete my sample so i'm going to make sure not to call it the full name employee emp sample so it won't be deleted it is this one that we are going to duplicate once it's duplicated i'm going to assign it a unique name and i want to give it that name the employee picture and the employee road now the employee row is not too important but it is unique and that's good enough i want to make sure that it is unique and it has to contain the words employee picture because when we first start with this macro here we want to make sure that we're removing clearing out any existing pictures by removing any of those pictures that contain that text and so that's just what we do down here on play role then we can work with it once we've created it we're going to work with it so with that particular newly created shape i'm going to position that based on column e and the left plus two so we're focused on column e and slightly off not directly on that border but a little bit to the right plus two pixels gonna do that for us and i also wanna give it the top position here inside the top skills matrix e in the employer on top position once i have that all i need to do is take that picture that accurate picture and fill that shape with that picture so dot fill user picture adding that picture of the picture path inside that it's going to add that picture in the shape that's all we need to do we just loop through all the employees and it's going to add a picture for every single one all right great now that we've got all the employee pictures we also want to load those skills so so far we've added in all of our project information we've added in our skills we've got it in our employees and we've added in our pictures but we haven't added in the employee levels here i need to know what skill levels for each of the employees based on the skills so how are we going to do that well the first thing what i want to do is start a loop the last skill column i need to know the last skill column what is the last skill column and to get that we're going to do here a b 6 we're going to get the last skill column in this case here it's lighting here this column here right it is column nine column nine but i want to get that last column so to do that we're going to get ab6 and xl to the left that is going to give us the last column from ab6 all the way to the left so that we can loop starting with seven and moving on so we can go from seven to whatever so in this case the last one is nine so i want to start that loop fourth scale column equals seven to the last skill column and i also want to know the employee rows for the employer on nine so not only do i need to loop this i need to loop through every single employee and then every single column so we've got in a loop embedded another loop first one employs nine to the last one and then call of duty so i'm going to extract the information so how do we do that for the skill column employee okay so first we're starting employee and then one skill column and then what one i want to do is i want to check a and again remember i want to make sure i know that employee database that's got to be a number got to have that employer because those skills are located here so if i want to know the appliances skill 9 or the cabinetry skill 6 i need to know first of all want to make sure that we have the correct employee database row in this case it's three and i also want to have the column i need to get that column appliances or whatever so how do i do i need to then find what column we know what row because the row of course is going to be located here five six or seven and then i need the column so how are we going to extract a column well first of all you want to make sure that that column is located right here here we put it in 101. how do we do that so 101 is going to take care of that column what is that column what i'm going to do is i'm going to run a match we have a named range called skills and that named range is based on there let's go into that name grant just so we can take a quick look at that it's called skills and that named range is located here in the admin so that is named so if i know skills here's all our name range and i know our first one starts on four but i need to look inside the employees right or the first one that's found if the first one is found where is it going to be found here equals column so if the first one is found that's going to be found on column eight right so if the first one is found appliances how do i get to column 8 well i simply add 7 to get to 8 because i want to know that column so that's just we did inside the formulas inside our skill matrix here so excuse me seven because we need to add one more on this one seven plus one if the first one is found and we need to get to column number eight then we need to add seven that makes more sense okay so that's just what we do here so i'm going to match it number if the first one is found i need to get to column 8. so this would be 1 this will return 1 if the first one is found and i want to get to the employee column i'm going to add 7. otherwise i'm just going to show something other than blank otherwise you're going to show blank this kind of lets us know okay because i don't want it to show 0 here i just want to show some text and then what i can do is i can check to see if it's a number if this is a number i know we have a correct column that column 15 is going to be the same let's take a look inside here skill matrix here take a look at this we're looking for called flooring right and i want to know what column it is so if we do equals column here and we show all the columns here here in this case 8 and i'm looking for column 15 we know here flooring is column 15. i want to extract that 15. i know the row now in the employee row i know the column so i can extract this number to for example if it's peter parker i know that 10 is going to be extracted i know how to get that peter pan i know i want to get that 10 right here i've got to know the row that row of course is coming directly from a n here row 5. now i know the column right the column here is all the way down here and one is column 15. so when i know the row and i know the column i can extract the exact data that i want and so that's all we're doing inside here so the employed database row is going to be based on a n the employee database column is going to be based on row 101 and the skill column now that i have both the row and the column i can simply bring in the data so the cells the employee row and the skill column right the skill column we're looping from seven to nine the employee rose from nine down so that is simply going to be equal to whatever is on the employees the employee database row the employee data score bringing in that skill level over and that's all we have to do looping through each scale and then looping through each employee skills and it happens very very fast when we actually turn off screen updating and you can see how if we just load a large project or let's go let's go back to the big project you see it's very very fast here even there it's really fast so it helps us out even if we have a lot of data so that's all we have to do and then what i want to do is i want to set this default to team name right default team name because i want to make sure that we can tell the user to add a team name here notice that when we add here if we put anything else in it is no longer italicized and no longer that great and that is done through conditional formatting there's a rule here as well additional rule basically if it's f5 it's equal to team name then in that case i want to make it italicized and i want to give it a great color that's how and basically that way the user can then save all of these employees as team fred if they want to do that but as soon as we load the project in here that is automatically going to go back to team name we're setting that default back to theme name using here set the default and also i want to make sure if for summary if the active sheet is codenamed before currently on this sheet then i just want to when i load it i want to make sure we select a cell perhaps let's say j3 that's the project name so that line of code is just going to simply select j3 and that's it turning on off screen calculations to automatic and screen uploading to true very very easily all right next up we have delete that's the last macro in this particular module and then all we want to do is just let the user know are you sure you want to delete it yes or no and delete the project if they say no we're going to exit out of the sub we're going to determine we want to make sure that b4 contains it if we're going to be deleting this we need to make sure that b4 contains a project row if it doesn't let's say we new project and user clicks delete all we're going to do is just say you should want to delete and then we'd say yes and then it's just going to clear there's nothing is going to happen so we want to make sure that b4 contains a value right so to do that all we need to do is just to make sure that we got the project row that's very important if we're going to be deleting it from the project okay so the project row is skill matrix b4 equals the project row and then we're just going to basically based on the project delete that entire row now if i was building this application for distribution i would also want to remove all the skills associated with that that project and all the employees associated with that particular project using again criteria and then deleting them but we're not doing it in this one because we're covering enough that covers it at least it's going to clear it out then what i want to do is once it's been deleted i'm going to run the macro that's going to load that project list and then add new projects so it's going to automatically delete it so simply if i want to delete project 14 and i click delete project and yes i want to do it it's going to delete it it's no longer going to be in this list here so that's delete okay great so we've covered all the project macros and now we've got a few macros left on our skill matrix macro so let's take a look at some of the matrix we're going to get into some of the macros on this one not too many so let's go over that before we let you go alright so i got a few of course variables on this we're going to go through these variables as we come across them for example skill level is going to be a long variable it's going to be the active cell value i've got a macro that's going to i'll go up and down remember when i select a specific scale i want to be able to increase this up here like this or an increment up to the maximum here can't go beyond 10 and it can't go below one so if i go we have it down and we just kind of can't go below one how do we know that well i'm going to use these levels here now we've got a named range called the formula called levels i believe let's take a look at it called levels here it is and it's a dynamic named range based on those levels so that way you can create multiple levels or you can have one through five or one through 20 or whatever you want here and so what i want to do is i want to know the maximum of this in this case 10. i want to know the minimum in this case one so i'm going to use that and that way if the number falls below one we can let them know so the first thing what i want to do is and we're increasing it up i'm going to set the level range to the admin levels right we're setting up range right we've defined this as a range level range as a range and of course if there's on air if it's nothing then please sure to set the level we want to make sure we have a named range called levels that's very important in there so i want to make sure that we have that if it's not there then we need to let the user know to please i'm sure to set the levels maybe it's empty or something like that so what's the maximum skill now the maximum skill this is the long variable we're going to use the max formula but this time we can use application worksheet function max level range okay so it's going to give us the max now the skill level here the skill level here is equal to value whatever the value the user has selected so in this case the skill level in this case is two right this is one and this is two so we want to know that value we've put that into a variable right here and it's going to be a long variable so if we're using remember we're going up this is the one that's going to increase it if here the skill level is greater than or equal to the mask level then let the user know they cannot go beyond that skill levels cannot go beyond and then whatever the max skill level that way we get something dynamic so that when i let's say i've clicked on this here we go up 1 to 10 we go one more it says skill levels cannot go beyond 10. so that number is dynamic based on a variable if i were to change it to 11 if i add one more in here we can now go beyond that so now simply here we can go increment at one so now we can go on so now it says skill levels cannot go beyond 11 so very very handy very very helpful so it's completely dynamic based on the values you enter into that and that's why we have it there so that's why it's called the dynamic skill matrix so we also assuming that it is not then what i want to do if it's beyond i just want to set the x and i want to set the active cell value to the maximum skill and then what i would say is act a cell value assuming that it is not let's see let's exit out of this one exit sub all right we've set them and we've set it up we don't need to increment it anymore we can exit out of them we set it to the maximum now what if it's not what if it is less than or less than the max scale then i want to increment the active cell value it's going to be the active cell value plus one we're incrementing at one right if it's not up to the max we can simply increase it until it gets up to the max so that's very very easy okay so it's going to be exactly the same for the dial exactly the same except this time what we're going to do is we're going to want the minimum skill and the minimum skill is going to be the application worksheet function minimum based on the level range so i want to know the minimum number based on that range in this case one okay if the skill level is less than or equal then they can't go below the minimum skill cell view and then of course we're going to exit the sub so we have that and now all we do assuming that it is not at the minimum level we can just reduce it by one okay good that's great great so that we covered that but what about we also have to remember now we've got some more macros we have a macro that we're going to come up next called adding the skill now if it goes beyond that we can't add a skill beyond that or maximum of 20 skills can be added right i want to go beyond that but for projects that have enough we need to check if it's not the maximum i want to make sure that they can add it of course if it already exists they cannot add it so for example if i try to add this it's going to say lighting has already been added but if i added another one we can add it here so it's going to let us add it so how do we do that we do that through macro and the macro is tied to this button right here called add skills so if i find the macro we see that's called project add skill that is the macro that we're going to cover right now called project ad skill so inside this macro what i want to know is i want to know the last skill column how many skills do we have so we're going to set the skill column is going to be the first available still column so this is 7 8 9 10 this is going to be 11 right the first column is going to be available it's going to be 11. in this case what i want to do is i want to put that into a variable so the skill column is going to be range a6 and left this is going to be the last column with a value and then what i want to do is add one on to that so that would be the first available column to the right even though it says to the left we're moving to the left of this range right to the left of that range that's going to give us the first available column to the right then if the skill column is less than a 7 then just set it to 7. we want to set that minimum skill column to be 11 right so i want to make sure that if we have a project with no skills at all and we add one we need to make sure we're adding it to column 7. all right assuming that we do have the minimum then what i want to do is if the scale column is greater than 26 then we need to let the user know that the maximum skills have already been added so a maximum of 20 skills can be added then exit the sub nothing we can do we're setting the maximum also what i want to make sure z5 is empty make sure to pick select a scale we can't select if this is empty z5 is empty there's nothing we can do so we want to let the user know to make sure that they select something inside z5 of course this is the data validation based on the skills that named range that we have here is just the data validation here based on skills relatively simple okay so now what i want to do is i want to check to see if that skill exists if i try to add it again it's not going to let us i don't want to add the same skill so what i need to do is check that range and i'm going to see run look for a find if it's been found then let the user know so we're going to use the find command so setting the found scale this is a range based on all of the contents between g6 and z6 what we're going to do is we're going to use the find i'm going to find whatever's in z5 and i'm going to look in excel values and actual whole if it's been found then it says if not found is nothing these cancel each other out meaning it's double negative so that means it has been found if it has been found that skill already exists then what we want to do is let the user know this skill has already been added to the project and then what we're going to do is we're just going to select it let the user know which one selects so it's kind of kind of like let's say we have a let's let's say let's say i add some more skills here and uh so i want to let the user know exactly in case there's a lot it's hard to find right so now we've added it so let's say i want to find one i'm going to try to add welding it's going to let me know and then what i'm going to do is i'm going to select volume so that kind of pinpoints exactly let the user know yes it's been added and selected so that it highlights the one that's already the duplicate and we do that through here row six found the skill range so whatever column it's been found on select it once we select it that macro is going to trigger and it's going to automatically add that shape and the conditional formatting all that okay great so that's it so once it's been we're exiting out right there's nothing we can do if it's already been added assuming that it hasn't been added yet all we need to do is add it so inside the cells the skill column the values can equal whatever's in z5 this is going to add a skill once it's been added as i mentioned do before i want to save it right away and load it right away because that way it's going to load all the information so i'm going to run the macro to save it and then run it's going to load it and what that's going to do is automatically as i add it inside there assuming that we have employees here let's add a skill here i've got employees so let's add a skill let's add wedding to this so what i want to do is loading that saving and loading it's going to odd remember when we load it it's automatically going to add the levels based on the employee so that way the macro as soon as we save it we add it it's going to bring in that data and that's what i want bringing in that data okay that's it that's all we have to do what if i want to delete a skill when i select something remember we show how to display this but what if i want to delete it well that's going to be a macro that we have here and how do we do that well the first thing is we want to make sure that we've selected a column that column that we've selected is going to be stored directly inside b7 so i want to make sure b7 contains a value so if i select it it's going to automatically so that's the first thing we have to make sure with the skills matrix if b7 is empty that means we have no selected column right we can exit the sub out if we do we're going to put that into a variable called scale column then what i want to do is i want to clear the contents of row 6 which is the name and clear in the contents and also what i want to know is has it been saved yet already most likely it has because soon as we add it so what i want to do is find that database row here and i want to make sure to clear the contents out here so i want to make sure here located in 100 h100 in this case i want to clear it out if this particular skills matrix row 100 the skill columns if it does not equal empty that's already been deleted i want to delete that database row so we're going to put that into a variable called the skills database row right if i've just added it and i need to add delete 52 if i go into project skills i want to delete 52 so the best way to do that here is to get that row put it into variables so that i can delete that right so if i do this i want to know that 52 i need to know what road to delete so as soon as i click here and click delete and then we go into the project skills we see that 52 has now been deleted so we need that 52 and i need to put that inside a variable then all we need to do is just delete it this is what we just did project skills range the database row colon and the skill date delete so this is going to delete skills database row delete skills database row okay so we have that now what i want to do is i also want to make sure we're clearing the database from here i want to also clear whatever values are in here inside row 100 and the column we're clearing the contents that we're clearing that database row that's all we need to do then what i want to do is i want to hide that button right i don't need once we've deleted it i want to make sure that this shape here called delete skills button is hidden so we're not deleted we're going to hide it using mso false so we're going to hide that i also want to clear b7 that selected column should be cleared out b7 is collected so if i have a large one i want to delete that i want to make sure that b7 gets cleared out so we no longer have that selected column i want to and that's what we did then of course we just also saved the project and load the project again that's going to refresh all the data all right great so what about getting some unique teams right if i add a team remember we added a team before let's go ahead and click here here right notice we added a team here so i want to make sure that we have a macro that's going to actually get those unique teams if i decide to rename this or create a new team i want to get some unique teams if i have a let's say i have a let's okay here we go got a few employees on this one i want to create a new team i want to automatically have a macro that's going to get that name let's just say we call this framing framing team okay and i want to give it and i want to create a macro called save as a team i need to automatically update this named range with the word framing right i need to make sure that that's updated here so how do we do that we need a macro that's going to do that now if we take a look inside the employees we have a bunch of teams here so what i want to do is i want to get a unique list of teams and i want to place that unique list directly here and i want to create a named range based on the results of that called unique teams so if i look in formulas and name manager and look in teams we see that we have a dynamic named range based on the results of that filter that's going to be without a criteria so we have that so that's all we need to do is create a macro that's going to automatically look through all the employees get the list of unique teams and place it directly into site column ae so that's we're going to do here so with the employees we're focusing on that the last row is going to be a we're going to run if it's last row let exit sub if there's no data i want to clear any criteria on this sheet this is very important because when we create other types of like let's say we have criteria framing right if i do that this is a criteria here it's going to get in the way i don't want a criteria here so we want to make sure that when we're creating an advanced filter without a criteria notice this is empty this is an advanced filter make sure you delete the criteria first using names criteria delete if that criteria doesn't exist on the sheet it will create an error so therefore we wrap this in on air resume next and on error go to 0. that's how we ensure that gets deleted now we're ready to create our our advanced filter starting in e2 just call it me all i'm focused on is teams which is column e so i'm going to create just me only column e and i want those results of unique and i want to place them directly inside ae2 and that's all we need to do unique equals true very important here because i only want those unique that's all we need to do so i just need to run this macro only when we add a new team and that's exactly what we're going to do now project add employee or team we're going to add this one we're focused on adding a team remember we have the ability to add a specific team if i want to go in here that team that i just created called framing team and i want to add it all i need to do is click add it's going to add those same members to that so how do we do that how do we add a team to that just with a single click of a button well that's with a macro called project add employee or team now we're going to use the same macro for ad employer team if i want to add a single employee i can do that here name and notice how the drop-down list changes now we can add a single employee did you see that it looked kind of weird it was fast if i change this from name to team the data validation list notice it looks at its teams it changed from teams to employees so how do we do that well that is based on a change event i might make a change to f3 based on the value team or name i want the data validation to change from names or from teams it allows us to create a lot of functionality you know just a few different cells so how do we do that well it's going to be based on the change event based on f3 so let's take a look at that inside our skills matrix and we're focused on the change event of f3 add employee by change so if the user makes a change to f3 then we want to do something anytime we change validation like we're doing here in e4 the first thing we want to do is delete the validation and also delete the contents of that cell so to do that inside e4 validation delete we're deleting the validation and what i also want to do is clear the contents so that's kind of first thing so regardless of whether they've selected team or whether they've selected name we're going to delete the validation the next thing is if f3 equals team then we want to do is add a unique value i'm going to run the macro here's that macro we just went over called get unique teams this is going to run a macro that's going to update that update those teams based on that so as soon as we change this to team it's going to update the macro so we have an updated macro that's going to make sure that the team names here are automatically updated so once we do that then what i want to do is add the validation it's the same thing we don't need to do that so it's the same thing as if we were going to data validation data validation here and basically i'm going to do this through i'm adding teams here right both based on both of those teams right these teams here and the air alert information or whatever you want on the error alert so i need to add that to that but we're going to use vba to do that so e4 validation we're adding a list type of validation alert style can be anything on what you can do informational or stop formula is equal to make sure it's the equals make sure you're adding that equals and the teams which is the named range else employee adding staff and split employee name we're adding individual employee names in this one in this case we're going to add the validation the same one alert stock can be the same or different formula equals employee name this way we're adding employee name lists so when i select name here instead you will see that the validation will change inside the data validation here and it's going to be changed to if we say the list employee name that's how we do it now all we need to do is just have a single macro that can add an employee a single employee based on the name selected or we can add that and i want to make sure that if we add if the name already exists like it does let the user know the employee already exists so that is the next macro that we're going to go over okay so let's go back in here let's make sure we finished up that here at employer team so this is called project add employee or team that is the one that i want to go over you know so if we look in here let's make sure we finished up everything on this skills matrix here so we just added two valuations so this is all we need to do for adding those evaluations just simply basically clearing out the validation we're adding either the team validation or reading name that's it that's all we have to do here inside our project work now what we want to do is add employee or team add employee or team that's what we want to go over how do we know first thing i want to do is i want to see if f3 is empty i need to know are we adding unemployed name i want to make sure that f3 is not empty because we got to know what are we going to be adding are we adding a team are we adding a name so first thing f3 checking into f3 make sure that's not empty please select add employee by option okay so we're just going to tell the user to check that exit sub okay so what i want to do is i want to get the last employee row whether we're going to be adding a team or whether we're adding an employee i need to know what the last row is and i need to know the first available row in this case 13. okay so the last employee row is going to be the last row with value set the last employee row oh it's going to be first available row okay so we have that now what i want to do is if we're adding a name if we're adding a name here and but however if it's f3 however if e4 is empty e4 would be whatever name we're trying to add right then like for example if i try to click name here and it's empty i want to let the user know hey please select an employee there's no name to add so that would mean e4 would be empty so if e4 is empty let the user know please select employee tab okay so exiting the sub we can't add anything if they haven't added a name all next thing assuming that they have added a name i want to check for a duplicate name does the name exist in this list already i can't add a name like you said before if fred already exists we need that note to come up and let us know that fred's already been added so to do that what we're going to do is we're going to set an employee range we already have this defined as an as the range we're going to set that founded player range going to be based on the e9 through the last employee row we're going to find i'm looking for that i want to find whatever's in e4 i'm going to look in this range here and i'm going to look for this e4 if it's found in this let the user know how do we know if it's been found if not found is nothing remember the two negatives can't shoot out that means it has been found the employer already exists within the list below exit the sub out we won't allow them to put duplicate employees in but if it's not then all we need to do is take whatever's in e4 and place it in the first available row inside e and the column that's it add employee to risk else if that would be in team if it's a team f3 is equal to team in that case we need to do also again the last row in this case i want to know the last row of the employees here i'm adding a team right in this kit i want to know all the employees of that team how are we going to know well with the project employees if we're adding a team i want to know the last row i'm going to run an advanced filter and i want to know all of the employees based on whatever team that's selected how do we know what team well if i select team here and then i select split any team here such as framing then i want to set this should be the criteria framing so if we go back in employees we see we have a criteria here based on a formula based on the skill matrix e4 that is our team so we have our criteria then all we need to do is simply get the results here and our results are going to be here and then all we have is a total skill rating so that's how we do it so all i need to do i'll go over the skill ratings soon so we're going to determine the last row if it's less than three exits so we're going to run an advanced filter so we can extract all the employees on that specific team all the way from a2 through e we're only extracting the i only want to know the employee name and all the way through e i only need this information i don't need the rest so our original data is simply going to be from a2 through e and down okay and then what we're going to do is we're going to have those results come in all the way from a h all the way through a i that's all we need to be concerned about here so our results here are going to be a h through a is where we want that results ah ah3a is where we want them to come out let's just update that ah2 through ai2 right we want to make sure the criteria range should be af af there we go af3 that's what i wanted and the results here are going to come into ah23i so the criteria simply is af2 through af3 that's our correct criteria then all we need to do is get the last row based on the results that last row is going to be column base and column a h the last results row is less than three then exit sub there's no results then all i want to do is bring this information here and bring it directly inside here bring it right there here and of course this can be based on either the rate or the stat position based on whatever the current formula is so that's how we do e in the last employee row and e in the last employee row plus the last results row minus three basically the total rows i need to know the total rows here so the last employee row this 6 minus 3 is going to get us our total rows here so that's all we need to last minus 6 because that's going to get us so we know that it's 6 so i want to bring in the basically four rows of data bringing it directly into here and adding it on so if i wanted to add this team again or something like that or we had a new project let's say we add a project here and i want a different team i can simply add the different team a different team like this and add them to okay so that's how we add the teams that's easy and then of course saving the project and loading the project back up that's going to bring everything it's going to add those teams so adding employees or adding teams very easily with the click of a button now what if i want to remove employees what if i want to take this and i want to remove this employee and remove this employee how do i do that we've got a macro that's done sign that i need to know of course which selected row i need to know that we've selected pro employee row in b8 so we need to make sure that we have that row and also want to see if there's a database row here located in am so we need to delete the employee from the project so that's going to be the macro that's been assigned to that shape called project remove employees so we're going to focus on the skills matrix we need to make sure that we've selected a row b8 if that's empty we need to let the user know to please select an employee to remove and if it's not empty what we'll do is we'll add in that to a variable called employee row what i'm going to do then is determine the database row making sure that's in column am in the employee row that's the database row as long as that's not empty we can put that into a variable called project employee database row then all i need to do is delete it so basically if i want to delete this employee i need to extract that row 84 here and that's going to be in the project employees down here in 84. so i want to remove that mark mason from that one then i can know i've got to know the row to know which one to delete so if i click delete here at a mark it's going to make sure to clear that row out notice 84 has been clear so i need to know that row that row is going to come directly from 84. and then all what i want to do is i want to hide that button and i want to load the project again so deleting employee here we have one deleting it and then we're going to reload the project and then hide the button that's all we need to do for removing employee okay cool just macro now remember we have a really really cool macro right here called add the optimal staff let me remove this and so let's say i've got three different particular skills to this so we've got three different skills here now i want to know how many staff let's say we want to have five staff on this i'm gonna save that project so i know that we need five staff but i want the five best staff for these three skills i'm going to click generate optimum staff and it's going to automatically generate the five best staff based on that so how did we do that well the first thing i need to know is who is the best staff for these three skills right i need to know who's the beta based on their ratings so we can do that with a formula in fact we're going to use a formula so inside this last column what i have is called a sum product formula and basically what i want to do is determine which staff i want to load the total skill rating based on alt which is 23. if we take a look inside these three skills here i should use them in order here let's take a look inside the skill match so we have sanitation plumbing and welding nine six and nine right so let's take a look at the first employee sanitation plumbing and welding inside the employees so inside this here if we take a look in plumbing and welding we have three of them so i should use them in order a little bit so here we've got plumbing eight sanitation nine and welding six so that's 23. this is pretty good here actually sanitation right so i want to know sanitation 9 welding 6 that's 15 and plumbing is 8. so 23 i want the total score this is a great formula it's based on only those particular skills that are listed here based on this range right here so for all the skills i need to know the score for that employee in this case the employee has 23. if i add another skill it's going to change the dynamics of that right if i add lighting it's going to change that now if we look back on employees now it's 33 right because his score for that matrix of lighting has a 10. so if we look inside and we look inside lighting we see 10 so it's been added so we know that so what is that formula so basically we're going to use sum product and we're looking for is number right i want to turn these into ones and zeros that's why i use the double negative and we're looking for is the number i'm going to run a match right this match is going to be based on h2 so the lookup value i'm going to be looking up is from h2 through aa2 i'm looking up for all of these skills based on this header column starting in h2 all the way through aa2 so all these skills and what i'm going to look it up and i'm going to the array that i'm looking it up is based on the arrays from g6 to z6 this is our skills range inside the matrix and i want an exact match okay once i have that what i want to do is i want to total it based on the array in h3 through aa3 these are the scores that are located in that row a h3 through aa3 right in that particular row row 3. notice the rows don't have the new dollar sign right so that way when i drag this one we can use this for every single employee but notice these do are absolute we have absolute here and we have absolute here but this last one is going to be we want to sum based on whatever row we're on and what that's going to do is going to produce the total it's going to basically count or sum all of the scores for only those skills that are based on this range here a great formula once i know those totals i can then run the sword in vba so then all i need to do is just bring this formula down here and it's going to give me the totals for every employee once i do that all i need to do is then run a sort excuse me run an advanced filter based on that so now i what i want to do is i want to know all of those and i want to sort based on the skills so if i then have all of our employees come here including their position or amount then what i do is i sort run a sort inside vba based on the highest score all the way to the lowest score and if i know that i need five employees for the particular job all i need to do is pull those five first employees and bring them directly inside here or however many employees you have and bring them directly inside here and that's just what we've done so from jack all the way to greg and bringing all those employees in here from jack all the way to greg bringing those employees directly in here so that happens with just a macro relatively easy once we have that formula so first thing we want to do is know the last based on the last employee row if the last employer is greater than eight we want to do is we want to remove any existing employees we want to let the user know i don't want to do this if there's already employees to load so i want to make sure to let the user know if there's existing plus please remove any existing points or create a new project and add optimal staff so this is really for new projects or projects that don't have any staff associated with them and we can do that for you this project we have all of our projects so we can create a new project too but so that's how we do so we just want to make sure that there's no current employees selected and also what i want to make sure is if w4 is less than one we want to make sure to please add one required staff how many staff are we going to add well w4 is going to tell us that we need to make sure that this has a value how many stops should we add 6 5 or whatever so we need to put that in here also there we go i want to make sure that g6 is not empty what is in g6 i want to make sure that there's at least a skill this should be g right i want to make sure that g6 is not empty there has to be at least one skill right we need to know so let us know please add at least one skill before generating the optimal stuff okay assuming that all those conditions have been met we can then run it we need to know the staff quantity that's going to be based on w4 how many staff should we be adding now we're going to focus primarily on the employees again we're deleting a criteria and why are we deleting it because i'm going to run an advanced filter and i want those results to come here inside here but i don't have any criteria i want basically all the employees that way we can keep the original data the same and we just need to run the sort so i'm going to bring all the employees no criteria so to do that i want to make sure to delete any existing criteria i'm going to determine the last row running an advanced filter from a2 all the way through a b no criteria no this is blank no criteria we're copying it to ah2 through aj and we want unique results oh the last row is less if the last results rose less than three then exit the sub although it shouldn't be okay now we're ready for our sort remember i said we want to sort based on the total skill rating so we need to clear any existing sorts and we're going to add a key based on aj3 and i'm going to sort on those values and sort descending i want the highest to the top and the lowest at the bottom sorting on normal then we're going to set that range to ah3 all the way through aj and the last results we're going to apply that then what i need to do is i need to bring in the data but not all the data i only want to know if it's based on you know six or however many we did i want to bring in that data based on the quantity of staff that are required the top x quantity so e9 through f that's going to bring in right here e9 here through f and all the way down but how many down based on the staff quantity it's going to be based solely on that so e9 through f9 right plus the staff quantity minus one that's going to give us the total stat that we need to bring over and then what we want to do is a3 through ai and 3 and plus the staff quantity minus 1. and that's going to bring all the staff over okay great so now that brings all the staff over then all we need to do is simply save the project and load the project that's automatically going to bring in these values for that that's it that's all we have to do to automatically generate optimal staff based on their highest minimum based on the highest score and that way we have this automatically done okay great but what about save as a team remember i said when we have a team if we have let's say we have a few projects we want to save this as a team we want to create a special team we saw that work a few times clicking this button is going to save these employees as their own unique team so how are we going to do that well with this macro right here called skills matrix okay so for this one what we're going to be doing is we're going to focus on i want to make sure that f5 equals team name if it equals team name or it's empty right if this f5 equals team name which is the default that we don't want that or it's empty we need to let the user know to make sure that there we have added a t name please select the team name on that have to put in a team name on that so then we just let the user know and then exit the sub out assuming that they do have added a team name we're going to select it here now all i want to do is make sure that they want to update right update all of the name team names for those particular employees so we want to do that with a message box if message box are you sure you want to save all the employees to team and then we'll put in whatever team name it is we've already defined that variable here in a string variable yes no if it's no then we're going to exit the sub we just give them a chance to exit out of it assuming that they do want it we're going to set the last employee row i need to loop through all the employees right the last one being 13 i need to determine the employee database row and inside this database row once we have it we're going to go inside the team name which is located and call me and make that a duct date so that's what we're going to do inside the code so we're going to run that loop 49 to the last employee row we're going to get that if for some reason that employee database is empty if it does not equal empty we know we have a database row we're going to set the employee database row to whatever is in a n we're going to update column e of the employee database we're going to update it to that new team name and we're going to go to the next so we're going to loop through all the employees to do just that and then what i want to do is i want to run that macro to get unique teams right once we've updated that getting that in unique teams running that to make sure and then what i'm saying if f3 equals team then i want to set that team name therefore i just want to make sure that if they're created i want to kind of let them know and say okay that team name is now located right here right so i save it as a team name so let's just do this let's just call this plumbing maybe we have a plumbing team and we want to save that as a team i want that team name yes i do want to save it and it's going to put that plumbing directly in here so we see that and now if we look under each of the employees we have plumbing plumbing plumbing plumbing okay so it's already been saved up so we now have that we've also run the macro so we have that plumbing here we can do that all right so now we've updated that team so f3 is going to take on that team name f5 is going to be team name set the default text back right we want to set that default text back and that is it that is all we have to do all right what an incredible training this has been we have learned so much we've gone over lots of conditional formatting we've learned how to save a project add new project delete a project basically we've learned how to add and remove employees or teams how to create brand new teams how to load projects use conditional formatting how to automatically generate the optimal staff using a sum product formula and clicking one button and adding those staff based on the staff we've had adding skills finding skills removing skills and how to use this and of course conditional formatting inside the table and outside the table it has been a really incredible training thank you so much for joining me on this training i do appreciate it if you do like these trainings great ways to support us or joining our patreon so much going on there you won't want to miss those updated trainings and recordings even on this one i'll be doing one next monday if you want your features added on to this join our patreon make your suggestions and i'll be making those updates accordingly all right thanks so much and we'll see you next week [Music] you

Channel: Excel For Freelancers

Views: 93,535

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Skills Matrix, Free Skills Matrix, Free Employee Skills Matrix, Excel Skills Matrix, Excel Employee Skills Matrix, Excel Matrix Skills, Employee Skills rating, Employee Skill level, Staff Skills Matrix, Excel Skill Matrix, Create Skill Matrix, Matrix Skills Excel, Project Matrix

Id: rVQUNZ_zW9Q

Length: 138min 11sec (8291 seconds)

Published: Tue Nov 09 2021