How To Create A Dynamic RACI Matrix In Excel To Manage Unlimited Projects & Tasks [FREE Download]

hello this is randy with excel for freelancers and welcome to the dynamic racy matrix in this week's exclusive training i'm going to show you how to build this incredible racy matrix right inside excel complete with the ability to add and update projects filter projects of course add any number of tasks along with any number of category and due dates along with status and add employees to your racing matrix we're going to go step by step over every line of code formula and format i cannot wait so let's get started all right thanks so much for joining us today i really appreciate all the time you put in to your excel skills i try to bring you something each and every week something special if you do like these trainings please don't forget to click the subscribe button and also the notification icon bell that'll ensure that you get these trainings each and every week delivered right to you for absolutely free so what is a racy matrix or r-a-c-i we'll call it racy for short i don't know what other people call it well basically it is a charter race chart it's also known as a racy matrix and sometimes also referred to as a racy model it's a diagram actually that identifies the key roles and responsibilities of users against tasks within a project so i've got a little bit of a chart here just to show you a little bit of legend here to help us to understand what are the four major parts of the racing that's what it stands for raci the first is responsible those that are responsible are the individuals who do work with the complete task right those are the ones that are actually performing the work they're responsible for the work what about the a the accountable one well that's the one person now there's just a single person that becomes accountable on that task and they're the one that make sure they give the final review they are ultimately accountable for the performance of that task and getting that task uh done and making sure that it is correct so there that would be a manager supervisor also consulted that's the c those people who are providing input into the task in the better not necessarily directly involved they're not necessarily doing the work but they are providing input so it could be someone that's more of a consulting role that's why it's called consultant there are also other people that need to be informed but they're not performing any of the work and they're not being consulted on that in that case that is the eye of racy informed as the people who should be kept in the loop on a task but do not offer feedback or taskbar so we're going to develop this racy i'm going to show you everything i did to create this every formula every conditional format every line of code how we got it all to work out and then what i'm going to do is i'm going to add some email automation onto this so that when we do have different ones when we do decide to change those roles or update those roles it'll automatically create an email that email automation i'm going to put that inside our patreon platform we've got an incredible platform so many and bonuses so i hope you'll do join us for that patreon next week what i'll be doing is i'll be adding email functionality on to this there so make sure you get on patreon for just a few dollars a month and we've got so many we've got the pdf workbooks where we can show detailed pdf documents of every piece of code that went into that training along with tons of other perks and documents we also have downloadable videos we've got updates and tons more going on on our patreon so i hope you'll join us there all right let's get started with this training because i've got so much to cover so basically what we have here is a task here a task these columns deal with tasks so we have also our task categories so for example our category here is a proposal so these specific tasks fall into the proposal these fall into the inspection if i were to select one of those it's going to put that task name that category the progress that we've made and also the due date we can then change the progress or the due date and then of course we can also save it and it's going to save it it's automatically going to update that accordingly we have a selected line we can delete any task here if we want to so in each task and then for each employee we also have the ability to add and remove employees accordingly so what we want to do is we want to set that employee for that specific task we want to give them a specific role to do that all we need to do is select on a cell and give make sure let them know that that person is responsible or maybe they're accountable now keep in mind you could only have one this is why a racy is so powerful because we can only have one person that's accountable but we do need to have one person that is accountable at least one person is responsible if we don't then we have a problem also we can have a single person who is not only responsible but accountable as well so for example this one notice there's no responsible we've got some required notes it's going to tell us it's going to help guide us and let us know what is required for this task in this task we both need somebody who is responsible and required so if i add somebody who's responsible now we only need somebody who is accountable right so when i add them accountable a person that is accountable then this goes to green here then roles are complete now if i decide to add one more person accountable which we can't have because we can only have one accountable then it's going to let us know that we have multiple people that are accountable so that's a red flag we cannot have that but we can have more than one people that are responsible doing the actual work so we can do the roles complete here all right great so we can also have the same person that is also accountable and responsible so if we decide we want to clear this roll out and we notice that we're the role is not complete here we'll i'll update that and but we do want to make them responsible we see that they're now complete so the roles complete i'll change that text that needs to be updated a little bit so we didn't have somebody accountable so we need that accountable right if we only have that person who is accountable we need them accountable so what it's going to do is going to keep track of those particular tasks and it's going to let us know who is accountable who is responsible could be more than one people person that is responsible and of course who is being consulted and who is being informed like i said this works really well with a project management system so we've built this racy around a project manager now this is very very similar to something we created just a few weeks ago called the skills matrix in fact i put it on the same workbook because this is what it was that we created if you didn't catch this video this lets us know what kind of skills that we have on a per task basis are per employee so we can increase the skills i did this complete training but it is now in the same work bug way so you can download this workout free using the links in the description of course if you want a ton of workbooks like 200 of them for just 77 you can also pick that up okay so this dynamic skills they're now in the same workbook because there's a lot of similarities so i wanted to make sure that you could see the difference and have both in one workbook so you can compare and contrast those both many companies will use both as skills matrix and erasey so they can come in hand in hand and that's not necessarily either or all right so we can also create a new project we can also load a project on selection loading different projects we can also filter projects based on a status we can filter project based on the product on the project type so if we have only commercial we may want to only load commercial types okay so we understand that we've got our pro our listed projects here we've got our listed tags here we can also add employees for example this particular project doesn't have any employees doesn't have any tasks associated with it so we can add any type of employee but if we try to add the same employee twice that should not be allowed right we can only have one employee on it we need to let us know so we know we've already added fred so we can add a different and we can also add tasks now notice we cannot select there's nothing to select here it's not going to appear here we can't assign any roles to this because there are no tasks also we can i forgot to show you this one we can also show based on position or based on the name okay so we have that but we do need to add a task we need to add a task out or we can select task categories or types here and then we can just put meeting here and we can we can add a status if we want and we can add a due date if we want so there's a lot of features in this so we're going to get to it and we're going to save that task so it's going to show both our category and then the meeting so if we add another one under that category or we add another one so let's say we want to do a new one so now we can have client call and if we add it into the same category it's going to appear under the same category and then we'll make this pending here and then we can save that task and automatically save proposal suspension so now we have both of them there so it's going to keep adding it based on categories and then sorted accordingly so we can save and task we can of course update our project information here if we want to update a project description we can do that here too project description and then we can just save that and it's going to automatically save so when i go to another project and then back it's going to load in all that information and it's going to keep so how do we do this so let's get go over a high level overview and then we'll get down into it so in order to make all this happen what we need to do is we need to make sure that we have our data stored separately on separate sheets i need one sheet for a project another sheet for our employees another sheet for our tasks and another fourth one for our racy roles here so let's take a look at some of those so we have an employee list here this is the same one that we used for our previous one we have an employee id and position and we rates and teams we're not using in this we use those our skills matrix and also we use an email which we'll use a little bit later on in our patreon update and our pictures we're going to use so in this training we're really going to focus on id's name position and pictures that's the part of the table we're using we also have our projects now there's a project we have a project id a name a project type days we will not be using in this specific training manager which we do have status we have and description we have so budget and required staff and days we are not using in this training that we did use for our skills matrix training all right so we have that together and so that's basically it for projects relatively simple and i also want to know which employees are listed on which projects so we have what's called our racy employees so we've got a project id so i need to know what project those the name of the employee the position of that employee and also the picture of that employee that's going to come in through a formula and so how do we do that like if the employee position changes i want to know so what we're going to do is we're going to have a formula we're going to bring down that formula and that formula is going to bring down for every new employee we add so that the architect directly comes from our employees it directly comes from this position here we're going to pull it in okay and we're going to do the same thing with that picture i want to pull that picture in now this picture is just the picture name right when we combine this picture name with a specific path it creates the full path for that picture and where is the rest of that path it's located in our admin folder here now we have a brand new one called task types here we have that here task and type category so i've added that in and that's going to be with the named range here and let's take a look inside that name branch and it's the name manager and we have a brand new one called task type so if we scroll down here right here it's called task type this is an offset it's going to encompass all those tasks types so we can change it and update them however we like for all of our projects so we can create additional dynamic task types okay here is that employee picture that i wanted to show you this is where the employee pictures are now make sure when you get this and you want to update it and we have a list of employees now if you want all these employees along with the icons here i'm also providing that of course inside our patreon so we've got a list for all the pictures here and we also have a default so the combined path this path combined which is the same path that's right here that path is the same path combined with our employee picture name here will have allow us to display those employee pictures right here so we're going to have them displayed right here which is a nice idea so we have that inside that so that's why we have our combined picture so it is that what i want to do is i want to bring that inside our racy employees are she called racy employees here so we're going to use a formula and i'm going to use a formula basically what it's going to do is i'm going to look up this name it's going to look up the name once the name is found it's going to match it based on the employee name here it's going to give us that row and what are we going to do we have a named range called employee position i want to index that employee position we're going to use the column that employee position is just another named range on our employees here it's called employee position this is a dynamic and i've got another one for employee picture here so let's take a look at the next one the next one also again we're indexing in this case we're going to index employee picture we're going to use the same match we're basically on the employee name and it's going to bring it down that way every single time we add a new employee to a new project is going to bring down those formulas i don't want these formulas here because i don't need unnecessary formulas it slows down the workbook i only want these formulas here when i add the rows so vba is going to do that for us it's going to do the hard work the heavy lifting is going to take this formula it's going to bring it right down here as we add in the project id as we add in the name as we add and then how does that work let's just take a quick look so i can show you all i have to do is just select on a project number five there's no employees here if i add an employee here clicking on add employee and then we go back into the racy employees we see now five dave carpenter brought the formulas now been brought in we have the here number two is the employee id and the database row the row that's associated with this so that's how we do it so as we add employees to our racy it is then going to add them to that list so we can bring that back when we load them in all right so that's all we need to do there so we just need to store it here that'll come in handy we'll run all we need to do is to load it it's just we run that project id through an advanced filter we return our employees and then we bring them in through macro okay so that's our employee id so we have project tasks these are the tasks that are associated now notice we have again we have task id which is a unique id we have a project id so all when we do it let's say we add a task to this one so let's go back into the racy and we'll add a task we'll call this test task so we know exactly what it is we'll give it any kind of a category and we'll give it a status of pending we don't need a due date we're going to save that task it's going to show that task right here we can now add our racy role here and also if we look in the project task we see we have got a unique task a brand new task id 25. we have that project id which is project which is five we have that our task name we have our category our status we didn't put a due date on and we have the database row relatively simple so you see there's a lot of similarities in the way we then we have our racy roles this is our roles this is where those roles that those roles is basically this data right here this data right here this r a r a r c so it is all this data here this data here i need to store somewhere and in this case what i need to know is i need to know a few things right if i have a role here that's associated this responsible role is on fred i also need to know the project id number that project id is gonna we're gonna go through that in just a moment that project is number one here so i need to know the project id i also need to know uh the individual task id so we need that that task id because it's assigned there's three things that we really need to know about this we need to know who it was assigned to in this case fred we need to know what task it was assigned in this case client meeting and we also need to know if i say in this case once more i'm going to make myself crazy and then we need to know the project id so we need all three of those elements that's very very important so we have that stored inside our racy role database here so project id we have our task id we have our employee id employee and we have the employee name all this is going to help us then we have the actual role what is that r a r c notice our roles and then we have a database row so words are going to store all three of those things we need all three of these things in order to find out our project id is going to let us know what project to put it on our task id is going to let us know what row to put it on and our employee id is going to let us know to what column right so we need all three of those things here i need to know what row in this in this case row 9 this column here column i so that's going to tell us exactly where to place it so we need all of that we can also clear a roll here if we want to clear it okay great so we understand now a pretty much a high level of how we're going to approach this and that's it that's pretty much it as far as where the data is stored so we're just going to focus on those four datas and the idea is to combine all of that data and get it loaded up in here and of course any changes that we make to a task gets saved back to the database any changes they can save to a project gets saved back to the project any changes to the employee if we want to decide we want to remove employee that also must get removed back in the database and the same thing with the task if we want to remove a task we also need to make sure that this task gets removed with that project and we can easily remove the task so we've got all that and of course we want to be able to alternate between a position showing the position or showing the name of the employee so we have that and we may want to sort tasks on based on a status so if we only want to show our pending tasks we can do that if we only want to show in progress i don't even know if we have any we have one in progress so we can also do that lots going on here but when we have a good foundation of data right it's very very easy to bring it in right and we also have conditional formatting so let's go and take a quick look at some of the conditional formatting that we have on here especially this here we've got a lot that we're going to have on here so i want to show you what that's like and of course we have some alternating row conditional formatting so i'm going to highlight this and go into the conditional formatting we're going to take a look at some of the rules that we have based for our tasks and the first rules you then the first rule you'll see is based on the highlighted row so when i highlight a row and that row number that i select for that task is going to go into b8 so this rule is going to allow us to give it so all we've done is just give it a background a fill of that darker blue and a font of the bold and white color font when we do that and our row is automatically on b8 it is going to show up like that and we're going to apply that to the entire row all the way from f through columns ae and if we take a look inside b8 we see the select row is nine so we know that nine is going to be the one so you'll see as we select 10 that's vba it's going to take care of that based on selection change which we'll get into in just a bit okay continuing on with the conditional formatting in those cells we also have some alternating rows take a look at this this one's based on f 8 f 8 is not blank and that means there is a task notice our task shows up in column at f while our categories show up in column e so this one is specifically for our tasks what i want to do then is basically if f8 does not equal empty then i want to give the dotted lines to the left and the right left and right and if we take a look at this here we just see the dotted lines border here we've got that blue dotted lines on the left and the right so these will take care of that now this applies to f8 through g3 f8 and f8 through g3 knot doesn't extend all the way from e or through f because i only want it in those that's how we get this dotted line on all those and then also what i want is alternating rows we notice that there's a light and blue white so when i edit this row we're going to take a look at the two conditions there's two conditions for this blue background what do i want to happen well f8 should not be empty and and notice that it's not just eight it is for every row below that's because eight doesn't contain the dollar sign so it's every row below that and i wanted for odd rows equals mod of row 2 equals 1. that's for odd row so i want to color odd rows that are blue but only when column f is not empty so that's going to do it and i have something similar for the y one so here in the y one this is but this is for even rows it's exactly the same except this is for even rows we format that we see that it's got no fill so but a font and we've got some borders but it's just gonna be that white okay so that's it for that all we have to do on that that's for the even rows and then also what i want to do is i want to color those categories right categories notice how that blue color with a bold so when we edit that rule we know that that's going to be based on column e and then any row starting in row eight and if that is not empty for our categories i want to give it that blue color i want to give it a the blue underline and i want to color it in bold okay so that's how we get our categories something very similar here also what we're going to do is selected row here but this of course is going to be based on our selected project which is 10. so we've got the same conditional format and the same rules here for alternating when we have that so that's our conditional formatting i also have a little bit of conditional formatting here i only want to give borders to those cells that contain both employees and tasks so if we look in here and can do conditional formatting we manage rules we see we've got several rules up here and it's going to be based on the value of course the roles that are being placed and whether they contain an employee or task so let's take a look at these two conditions i want to give it let's edit the rule and let's look at the format i want to give it this border this bottom blue dotted line border but only in two instances when f9 and notice that nine doesn't have a dollar sign that means going to be any row starting at nine and also i through six now notice that there's no dollar sign before i it's not absolute that means for every column that it applies to but only on row six this is for our employees notice that our employees are all on row six starting on column i and going forward so that's going to mean that any that means that we have to have an employee in row six we also have i have to task in column f if we have those things then i want to give it that border that blue dotted line border and very very similar here when we edit the row we see the same rule here but in this case what i want to do is i want to give it that right border now this is just a little bit different so i wanted to make sure that we have separate ones so that they're separate so i can show you that it's a blue border great so we showed us that that's basically this is going to be i6 notice it's i6 a little bit different and then f9 okay also what i want to do is i want to give colors if it contains i we're going to give it this dark red and white font if it's c for consultant i want to give it the green red is going to be for a and then r are the people that are doing the actual work of course those who are responsible are going to get that dark blue and then if the same person is going to be doing both responsible and accountable which is the ra i want to give it this faded from blue to red so all i need to do in the fill and the fill effects is go from blue to red and then make sure that it's vertical that's the way i want to show for those who people who are both responsible and accountable which is the ra so it's equals ra and that's all i have to do with conditional formatting and now we also want to know again the highlighted row which you saw that and then the category row so that's it so the other ones are just continue with the category and the continuation of that highlighter row so that's it for the conditional formatting also i've got some roles here some rules here so let's take a look at this red flag how do we do that it's a little bit more of basically what we're going to be doing is we're going to use some product in this and some product is going to tell us i need to know if what is found inside that right so basically we're looking for it's irrelevant as far as those being consulted and those who are being informed it doesn't really matter there's no rules that apply to those two but there are rules that are applied to responsible at least one person is required to be responsible and only one person and one person should be held accountable so if we have a missing accountable that should come up as a red flag if we have a missing responsible that should be a red flag if we have just one of those it can be a yellow flag notice we have just someone responsible but we don't have anybody accountable so that's got to come in as soon as we add somebody accountable it's going to go to the green flag so how do we do that well first of all we're going to make sure that f14 if f14 is empty then just show empty right we actually have to have a task so i want to make sure that there's a task now we're going to use the sum product we're going to use search what i'm looking for is i'm going to look for the a the first thing what i want to do is look for the a and i want to look where do i want to look for that a i want to look for it in that entire row from column i to column ac on that specified row we're going to add the zero onto that of it when we use the sum project along with is number then we can determine how many are found how many of those a's are found in that if it's greater than one we're going to just put zero here and then that'll come in handy with conditional formula so if it's greater than 1 i want to put 0. 0 is going to let us there's going to be the red flag because we can only have one person accountable so if this is greater than 1 i need to make sure to let these know this is going to have the 0 is going to cause a red flag so also if and okay there's two conditions now the sum of the project we're looking for a is equal to one which is correct if it's equal to one that's what i want i want the number of a's found in that row to be one and also what if we're looking for r if that is is greater than or equal to one remember we need at least one person responsible so if it's greater than or equal to one then we are good this is correct this is what i want i want at least i want only one person accountable and i want at least one person responsible if that is then i want to put a 2 a 2 is going to denote our green flag but what about if it's a yellow flag what would constitute yellow flag two conditions if the number a okay is equal to one if or remember this is an or we're going to do two conditions if a is equal to one and the sum product is e greater than or equal to 1 then put 1. 1 is going to be for our yellow flag and that means let's say we found that there's a 1 in case there's only one a right only one a and no r's then we're going to put a 1. that would be no because if there's an a and r would show up here right and and r is correct it would show up with 2. but i want to know that yellow warning that means there's only one person that's accountable and no responsible or perhaps there's only one person responsible no accountable those two instances so either the instance either only one person is accountable which is that sum product or there is only one person that's greater than one or greater that is responsible in that case i'm going to put a one now one is going to be our yellow flag a one is yellow flag a zero is a red flag and a two is our green flag so now when we escape out of there we take a look inside our conditional formatting for that that's exactly what we are going to find in this icon set we're going to take a look at this icon set here a 2 greater than or equal to 2 is going to be a green flag a 1 is going to be that yellow flag and less than 1 is going to be that red flag so that's how we apply to it now we're going to have very very similar rules to the text here but it's a little bit more because i want to let the user know what is required so the first one again if f14 is empty certainly we don't want to move forward now what we're going to do is we're going to check the sum product we're looking for aids if here we're going to search for the number of a's in that row if it's greater than one we need to let the user know multiple accounts right we cannot have multiple people accountable we can only have one person accountable so that's a warning i want to show mult just like it does here multiple account okay but what about another condition if we're going to search for the number a it's equal to one and this is going to be an and right two conditions and one the number of person accountable is equal to one which is exactly what we want and we're going to look for r if r is equal to zero meaning we have okay we have the right number of accountable but there's nobody that is responsible it's equal to zero in that case i want to put responsible is required right we need to have a responsible person there's another condition what if we find that the search number is there this is going to be another and what if we find that we don't have anybody accountable meaning it's zero we're searching for a zero and we have the number of r's equal to one so we have the number we could put greater than or equal to one in that case that should be fine greater than or equal to 1. in that case we need a count required remember there was one issue with this this fixes it so i'll copy this formula so in this case we have enough responsible greater than or equal to 1 but we don't have anybody that's accountable in that case i want to put accountables required now what about if we have another account if and search a is equal to zero and two conditions one there's nobody accountable right and what about there's nobody responsible also equal to zero two conditions then they're both required in that case we need someone responsible and we need someone accountable the message will be responsible accountable required they're both required we need to both okay otherwise any other condition the roles are complete right so every other we've accounted for every other condition here and that's it so now once we have that formula again all i'm going to do is i'm going to pause the code for a second because it's a selection change when you have a selection change and you try to copy it's going to create so i'm going to copy this and all i'm going to do is i'm just going to paste that all the way down here that one little fix so we have that paste the formulas and that's how we get it down very good so now we've done everything so now we see we this one of course we have only the informed and only the consultant but nobody is responsible nobody is accountable so of course we're going to need to make that change so how do we do that let's restart the code again so i can get that selection change event there and then when i select something here and i can make them responsible now we see here that we have accountable is required and once we select accountable is required and we now have the roles complete so that is how we do it so that's all how we get both of them so how do we continue with this everything else is going to be based on some vba code so we're going to go step by step through all the vba code the first one i wanted to show you is this one how do we get this really cool pop-up to show up and how do we get that action jump well this pop-up is called the assign roll group i've given it and all it is is basically just some just some shapes here just some rectangle shapes here and i've just colored them so there's nothing really going on this of course is just a little icon so there's nothing special with what i've grouped them together make sure that when you're grouping them together you always want to make sure that you are going to be let's i'll share move but don't size with cells move but don't size cells in that way if for some reason we change the width of the columns or the height of the rows that shape will not change so what i want to do is i want this to show up but i don't want to show up everywhere i don't want to show up if there's no task and i don't want to show it up if there's no employee so i want to make sure that it only shows up when there is an employee above and when there's a task to the left and not on not on of course these categories i don't want to show up so i only want to show up on tasks and when there's employees so that comes in on a selection change event so let's go into the developers and go into the vba here visual basic here or alt f11 i'm going to show you based on that so this is called the racy sheet rac i sheet that's what we're going to be focused on and we're focused on again we're going to start with selection change worksheet and then selection changes however you get to it now if the user makes a selection change anywhere from where let's take a look at that it's going to be here in column i8 through ac 58 this is one if the user on selection of any change to any racy task role right here i8 through ac 53 so here we have i8 i'm going to increase that i8 is the first possible one here right here but there's nothing here all the way to ac and then down them in fact i'm going to make this all the way to 53 that's correct 53 is the last one we've set up so we have that there so once the user makes a selection then i want to run a check i want to look in column f remember f is where our tasks are located this row 6 is where our employees are located so we're going to check both of those if f and the target row does not equal empty and cells we're using cells row 6 and whatever the target column does not equal empty only then should we proceed ensure that the task and employee name ensure that there's an existing task and a polynomial only when we have a task and only we haven't do we want to proceed then what we want to do is we want to display that group but i wanted to make sure to display that group of shapes very very specifically i want to display it on the next column over and on the nocturna just to the right of wherever the user is selected so that its position is variable okay so how do we do that well first of all the left position of this shape is going to be based on the target row and it's going to be based on the target column we could also use offset here that would work just as well plus one that means one column to the right that's the left position what about the top position the top is just going to be based on that same row plus the target column top again top position so it's basically the same top position of the target row then we're going to display it if we select anything else i want if we select anything i want it i want it hidden so if it's here and i select something else i want it hidden automatically how do we hide it automatically well the first thing we do inside selection changes if i if it is visible if it's currently visible then hide it hide it i'm going to do that with several shapes like the delete employ button and the delete task button we're doing the exact same thing so notice the delete task button is visible if i select anything else it's going to delete it in fact if i select another task it's also going to be hidden temporarily and then it's going to be displayed that's also in selection change so let's while we're on selection change let's continue with the code here on that so that we're going to do the same thing with delete employee button if i've selected an employee notice this button here is called delete employee button the same with text this is called delete task button so i've given names to these groups so if i select anything else i want it hidden i want it automatically so these three lines of code does just that it says basically if that group of shapes is visible then hide it so we're going to do that and also what i want to do is i want to select the employee and task rows now the employee row v8 and b7 are associated with those so if i select deploy i also want this highlighted if we go into the home conditional formatting and manage rules we see that b7 is based on the selected employee column b7 is going to equal a column we're going to give it that same color and look okay and that's going to be based on i6 i also want to if there's an employee then in this case i6 i want to make sure that we add in a right here or at least the right border here notice that only when we add employees do we have that border so notice let's look on project see there's no borders here as soon as i add an employee if we i need to make sure i select an employee that border shows up so i only want that border showing up when we actually have an employee that is the conditional formatting that we have added here so going back inside the conditional formatting and managers rule we can see that we have that border it's based on i6 now notice again there's no dollar sign before the i because we want it relative based on any column within the range starting in column i so when we format it we see that we have that right border and if that's going to apply to starting in column i all the way to ac6 all right so that's it so that's conditional formatting and we have also on the selection change when i look select a project i want to load that project that's also a selection change event when we selected you watching me do this as i select a project certain things happen first of all we need to highlight the row we need to know that select row is going to go inside b2 we also want to run the macro that's going to actually load the project and that's if the user makes a selection on anywhere from d8 through d99 and we want to make sure that d contains a value there's our project range i also want to make sure that we're going to load the right project so we're going to dimension the found project as a range we're going to set that found project to projects based on the project name right we're looking in a range of all the project names and i want to find that project why do i want to do that now keep in mind that sometimes what i'll do is i'll put the project id down here i'll put the project id down here somewhere hidden and then if i want to load that project id i'll just take whatever's here and i'll place it directly inside b3 but here's another way of doing it what if i search inside our projects here and i look for the project name if it's found i know what row is found on then all i need to do is return whatever's a and that row that it's found on i can return that and i can place that directly inside b3 so that's exactly what we're doing here it's a little bit different way so i want to show you multiple ways of achieving the same result so this time we're going to search for projects we're going to search it and we're going to look what are we looking for we're looking at the target value the value that we've selected we're going to look in excel values in excel hold if it's found if it's not found found project is nothing meaning it's not found we're going to let them know the project has not been found or exiting the sub assuming that it has been found what i'm going to do is i'm going to take b2 and place that target row that's going to trigger the conditional formatting it's going to put that row number directly inside b2 right here okay so we have the row that's going to trigger that formatting okay and then continuing on what i want to do is i want to also put that project id inside b3 how do we do it again we we we know it's been found so we look at the row found project dot row that's going to return the row that it was found on so all we need to do is what is an a in that column that is our project id so this is our project id it's going to place it directly inside b3 and then we're going to run the macro which we're going to get into soon to actually load those projects all right great but what about on task selection notice task selections that's coming up when we want task selection we've got to do a few things if i select a task i want to load that task those tasks details in here the name the category the status and of course the due date and i also want that selected row to go inside where do i want i want to place that directly inside b8 but i want to make sure that when we make a selection change anywhere from f all the way down i want to make sure there's a value in f so if the user makes the selection all the way from f9 through h and they could they could also select the date or they may want to select the due date that would work just fine so that's why we go all the way from f to h right anything they select is fine okay as long as there's a value in f that's required and f and the target row doesn't equal f must contain a value then what are we going to we're going to place that row inside ba that will trigger uh let us know what uh our selected rows and trigger the conditional formatting and then we want to show that delete task button that is the button that little icon and shape that's going to show up it's going to allow us to delete that task so we want that to show up and i want to show that up directly in column e so we can do that the left position is going to be based on e in the target row the top is just it's also going to be based on e and the toggle and the last thing is i want to display that shape again we're hiding it automatically i'm hiding that delete task button automatically on any selection and i'm only showing it under these conditions the last thing is employee excuse me the second last thing is on employee selection when i click on an employee anywhere from i6 to 86 we need to make sure that there's a value right if they've selected nothing we don't want anything to happen here right only when we select an employee do we want that column i want to display that column directly in b7 and i also want to display that delete and play it allows us to remove an employee just like that when we click you may want to put are you sure you want to delete the employee but that's okay all right so we have that so how do we do that so we want to make sure that there's a value in row six in the target column put that column in b7 for the conditional formatting display the delete employee this time when i'm displaying it in row six and based on the target column and then left then i'm going to add 51. why do i add 51 because notice our target column is right here i don't want to place this directly inside our target column which would be like here i want to move it over to the right so it stays in line with that diagonal column this diagonal column if we go into the home we can see that we have this particular the angle counterclockwise that's how we get it when we click here we see that the text is this base there this angle orientation here this 45 degree hinge that's how we've done the formatting for those so i want that x to appear also based on that in the column so moving it over 51 pixels to the right do that we simply just add 51. and i'm going to base it on the top of the cell and then display it all right we've already gone over this part of it right we know how to display that so we've gone over all of the selection change macros we have just a little bit of a change when we make a change we want something to happen there's only one change event and that means when user makes a change of it what type of a change well this type of a change when they're changing anywhere from d4 to d5 what do i want to do i want to reload those projects i only want to list those projects that are perhaps commercial or only those statuses with those types so we do that that's based on a change event if i click here or here i want something to happen so all we need to do on any change of d4 through d5 run the macro that's going to load that project list so we've done just that here if the user makes a change from d4 to d5 run the mac or this load the project list so that's it for our worksheet change and our selection chains now we're going to get into some of the modules notice here we have some modules here project macros and skill matrix macros those are due to our previous training which was on our skill matrix here so we're going to focus on the three races racing macros racy tasks and racy project macros okay so that's what we want to do in here so all right so how do we do that well let's just start out with the first module called racy macros and we'll go all the way up to the top and we're going to racy roll assign so i'm going to start right here now and this is the exact macro that's used when we actually assign a role so when i click r i want something to happen now i don't want to add in five different macros right it would be nice to have the same macro automatically used for all these five so how are we going to do that well what we can do is we can use shape naming shape naming can base of it so notice the name of this shape is called roll underscore r this is called roll underscore notice that both the icon to that square shape and the rectangle shape have the exact same name and i've done the same one for accountable here roll but underscore a so this one's a and the same thing for that shape here take a look here roll underscore a so all we need to do is determine the shape that clicked on that and remove the roll and underscore and whatever's left in this case ra is exactly what's going to be put inside the selected cell so when i click r a here it's going to be just that so by naming our shapes we can use the same matter except for the clear roll that's got a different macro so so all five of these whether it's are responsible accountable responsible and accountable consulted on form you're going to use the same macro and that's the macro that we're going to go over right here so we're going to focus directly on the racy sheet here and the active cell value is going to be very simply equal using the replace now the application caller is the name of the shape that called the macro we've been over this before but i want to know when you try to run this macro directly from the vba here using the run you're going to get an error always and i get this question a lot why do i hit this error because we're trying to run it directly from here notice that application color anytime we use this there's no shape that this is looking for the shape that called the macro there is no shape they call the macro because i called the macro from this button right here so this will always have an error which is fine because we're all just remembered make sure we use a shape to run this macro and there's no problem so what is the name of that shape it is roll underscore and whatever we want so what if i remove that roll underscore and i replace it with nothing using the replace command what's it going to leave me with it's going to leave me with whatever role i want to place that r that a that r a i or c it's going to put that directly in the cell okay once i've done that i've placed it directly in the cell that's perfect but now what do i need to do i not only need to place it here i need to save it directly in our role database here i need to save it here here here here right in column e but i need to determine what row has it been saved before if it's currently has a role database then i need to know so how are we going to determine that well that's with the rest of the code first of all i need to know if b17 does not equal empty or b17 equals zero then it's good what is in b17 let's take a look inside here so we can see that under our racy and look at b17 when i select something take a look at b17 it says that's 90 a right so here's what i need to know when i select something let's take a look at the selection change you see here that certain things are going to be changing right b14 takes on one so how do we get that well what i need to know is i need to know remember i need to know the task id and i need to know the employee id take a look inside this ratio again we have the project we know the project id i need to know the task id and i need to know the employee because if it's all found i need to find the row where this is located on one one one i need to know the project id the task id and employee remember all of that is associated with that particular role here in racy we have the employee id we have the task id and i need to know the project is going to be here so how do we get both the employee id and the task id well the task id when we load that in we're going to store that someplace it's going to be located right here in column a8 we take a look over here we see h this is where our task id when we run the macro and i'll show this to you soon when we run the macro to load this project all the task ids are going to be loaded in here simply put they're going to they're going to go from here they're going to go from here and they're going to be coming to the row so they're going to be coming in they're going to be brought directly inside here so that's how we're going to bring the task that you notice that's here so that's how we're going to bring those in so we have the task id but what about the employee id i also need to store the employee id the employee id is going to be down here as we add employees the macro that we add employees the employee id is going to put here so i know the employee id is going to be located on row 100 and whatever column we have selected i know that the task id is going to be located in column a h and whatever row we have selected so that's the rest of that selection change so if we look inside here going to go back into selection change here we are back on ray c under selection change and remember we had that shape we were showing that shape but there were two things that we left off that i'd left off purposely b14 i want to take on our task id that's going to come from ah and whatever row they've selected the employee id is going to go into b15 and that's going to come from row 101 and whatever column we have selected so the employee id so both of those values are going to go directly into b14 and b15 here so as we select it if it contains a value notice three three one one here here's we can do one one so this one's easy because we know exactly where it went one one one right so if we look in our ratio database i just happen to know that that's our first row one one one right so we know it's empty right so there's nothing here so if i add in a roll here we take a look in our row we see now it's got r so we know it but what i need to do is i need to get this row number i need to know that one one one is on row number four how do we know that because that's unique to the project id that's unique to the task and unique to the employee so a great form that we can use to get all three of those is called sum product i need those three if i'm looking up those three values where what row are all three of those values present on only one row possible in this case it's row four so we can do that with some product formula so here it is right here so i've got the selected product so i need to know all three of these now the project id that's just simply a link to b3 i just like to have all three of them here that's why i have it here notice when i select a different budget it's going to change so let's put back in here so if i have all three of these things we can use a sum product formula to extract the row what i'm going to put that row right here and this is the formula here if there's an error of course we want blank we're going to use sumproduct i want to know the racy roll now i've created through let's go with those named range so i can go back into the name range three named range i've created to make it easier for us and they all start with racy employee id racy project id and racy task id so we only need to look at one but basically the same we have three of them they're dynamic ones for the racy project one's for the task id and one's for the employer id there are three that are exact right here okay so that's all we need to they're dynamic so i've got three named ranges one for the project task and employee id if i know those three all i need to do is look for a match to find that we're going to do racy role we need to know where is it found word b16 where our project id is equal to b16 i also want to know where our employee id is equal to b15 and where our task id is equal to b14 what are the instances when all three of those things are true in if those are all true then what i want to do is i want to determine the row based on the racy rule project id this will get us the row that it's found on that's all we need to do using sum project so all three of these instances are true where the project id is equal to b16 where the employee id is equal to b15 and where the task id is equal to b14 return the row that it's found on and if there's an error shell blank that's it that's all i have to do to return to row so if i select something else here we see where the task id is 1 where the employee is 2 it's going to return row 5. in this case here employee id is 2 task is 1 project 2 is going to return row 5. so once i know the row if i know the row and if what about if it doesn't exist what if we haven't created it yet let's take a look at that if it doesn't exist right we haven't done anything for this our result is 0. notice it's not found so the result here in b17 is zero if it's zero i know it doesn't exist on this database in that case what i'm going to do is i'm going to add it all the way at the end here and this is going to be 100 so for that instance we're going to add a new one so whenever the result of b17 is 0 it means it doesn't has not been added to the database before so add it so that's how we can combine and locate it with just some helper cells some formulas then all we need to do is vba say oh if this is zero add a new one if it's not added to the existing one that's just what we're going to do as we enter our macro here so here we are continuing back with our macro we want to determine now we understand is b17 if it's empty maybe maybe there's an error it's not found or if it's zero we know is going to be a new let's type this in new racy database row so in that instance i want to look the a variable role database row equal to racy database the first available one the last one with the value plus one is our first available row then in column f i'm just going to put the row so that means that only for new ones here inside our ratio i'm just going to put in column f here putting in that row everything else gets updated or created regardless if it is the new or an existing ones but what if it isn't existing what if it's been added before in that case the role database row is simply going to be whatever's in b17 that sum product will return that row number so now we've got the row regardless of whether it's existing or whether it's new everything else we're going to do regardless and now all we need to do is place that project id inside column a coming from b3 all we need to do is place that task id coming from ah of course it could come from b14 just as well there's two different locations and we also want to put that employee id also located here coming from row 101 and the active column and i also want to put the employee name there too it's going to come from row 6 and whatever the whatever role whether it's r a i or r a whatever it is we're going to put that inside column e so the actual roll is going to be put inside column e now we've got it stored up so now we've added all this information or updated it regardless so that's how we add a row that's how we save it by using a great sum product formula okay but what if i want to clear the content so it's pretty much the simple thing all we're going to do again in this case all we're going to do is simply clear the contents of the active itself this is the macro that's tied to the last one here saying clear role so when i click here it's going to clear the role you could just as easily put delete that would be fine too but we're going to use clear role because it's more of a specific and we can do that so clearing the role is going to do just that anything we want to clear we can clear the role when we do that then we understand that we are going to determine if it is a database right if it's not if it has not been saved in the database for any reason then we can exit the sub there's nothing further to do however if it has again we can do all the same thing assign the database and then basically just add everything in and then in column e instead of adding whatever we selected the active cell value in this case it's a clear contents or we could use the actusa value which is going to be empty anyways so that's how we both add roles and clear roles so that macro is the one that has been signed to this so if we right click here on anyone and we can see if we go into the assign macro sorry it's off the screen and then we can see that is the racy roll clear that is the macro that we just went over okay nice very nice we went over those two macros but what about adding employee adding employee what i want to do when i add an employee do a few things is i want to first check to see if the employee has been added first if it has let you let the user know that that employee has been added when we click add employees say the employee has been added already to the project if and then of course select the column so the user can see where it is so if i try to add again jack and click add employees added i wanted to select jack so we can see exactly where jack has been added if it has not been added then add it to the first available column in row six so we can do that with the following lines of code to do this the first thing what i want with our races i want to determine the first available column so the employee column is going to be ac6 which is the last possible column to the right and to the left column plus 1. so this will get us the last column with the value to left plus one it's going to be the first available column now what i want to know is if the employee column is less than nine then make sure to set the minimum the first column to nine right if it's less than this is column 8 this is column 9 so i want to make sure that the first possible column is column 9 in case it's less than that so that's going to set the minimum column number all right if the column is greater than 29 we only have 20 maximum 21 employees let the user know a maximum of 21 employees can be added of course you can customize this and add any number of employees you want all right assuming that it's now not within it now what i want to do is i want to check to see if that employee exists first of all if ad5 equals empty make sure that they have added employee ad5 is the cell where they've selected employee we need to make sure that this has a value if it's not let the user know to please select an employee first all right then what i want to do is i want to check for the existing employee we're going to set the range this is a range variable found employee range based all the way on all possible cells within the row 6 i through ac that's where all of our employees are going to found we're going to look for something we're going to look for whatever they want to add which is located in 85. look in values and hold and if it's been found if not is nothing those cancel each other out and that means this means it has been found it's found already so let the user know this employee already has been added to the project then i want to select it so the user can see directly maybe they didn't see they can't find it so we're going to let them know we're going to go row 6 wherever column it's been found on we're going to select it that selects it's going to automatically highlight that once it's selected and exiting out of the sub all right assuming it's not a duplicate then we can they'll go ahead and add it so in row 6 in the employee column that we've just defined up here we can then add whatever's in ad5 then what i want to do is i want to set the employee id remember that employee id also needs to go into 101. where can we find it that's going to be located in b13 let's take a look inside b13 that is going to be an index match formula right here that's the employee id and we're going to use the index based on the employee id and we're going to whatever is in ad5 we're going to match that that's going to extract the employee id otherwise it's going to show empty so we notice that here jack johnson his employee id is six so now i have the employee id from the formula and all i need to do is take that employee id and place it directly inside whatever say the first available row here whatever column we're adding and it's going to place that on play id in row 101 whatever column is associated with that all right so that's it so now we have then all i want to do is run the macro that we're going to be going over very soon to save the project and i want to reload the project that's going to automatically load all the details so now we know how to add an employee but how do we remove an employee we have to have the ability we have a macro that's been assigned to this button here we assign the macro we see there's nothing but if we click on an individual shape inside that and then we assign the macro we see that that's called project delete employee so when i delete employee i want to make sure that it is removed from this project so i also need to remove it from the database so just like that we might be out able to add are you sure you want to delete them if you want to you can add that in no problem so delete that's what can do so that is the let's get the action the first thing what i want to do is determine make sure that we have a selected column when we select a column it is 20 is going to show up here that's in b7 so if we don't have any column that you just selected we can exit out of the sub all right that is required so we have that we know the employee column we're going to set that into b7 as the employee column all right so what i want to do is i want to clear whatever is in row 6 in the employee call i'm clearing it out and i also want to make sure that we clear out 101 actually we should probably clear out 101.2 i'm going to do that too i don't think i did that clearing out the employee id is important too so that's located in row 101 so we're going to clear out the employee id and we're going to clear out the name clear let's do that clear employee id and we clear our employee name clear employee name now we have that so now when i say if cells now what i need to know has been added to the database if we remember here database this is our database row our employees when we add employees they're inside this table we need to know the row that they've been added once they get added this row gets moved over so i need to know has this employee been saved to database because if it has been i need to remove the row associated with it so that row is found directly in row 100 right here so i need to know if this is not empty in row 100 then i need to delete that row from our racy employee database right here so we can do that here with the following if here cells 100 employee does not equal empty then delete the database row raise the employee database row is going to equal whatever is in 100 the employee database row then we can delete it from the racy employee database this is the database raise the employee database this is the row and the colon and we're going to delete this row delete database row although it's relatively obvious and then what we want to do is we want to clear the contents of this of 101 i also want to clear the database row here inside that clearing that database row also i want to hide that delete employ button that employ button we no longer need we've just deleted it so we no longer need to show that button so we can hide that button i also want to clear the selected column in b7 and i also want to then save the update save or update the project run the macro and load it again so we're going to run these two macros okay great that is all we have for our racing macros so we've been over already how to delete employees here how to add employees of course we also know how to add roles to that and how to clear those roles up inside our next module is going to be our racy task we're going to focus on our tasks now all right how do we add and update and delete tasks well i want a single macro to load those tasks so you see here we've got all of our tasks located here when i select on a project i want to load the unique tasks that are associated with that project and so tasks are going to come directly from this database so basically we've got a list of tasks categories and i want to determine all of the categories first thing i want to do is i want to determine all the categories that are associated with a task so for example project id has these four categories once i know the four categories associated with a project i can then run another advanced filter based on that criteria so framing if i put framing here and i run an advanced filter with a criteria or project deframing it is going to return only the tasks associated with framing if i do that for each one of them then i can then place them separately inside marriage so if i do that with the first one proposals it's going to go all three proposals then i bring them in here then i do the next one for inspection foundation and framing so if i do that for all four of them we're going to be able to get all the tasks associated with each task type or category so that's exactly what we're going to do inside this macro but the first thing what we want to do is we want to clear out all the data right we want to clear out all the way from e all the way and including ah as well right i want to know the database rows that are associated with the task we do need to clear those out as well so that's the first step we're going to do is clear those contents so with our ray c e a through ac we're going to clear all those out and a h through h9 data that's going to clear all the existing data and the database rows then what we want to do is we certainly need a project id that's located in b3 if we don't have any project id for whatever reason we can exit the sub out project id is required i want to set that initial row that task grows row 8 that initial row right here as 8 as we add tasks it'll increase but i need to set the initial row to eight so i know where to place that first category so once i have that we're going to focus on now we're focused on our project task database that is the database that's located here a lot of the work is coming here first thing i want to do this we have already placed this project that is linked to whatever's in racy and b3 so we don't need to place using vba because it's automatically linked to whatever's in b3 so all we need to do is run an advanced filter determine the last row and know i wanted to determine all of the categories based on this project id and i want to put those directly here in p3 so that's within advanced filters so that's the first thing first thing we want to do is determine the last row of this database if it's less than four we're going to exit this sub also keep in mind now you'll notice that i started doing this a lot i'm going to use i'm going to try to do this moving forward the first two rows is header and why is that important because when i delete generally the way i do things you may want to copy this is i might delete a row but i don't want to delete formulas here right so if i use formulas in that first criteria row i want to start our database on row four and not three so i'm going to try to do this more often sometimes we have data mapping in row one when we don't i'm just going to use this so i like two rows for the header and that way when we delete nothing important gets deleted but if i were to delete row three so i think this is a better way of going forward so keep that in mind try to try to do that especially if you've got criteria here formulas we wouldn't want to delete so if this header row was in line two and our first row of data was in line row three and we deleted it it would delete these formulas creating bugs so we don't want to do that so having two rows for our headers is a safe way to go for that so the first thing we want to do is run that advanced filter based on b3 through d right our b3 i only want to know b3 through d project id and category is really all i want to focus on because i just want that unique category listed i want that unique category list to come in column p and the criteria is only k k32 through k3 only that project id is our criteria so our criteria here in our advanced filter k2 through k3 and our results are going to come p2 this is our unique categories i'm going to put down here unique project because they're only for that project categories once i have these i need to determine the last row of those the last row of those categories the last results row if it's less than three that means we have no categories we can't can't maybe there's no categories associated with this we can exit this up that means there's no tasks no categories nothing so we want to make sure if it's less than three but if it's not then what i'm gonna do is i'm gonna loop from three to the last results row so if the four result row equals three to the last results row we're going to loop through those the first thing what i want to do is i want to take whatever is located in p in our row and i want to place it directly inside m3 so that's what we do here m3 is going to take on whatever is p in the results row gonna set that category criteria then i'm going to ready to run that advanced filter now that advanced filter is going to this time is going to encompass all the data so we're starting on column a and going all the way through g and a3 through g our criteria is going to be all the criteria now why does it include this status notice that i also need this status for this let's take a look back on our racy and we're going to take a look this one here pending what i want to do is i only want to show pending right so if if we take a look at this and we take a look at the format control we see that this is based on b12 b12 take a look in b12 we see that b12 is a 2 right but what i want to do is i want to convert this 2 into an actual criteria i want to show pending how are we going to get that well if we take a look inside our statuses right here we see that on row five our second one here is pending right pending so that's what i want to do so what do i mean by that let's go back into our project task so i wanted basically what i want is a formula that's going to show pending right notice the status pending that's going to return only our pending so we can do that with index we're indexing all the project status with all project status is a named range so formula name manager project status with all if we take a look at that all the way down right here project status with all here when we go into ammo series includes the all status and pending so what i'm going to do is i'm going to run a formula and determine what row is this if it's the second one then i know it's row so that's what our form is new based on that named range so we're going to run an index based on that named range that you just saw then what i want to do is i want to determine the row what is the row the rows based on b12 remember it was 2. so if i run an index based on that and i return 2 is going to return if if it's equal to all statuses then show empty anything else basically show whatever is in that that's how we can return the pending so if it's all statuses show nothing so when i change this here inside races if i change this to all statuses then it's going to automatically show and if we right click here we assign macro we see that the project load task it is that macro that's been assigned so every time we change it it's going to automatically load those tasks okay so that's how we get our criteria here that's how we get out so we run our advanced filter based on all of that criteria based on a project id based on any status and based on the category so we can only so this is our criteria our new advanced filter from k2 all the way through m3 and then i want the results to go right in here from s through w i want those results to go here so continuing on with our macro and our second advanced filter so a g through a3 through g our criteria is k2 through m3 and our results are going to come through s2 through w so that's all we have to do here s2 through w3 equals true there we go so that's all we need to do to get those results once i have those results i need to determine the last row based on column s that's last results row if it's less than three we're going to go to the next category remember this is we're going to skip down here and go to the next category because we're inside a loop looping through all the categories if it does have data we are going to set the category ray c e here i think we're already in racy so so racy we've got to go back inside because we're already in our projects database here e is going to take on that category name whatever's in m3 so therefore it's going to take on so that's going to put that category it's going to place it directly right here inside e now we need to increment the row one so we have our e is going to take on that first category then we need to increment the row so that's what we do here task bro equals task row plus one we're going to increment the task row now what i'm going to determine is the rate so now f through h here now i want to place f all the way through h i want to place the task the status and the due date this should say due date not due or that's fine due date but you get the due date due date let's put that in due date so it's a little more clear so we have the due date here and i want to have all of that information come directly from so it's going to come from our results so here we have our task our status and our due date so t through v is going to come directly in that's our next line of code t through v in the last results row is going to equal f so it starts out but i want to bring them all in right it can be the task row plus the task scroll plus the last results were all minus three what does that mean well if i'm returning one right i want one row then i need to subtract three right three our last result was three minus three is going to be zero right three minus three is zero so in this particular case this is going to be zero so it's going to be our task row plus our task row so we only have one row so task grow plus task or one row of data that's what i want if there's two rows of data this will be four minus three will be one so one plus our task row it's going to be two rows of data so this will take in all of the data regardless of how many results here it's going to bring in all that data and it's going to bring it directly inside here so i can bring it all the way in here all right next up what i want to do is i want to add in those database row however many databases i want to put that in a h and that is going to come directly from here in column w so that's the next line of code ah and the task row same thing the task row plus the last results rule minus three is going to equal w through degree that's going to bring in all the task ids once i have that what i need to do is i need to update that task row the task row i want to know the next task row right so i want to get it ready for the next so if i've brought in three tasks i need to set that next row to 12. so to do that we use this task is equal to whatever currently is plus the last results row -2 so this is basically going to say because why is that because i want to add in however many tasks that have been returned i want to increase that so if we take a look here we have just one result we're going to increase it minus 2 and that's going to be 1. so 3 minus 2 is equal to 1. so we're going to increase it 3 minus 2. whatever the task are currently is plus 1. so that'll increment it just 1 because we've only had one result all right so that's it that's all we have to do is going to loop through that through every single category as it loops to all the categories putting each category in here and then getting those results and then bringing those results here so that's how we have it but as you'll see our roll data is still missing so we need to get that in here let's just update that so before we end this macro that we're on what i'm going to do is i'm going to go back into the project this is the last one we'll be going over i've got one called racy pro this macro right here racy law project load those roles that's the one i want that's the macro that i want to run i want to run that when i update those tasks so before the end of this we're going to run that okay continuing on so it's going to loop through all those categories there once we've done that i want to clear any task details all those task details we need to clear right i want to clear all this information in case we have a task that's updated i want to make sure we clear all of that so that we can select it any selected task row must be cleared and so on and so forth so we clear all of that out and i also want any if the delete task is still displayed i want to make sure that we also clear that out that shape there should be hidden and i also want to now again load those roles right so now when i change it to pending or something it's also going to load the roles automatically associated with that just like that okay great so now we know how we're going to load these and load these tasks all up and load bring all that data in here so continuing on what about if i want to add or update a task and what i mean by add update if i select a task i may want to update its category and perhaps its status so if i want to do that i can do that and then just save it and it's going to automatically save it and it's going to go there okay so now we have it under estimate here so how do we do that i want to save it but also the same thing if i want to click here and i want to save a brand new one what if i want to enter a new one i also want to be able to enter new one using the same macro so let's say we have inspection here and we do the category just in enter category inspection and then the status here in progress and then we save that task i want to make sure to save it so that it's automatically saved so that the same macro is either going to save or update it so how are we going to do that and it's right here the one we just did so we can do that with this macro called here project add or update racy project add or update task okay so inside this macro the first thing what i want to do is i want to determine to make sure that we actually have selected a task and we have a proper task category i want to make sure that g3 and g4 contain values if either one of those are empty i want to make sure to let the user know to please add in a task name and category okay assuming they have both of those i also want to make sure to see if b10 is empty b10 of course is our task row now if we take a look inside our task pro it is going to be an index match based on our tax id our b9 is going to take on every time we select a task b9 is going to take on that task id our task row is going to be using a match to determine what row it is on if it is empty then we know it is like we save it right it goes to empty we want to clear that out notice it's going to be cleared out notice it's clear so if in this case if we add a task name we know that our task row b10 is empty we know it's a new one so b10 is going to tell us whether it is new or an existing so if b10 equals empty then it doesn't call this a new task new task so what do we want to do for a new task well i want to determine the task row it's going to be based on whatever they've selected i wanted to know the first available this is based on this isn't right here the first available task row inside the database oh the task grows sorry sorry we don't really need that actually i'm going to remove that we don't need that in this case we have a task database row that's the one we really want to focus on so this is the one i need task database row the first available row inside our task is it is a new one so we look at a project task and this it would be 28 here 28 is the one we're looking for so our first available task database where our task id is going to be in b11 b11 would be our next task id using the max formula is our next task maximum of the task id all the task id is plus one if there's no data at all it would return an error therefore i want just one so that is going to give us our next task id we want unique ids for each task so we use the max formula to help us with that b11 will take on our task id it is exactly that that we want to put inside column a so a is going to take on our task id b is going to take on the project id and then g is going to take on the database row everything else all four of these other columns are going to get updated regardless of whether it is a new task or an existing task so right here task id is going to come from b11 our task database row the first available database row first available row so now that we have that we also going to get our task id is going to be located in a8 i want to place that task id the one that we've just created here inside a h so not only do i need to put it inside here i also need to take that same task id and i want to put it directly in h a h for the newest got to take on the task a so we're putting it there as well then i want to update a column a with the task a b is the project id those won't change and g is the database row so everything else c d e and f are going to be regardless so if it is an existing task then we're just going to get our task database row from b10 be tended also we can also get it directly from here too as well so we've got it in both locations that can help us b10 will take on our row so we have our row this is just our id here so we're going to get a row i want to know that row it's going to be located in b10 so that's for existing everything else we're going to do regardless and that's going to be up to the project name the project category the status and the due date those are going to all come from the individual cells and update and then lastly we're just simply going to reload those project tasks right i want to run the macro that's going to reload after we save it whether it's new existing we're going to run it so that automatically it updates there we go so now you see it's automatically updated and so are results okay great so we have that so that's how we update the task and going to get both loaded inside our room but what about if we want to load a task right that's a simple macro if i select on a specific task that's going to based on selection change if i select i want to load that information up here i want to bring that information i want to bring the task id directly inside b9 so that's going to be based on selection change we went over briefly on selection change here inside the task if the user selects a task here on task selection we're going to run run that macro to load the task that is the macro that we're going over right now we went over everything else so it is that macro we're going to focus on the racy sheet and if b8 is empty b8 is our selected row if we don't have the row if i don't have that row and b8 15 16 whatever it is if we see that i can't go for it i must have that row located in b8 so if it's empty we'll exit the sub if it's not empty i'm going to put that and i'm going to put that inside a variable called task row then what i want to do is i want to check for that task id if that is empty and located in ah then we can also exit the sub so i should put in i can put in use the variable that we just printed instead of this we'll call it task row task row okay so if a8 and the task grows empty then exit the sub on node task id b9 is going to take on a h remember this is where we add in the task id directly in b9 that's where our task id is going to come directly from whatever's in a8 it's coming from there in the selected row i'm going to bring it directly over this id button will automatically generate the task row based on that match formula and put it right here all right so once we have that then all i need to do is bring everything up so g3 is going to take on whatever's in f g4 is going to take on whatever whatever's in the category right now i don't know the we don't have the category here right it's all the way up here so how do we get the category if i know the row if i know the task row is located here all i need to do is look in the project tasks i know the row and all i need to do is look at column d and whatever's in column d in our row and place that inside so that is going to be placed directly inside here inside g4 so g4 is going to take out whatever's in the project database d and whatever's in b10 value this is what our pro task database row is task category it comes from the task database it's much easier it's much easier than finding where it is up here much easier otherwise we'd have to look up so a lot easier all right so now that we have tests the status is going to come directly from column g the due date is going to come directly from column h so it's going to bring in g and h is going to bring those right here and right here all right so that's it so that's how we load it up so it's going to load the task what about if we want to delete the task that's of course the last macro inside this module that's relatively simple if i want to delete a task all i need to do is select this if just to double check we're going to make sure that of course we have an id here we're going to place it here to determine the row here once i get the row of course i know the row once we've selected b12 inside here excuse me b10 that's what our row is so we do that if letting are you sure you want to delete this task yes or no if no exit the sub b10 is empty also it's up i've got to have that database row in b10 if it's empty we're going to exit the sub oh you're going to put that inside of variable task database row from there and then all we need to do is delete that using the project task database task database row and colon and entire row delete and then we're going to reload that project reload the entire project which is going to reload everything so if i want to delete something all i need to do is just select it say yes on the confirmation it's going to delete that and the entire project is going to be reloaded automatically just like that okay and the task is now gone alright so we understand how to delete the task that is it for all the tasks database in our last module we have just a few macros we're going to run how to save and load this module we are going to focus on our project the rayc project this is the last module we won't go over this we'll go over these variables as we go throughout the matter so the first thing what i want is i want to load the project list that is this this project list this is the macro that runs when we change this i want to load all of the projects based on any filters now notice there's some filters pending i want to know only those projects that are pending there's no pending maybe there's some that are estimated so i want to use some criteria here from an advanced filter and that's going to be based on our project database so this was for our skills matrix this is for our reiki we're using the same database that we did for our skills remember our skills was here right so now we're focused on the races so our projects we're using the same database but keep in mind that this is the criteria we're going to focus on and again with formulas we can do that so what is the type the type is showing empty right now why is it showing empty because we're showing all types so if d5 equals all types then show empty so if d5 the ray c d5 admin equals g equals admin g4 what's in g4 well g4 is right here all types right so and i also want to know all statuses right so we can do that just right here so if it's equal to that then we just show empty and then the same thing here if ray c equals admin e4 admin e4 is located right here all statuses then show empty if it's all types or all statuses then show it empty otherwise show whatever's in there so now we have estimates so we see inside the projects we want to show estimates because that estimate that we have selected right here estimate okay so now we only want to show estimates then all we need to do is determine the last row run an advanced filter here o 2 through p 3. then we want those results to come here whatever results here i'm just going to bring them directly inside here but the first thing we want to do is clear out the contents before we load the projects so that's what we do inside there rayc b2 where i want to clear out any selected row b2 also d8 through d9 clear the content's existing project then with the project to determine the last row if it's less than three we're going to exit the sub i think that should be four let's see what i put projects on projects was on yep should be four okay so it should be four if it's less than four then exit the sub out so now what we want to do is have our data a3 through i the advanced filter our criteria again is o2 through p and our results are in q2 then we determine the last results row using column q if it's less than three three is correct in this case exit the sub then all we need to do is bring in our data d8 through d in the last results row plus five i've had a few questions why do we add five well remember we're starting on row eight here our data is coming from row three here the difference between these two rows is five so we need to add five to compensate for that difference that's going to bring over our list of projects okay that's it that's all we have to do is load our list the next macro i want to focus on is project new relatively simple when i click this button that macro is going to run it's going to clear everything out it's going to clear all those pictures out notice the pictures it's going to clear everything out so when i select it i want to clear everything out on that so how are we going to do that let's return back to our all list here let's go with our all statuses here and our manager so what i want to do is i want to clear out one i'm going to make this faster too notice it's a little bit slow oh by the time you get it will be a little bit faster it's simple we're just going to make sure the screen updating is false and calculations are turned off while the macro is running so how do we do that well the first thing what i want to do is i want to clear all of these pictures now we're going to get into how to create these pictures but keep in mind that every single picture here contains the word employee pick employee pick 11 employee pick 12 employee pick 13 and so on and so forth so if i know that every of those ones include so i can remove all i'm going to look for all the pictures that include employee pick and delete them so we can do that here so project for each employee pick employee pick is a shape that we've defined here so we look up here we see employee pick as a shape it's dimensioned as a shape if using the in string command employee pick name if it includes employee pictures greater than zero then delete it then all we need to do is just simply clear the contents of a bunch of cells so that's it for our project new nothing else we need to do what about when i want to save the project that's the macro that's going to run here when we click save project it is that macro that we're going to run so what do we do with that well that's relatively simple well we need to make sure that there's a project name so p3 that's where our project name if that's empty we need to let the user know to please make sure to assign a project name before saving and we're going to exit the sub out we're going to turn off application screen updating and we're going to turn calculations to manual this is going to make it a lot faster we just need to ensure before the macro ends that we turn them both back on to true and automatic okay so in this macro the first thing what we're going to do is i'm going to determine is it a new project or is it an existing project right b4 is going to tell us that our project row using that match based on our project id as soon as we click new project we see that b4 is empty there's no project but as soon as we select an existing project b4 changes to the project row so b4 is going to determine whether it is new or existing so if b4 equals empty then it's a new project else it's an existing project for new projects we want to do a few things i want to determine that project row it's going to be that first available row based on our project sheets we also want to take in whatever our next project id our next project using are just like we did with the task our next project id located in b5 is going to use that max formula this should say max equals this is not good that's not going to help us equals if air maximum based on the project id project id and it's going to be plus one right we need to add one if it's an error we're going to show one because we would want that to be our initial project id okay in this case 13 is our next project id using that max formula and we're going to take this and we're going to put it directly inside b3 for our new project id so that's what we do right here b3 is going to take on whatever's in b5 i also want to take that and put it in column ever project it's going to set that project id we're going to put that directly inside column a here right here 13 will be our next one using column a all right so assuming we have that if it's a new one that's all we need to do for new if it's in existing we're just going to extract that project row directly from b4 everything else is the same now we're not using data mapping in this case right let's see it's about five rows data mapping takes three rows so we've got a little bit of an extra but not too much data i did use data mapping remember we do have data mapping here this data mapping was for our skills matrix right this is map that data to here so we had a little bit more data now let's say we did i've had a few questions well what if i want to do data mapping on two sheets well all i would do with that is use a different row i could use row one here and i could use row two for data mapping so i could use two different rows all i would need to do is just drop this down and add one more row of data mapping to do that so i could do that too but i just chose not to because it wasn't so much data it was just five lines of code but remember data mapping would only take three lines of code for loop our four column four uh column two two last column then our data updating and then our next column but here we just have a few lines of code bringing the data lines b c e and f okay so that's going to bring in now we're not using in this case we're not using days we're not using budget i'm not using a required staff so we don't need that that was for our skills matrix here we did need all of that here right days and required staff so we did that that's why it's there okay so we're just using a little bit so that's all we have to do now what about that's going to make sure that we save all of the project information now what i want what about our update our sign employees embrace roles right i want to update any employees here all the employees here from column 9 all the way to the last one i want to make sure their information gets saved inside our employee racy database right i want to save the project id the employee name the position the picture and id and the database row here so i want to make sure all that is saved inside the database associated there so to do that what we're going to do is we're going to run a loop from nine to the last possible row nine through twenty nine and then as soon as it's empty as soon as we have an empty cell like this would be our first empty cell as soon as it's empty we're just gonna exit out of the exit out of that four next loop so all we have to do that if cells six row six and then column equals empty then we're gonna exit the four so as soon as there's an empty we can exit out because they're always in order now i also want to do is it a new one or is an existing one now that database row of that rate is right here so if it's a new it's going to be here if it's existing so i want to see if it's near existing if it's new like these here we would want to make sure to save those inside the database row so we're just going to check on those here so i also need to clear these out notice this one this should these should be cleared out i'm going to make sure on project load we need to now clear out originally i had a formula here based on the employee name up here but then what i want to do is i wanted to switch position so the formula was no longer helpful because i want to be able to switch the position so that's when i used a i'm going to actually clear out this also so i want to make sure we're going to be clearing out everything here from i through 100 because there's no longer a formula here so we'll just do that on new project right here remember that new project i want to make sure it's all the way to 101. now we're going to clear out one on one and the same thing for project load we'll get to that next that's going to come up next i want to do 101 as well okay so we got that covered because that shouldn't be there all right so continuing on so what i want to do is i want to check if it's empty then it's a new database role generally we're going to have them i want to know is there a database row if there's a database row that is associated remember that's the database row here four five six then all you need to do is update the information inside there if it's not so let's take a look at that so if it's empty then we need to assign a new one the racy employee rows can be the first available database row inside that database inside cells the row 100 we're going to place that database or i need to place that brand new row right here inside row 100 that database row that next row is going to come in this case it would be 30 it would be put directly inside here inside okay so we have that now what i want to do is i want to take that project again i want to put that in the first column that project id must go here right inside here inside our employees i want to put that project id so i know what project id has been associated next up what i want to do is i want to copy the formulas remember we have formulas that are going to display the employee position they're going to display the employee picture and i'll remember i said i don't want to have these here unless we're actually creating a new play so i'm going to take those formulas i'm going to bring them right down here into c and d so we do just that here i'm going to copy what's in c1 through d1 and we're going to place it directly inside c through d and then paste special we're going to paste those formulas in here going to paste employee position and we're going to place in picture names so we have that there so it's going to put in the picture names and then we're done with the copy and paste so we can turn off the dancing ants by returning application cut copy mode to false and then what i want to do is the last thing also four brand new ones i want to set that row going to be in column f all right else it's an existing employee in this case all we need to do is extract that database row from row 100 of the employee column the two other things placing the employee name and placing the employee id are going to be regardless of whether it is a new employee or an existing we're going to place that name right in column excuse me column b and we're going to place that id in column e b and e is all we need to do to update that all right so the and the employee id is going to come directly from row 101 we place that remember we place that as soon as we add that employee that employee id gets placed right there all right so that's it so we just loop through all the employees to make sure that all the data is saved then what we want to do is we want to update that project name in case there's a change right i want to make sure that we've updated the project name if i change that project name i want to make sure that it gets also updated here so when i select a project let's say i have project 5 and i want to change that inside here project 5. i want to change this let's just to inspection for site and then i want to save that project i want to make sure that that project name also gets saved here notice it got automatically saved now it's inspection for site so or we can change it to site inspection i'll show you again and then saving that automatically is going to update whatever's in d and the row here so to do that we just say if b2 the value does not equal empty making sure that we have a row then d and e and b2 means the row that's associated that is the selected row in this case d11 d11 we want to change d11 i want to change it to whatever is in p3 that is going to update that project name right here without having to reload all the projects again that's it then we turn on application screen updating true and the calculation automatically okay next up is the project load this is the main macro that we use when we run this that's going to load up our project so how do we do that well i'm going to make it a little bit faster too well what do i want to do the first thing i want to do is clear all the contents right clear all the selected rows all of the tasks all the project details all the rules so we can clear all that out on this one and then what i want to do is i want to clear out all the employee pictures again just like we did before we're going to look for every single shape on the sheet and we're going to loop through every shape if that shape includes the text employee picture then we're going to delete it using the in string command if it's greater than 0 that means it's contained in the name of that shape we're going to delete it next up also if b4 is empty we must have a correct project row we can't load a project if there's no row associated with so we need to let the user know to please select product we're going to turn off application screen updating to false and manual right that's going to help things make a little bit faster although we'll be speeding it up i also want to load them the project details just as we did before we're going to set the project row it's going to be based on b4 then we're going to basically bring it in bring in our project name from column b type from c manager from e status from f and and description a it's going to put all that information so we're just bringing up all information bringing it into these fields and this could come directly from these roads just bringing all that information into this once we have there then we are ready to load in our project tasks and we've already been over this macro remember we had this macro let's just go briefly over we went over that loading those project tasks we go and click definition it is that project task we cleared out all the data and we brought in all of those tasks directly so that's already in a macro that we covered relatively easy so we're loading the project text next up what i want to do is i want to load in the employees and the pictures this is the slow part because we have to go through and we also have to load in all these roles so we'll go into that so we're going to bring in all the employees now what i want to do is i want to run an advanced filter based on employees but i only want those employees that are associated with this project at least we're going to run a criteria based on a linked cell to b3 that is our project id so we need to determine the last row that's the first thing we're going to do inside our employee database getting our last row and then if it's less than 4 going to no employees we're going to run our advanced filter based on our criteria and bringing that criteria all the way from j through m so bring in the employee name picture the employee id and the database we're bringing all of that in there and then determining the last row of our results this is in this case it's 17 and let's get based on column j so j is going to take on our last results row if that's less than three we're going to skip all this and go to no next employee no employees down here if we have employees then we can continue on so we can do an employee call we're going to set that initial employee column is to 9 right i want to know that initial column it's going to increment just like we did the tasks in this case we want to know 9 that first column as we move to the right we're going to increment it so we get our first initial column to the right and we're going to set the picture folder i need to know that's an o5 setting our employee picture folder remember inside our admin screen is where our picture folder is located right here that's where our pictures are located in o5 so we have that folder we're going to add a backslash on to that once we have that i'm going to run our results our results are from three to the last results row we're going to need to extract that employee information from three all the way to the last row getting their name getting the employee id remember i said we're not going to use a formula we're going to take this i'm going to place this directly inside the row here 101. and okay so the first thing we want to do is we want to take that employee name and place it in row 6 and whatever the employee column is it's going to come from j i want to put that employee id in row 101 and i want to put the database row from m into 100. so again we have the employee id in 101 the employee database row in 100 that's going to come directly over here the database row in row 100 the employee id and notice how it's correct now incoming into 101 so we just bring that information directly in there it's going to be you can hide it of course all right so once we have that information in then what i want to do is i want to determine is there a picture right if there's a picture i want to display that picture i want to display it directly in row 7. so how do we know if there's a picture we're going to look in column k if k contains a value and we combine k with our folder path we get a full file path and we check to see if it's accurate so if k if it is empty we're going to just skip the whole picture right we don't we're going to go to the next employee but if it's not empty we do want to add so that picture file is going to be inside a string variable based on what is in column okay that's just the file name in itself i'm going to combine that picture file with the folder it's going to give us our full picture path now to make sure we want to make sure that is correct so we're going to use that directory command and if the picture path vb directory equals empty then go to next again if there's something wrong with the path we're just going to skip all of this if there's nothing wrong it's the correct path then i want to insert that picture but we don't necessarily want to insert what we do is we already have a sample shape and we just want to add it so we have the shape here if we take a look at this particular shape here it's kind of small you see it all over here it's called employee sample picture this is everything i want all i want to do is i want to take that picture i want to change the background it's just like going into the format picture and we go to fill and we want to fill it from a file so we insert from a file we'll click file and it's going to insert a picture wherever we'd want to put that picture we're going to do the same thing we're going to do to vba we're filling it with a picture file we're using this sample notice this sample doesn't contain the word employee pick right it's smaller employ sample right because i don't want this sample deleted when we delete the others and i'll put this sample right here so i'm going to duplicate this sample and then with that duplication i'm going to rename it give it a full name and then give it the column i'm just going to use the column 9 10 notice it takes on the column 11. and then what we're going to do is we're going to do okay so we've checked to make sure that it is an accurate path if it is what we're going to do is we're going to take that sample picture here we're going to duplicate it we're going to assign a unique name to that it's going to be the employee pick and whatever the column that's that unique name once we have that we can work with it so with racy the shapes this brand new shape that we have i'm going to place it directly on the left in row 7 on the left moving a little bit off the row to the left and a little bit off the top so it's going to go directly in that same column then what i'm going to do is i'm going to fill it with the user picture i'm going to fill that picture with the user picture that's all we have to do and then we increment the employee column by one that's it that's all we have to do to load up the employee rows all right we're almost done next up what i want to have is a macro this macro is going to run to load the roles the macro here is going to load all of this data here and we kind of have an idea we kind of know what it's going to be so let's go ahead and see what entails that before we skip out it is this one right here this macro here is going to be the same and load those racy roles so we're going to turn off application screen updating it's already off before running this that's why it's running a little so we're turning it off twice once above and once below probably don't need but we run this sometimes we run this only when we load project tasks sometimes we load it when we load the entire project okay so with the racy database we're focused on now only this database here i'm focused on this database here first thing what i want to do is i only want to know those results from this project again we're going to run the same criteria just as we do with the other databases going to run it and then we're going to get our results our results are going to be here and there's a lot of them that's why it's a little bit slow here but there's a lot of them then what i want to do is i've got a task id i've got employee id and i'm going to look for that so how are we going to get it i've got a task id i've got employee id but what i need to do is i need to know where are we going to place it what row what column are we going to place it if i know i've got a task id let's say i've got a task id of 1 right and i look all throughout here use the match i use the found i find and i look through here and i'm looking for one if one is found i know the row it's been found on it now i know the row it's row nine but now what i do is i need to find the employee i know let's take a look back here i know the employee id is one so now what i need to do is i need to look through all of the employee ids here's our employee ids i need to look through all here anywhere from i101 to ac where is 101 found where is number one found well it's found directly in column i so now i have the column now i have the row now i know exactly where to place it so that's just what we're going to do inside here so the first thing we're going to do is determine the last row of the racing device just like we're going to run an advanced filter based on our project id number that's just going to extract the right from here based on i2 through i3 getting all the results all the results for that one project once we have that we're going to get the results from n2 through r2 we're going to make sure that we have a last result row if it's last we're just going to if it's less than 3 we're just going to skip and go to no roles assuming that we do have we're going to run a loop for the results row equals 3 to last resolution now there's some other ways to do it we could probably run a formula let's say we have a match formula we're going to match this task id i would like to try that to see if it's faster let's say we had a formula down here what we're going to do is we're going to look up this and i'm going to look it up directly inside let's say racing let's say we're going to look up that task id run the match run it on this range here and then we have another one so basically using vba or we're using formulas then i have another one looking for the column of that employee we could do that here too it might be faster here so we can add some forms we bring down the formulas then we have the row then we have the column here and then we once we know the row we can bring it down here but this time we're going to use vba so you've got two different ways you've got an idea of what if you want to see what this might look like let me know in the comments below and i'm going to put it on our patreon to see which one's faster right so i'll make an update of this file on our patreon okay so i've got task id and play idea are really critical we need to know that so that's the information i'm going to strike so the first thing what i want to do we're going to run a loop through all the results i'm going to get that task id it's from column n and i'm going to get that employee id it's from column o so we have that now again i want to set i want to look for that task id setting the found task we're looking for it in a h that column a h i'm looking for that task then i'm also going to look for that employee that employee is going to come remember from 101 to 101 right that employee id i want to look inside 101 just to reiterate we're looking for that employee id 101 all the way from i 101 to ac we're looking for that task id all the way from ah 9 through all ah and then all the way down to i guess i could go down to 53 it should be really 53 that's our limit on this particular one so probably set that to 53 it might make it a little bit quicker so we had that there so we're looking for that now we need to make sure we can only place what i want to do is i want to place this i the r dell whatever i want to place this here but only if we find a row and only if we find a column so to do that we want to make sure both if not found task is nothing right remember this can't see said that not nothing that means if the task is found and if the employee is found not nothing then only when both the task id and employer are found we're going to set the task row to the found task row we're going to set the employee column to the found employee column now we have a row now we have a column now we can place it in racy the cells the task row the task column this value based on the found column based on the found row is going to equal whatever's in q in the result row q and the result row is going to come directly from there that's it so we're just going to loop through that and then the active sheet racy i want to make sure we select something else just so we can select everything else i'm just going to select g3 so once it gets loaded we're selecting g3 that is it that's all we have to do to load that in so everything else and then of course we're going to turn on screen updating true and turn calculation the last macro that we have to go in this training is the project delete we want to come up with a message box are you sure you want to delete this project yes or no if b4 if we don't have a row associated we we go to not saved right we just clear it out go down here load the project list but if it has been saved all i want to do is delete the row and just a reminder again if you didn't know once you delete the project it is going to delete it from this if i were creating this application to for sale like you should be doing you also want to clear out like let's say we're deleting project one you also want to run an advanced filter and delete all of the database rows associated with this put these in reverse so the top row sort them first it would be 18 the bottom would be four delete row 18 delete row 17 16 and we do the same thing for project tasks and we do the same thing for row so you will also want to delete this data associated with that not just this but for this training purposes it's suffice but i'm giving it so that's all then all we're going to do is load the project and run new run the macro new so that's good so if we want to delete a project it is relatively simple all we need to do is just select on a project it is that same macro that's been tied to this button we click delete are you sure you want to delete it yes and it's going to delete the project we reload it and it's no longer there very very cool i'll be making this a little faster trying to test it out and certainly making an update for those of you who are on patreon if you're not on patreon you will want to get on patreon i'm going to be adding email automation onto this you won't want to miss the email automation that's exclusively for our patreon so make sure you get on just a few dollars a month and it also helps support us now all right thank you very much for this incredible training next week something brand new i really appreciate your continued support don't forget to subscribe click on that icon bell and plenty of amazing ways to support we'll see you next week thank you so much [Music] you

Channel: Excel For Freelancers

Views: 71,527

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, RACI Matrix, RACI Tasks, RACI Model, RACI Chart, RACI Matrix Excel, RACI Model Excel, RACI Chart Excel, RACI Project Manager, RACI Task Manager, Manage RACI Roles, RACI Roles Mangment, Excel RACI Employee Roles, Use RACI in Excel, Free RACI Matrix, Free Excel RACI, raci manager excel, RACI Task Management

Id: 0z_NyIgEwgE

Length: 112min 7sec (6727 seconds)

Published: Tue Nov 23 2021