Items from here from one area to the other or one task from the other until they get completed i also want to be able to show specific employees or specific team members as i click on this i want to create these tabs for just specific i also may want to show only specific projects if i click on a project i can show different projects or all projects i also may want to expand show the expanded or show a card that's just limited right if they're not expand if we have a lot of.
Cars we may want to limit it so we can do that i also may want to view closed projects or open all projects and i also want to maybe show only certain priorities urgent priorities or maybe we only want to show high priority so we can do that they're all color coded based on a specific priority so we can set that up we also may want to filter between categories we only want to see those ones the design or maybe those only in a development so this is going to be completely customizable i'm going to show you how to do that every step of the way so that's going.
To be in this week's training a con board if you do like these trainings i bring these to you for absolutely free each and every tuesday i just ask they do a few things to help us out and keep this channel going well and that is subscribe that's going to be the first thing you want to do make sure you subscribe and click that notification icon bell there at the bottom that's going to show that you get alerted as soon as i create these videos so you can be one of the first to watch them and that's also going to do live chat as we do live chat each and every tuesday i'm there every single tuesday answering your questions in live chat so you hope you'll join us if the time works well for you another thing you can do is also comment below i'd love to.
Hear your ideas your comments your feedback that really helps so i want to keep that alive as well your comments and of course smash that like button that will help the youtube algorithms i create these absolutely free you're welcome to download this using the link down in the description if you want to support the channel a great way to do that is with the 200 workbook zip file i've got that available now that's 200 of my best templates all available in a single zip file and that comes with a complete 200 workbook library what that means is a single click to open up that workbook and a single click to view the video training on youtube so that's going to.
Help us out it's just 77 that's less than 40 cents a workbook i've got a few bonus workbooks in there as well i'd appreciate that that's going to help us out all right let's get started on this this is a sample we're going to be creating most of this from the beginning but there's a lot that i've done already so this is what we want to do so i want to have a project i want to have site i want to be able to hide tasks or show tasks if i click on a specific task i want to show those task details i also want to be able to hide that to expand the board and i also want to be able to create new tasks i want to be able to save tasks delete tasks some of that's been done already i'm going to walk you through this so we don't have a many many hour video but we're going to show this drag and drop we'll be able to drag it up we'll.
Be able to move these tasks forward it's as we get them in there so we can move them to the right and as they process or we're also going to show you how to recreate this really cool drag and drop if you want to move it back to a certain section or forward also we can do a filter and we're going to prioritize those based on the priorities so basically they're always going to be prioritized based on that so the red's going to come up first those are the high priorities the second and then go we do have an admin which i'll show you so let's close this out and we're going to get started on this training this is the sample so i'm going to close this one out and then what i'll do is i'm going to show you we're going to start with this one here now we haven't created any of.
The cards the cards are the individual items in them we haven't created any of the team members and we just have a few things on the hide so we're going to go over that with you let me go over what components make up kanban board and how you can create your own and how this can be beneficial in any type of business for any type of work you can also create something like this and of course create make it for sale you know because it's a fantastic feature customize it for your company or companies like this or you can create it for your own sailing and then sell it on your website great feature kanban is really really popular because it's a very simple way to manage your tasks as opposed to one of the more complex project managers what you can also do is you can take.
A project manager and create a one screen that has this kanban so there's different ways of managing projects different types of screens whether it's a scheduler or whether it's a gantt style chart or something like that this is a kanban so it's a different style of viewing and managing your tasks within a project so what do we have here so we've got an admin screen here i want to locate our pictures our pictures the team member pictures remember those pictures you saw they're located in this specific folder right so we have are the pictures because i want to show pictures of our individual members on that and i have that inside a folder and so for me i've got we've got four.
Different ones i've got fred harold lisa and mary those are the folders that's located in so i want to make sure that i map out that so when we pull those pictures up we can pull them from a specific folder so you can do that all you need to do is just have a folder and make sure you're mapping that this is that map folder that we want to create okay all right so we've got that and that's going to put that i'm just going to paste that we didn't include a browse but we just put paste in that link right in here i've also got stages now these are linked backlog to do in progress review completed and closed these are the same stages that i've got here they're all linked up here notice that you're using in indirect formula b in the column plus one equals true so basically what.
I've done is i've linked that what do i mean by b in the column how do i link that well obviously you can use a direct link but i used a specific link so that i could just drag it all the way over here it's going to be the same so if i know this is column five i know this is column five right e is column five f is column six and so on and so forth and i also know our first one is going to come in row six our second one is going to come in row seven right we know it's column b so if i want to link that very very easily all i need to do is use the indirect i know this is row.
6 row 6 here is going to end up in column 5 right so if i want to link this i know this is column 5
But i need to link it with row six and b so it's b plus the column the column's five plus one that's going to give us six right so i know that b six from the admin is going to and of course we have a1 style a1 meaning you know it's the cell style not r1c1 not row so if i do that and since each column is different it's going to basically take this column add one that's going to be the row so all i need to do is to link it to drag and drop and that way as soon as i change something.Here to like backlogs or whatever i want it's automatically going to update here okay so that's important so i want to change it back because inside our tasks we have backlog also i want categories we can create any type of categories i've got all categories and this is important and i've got our list of categories you can create any categories you want this is that same drop-down list that's located here it's all of our categories if we make a change it's going to be called categories i also want to show a drop down list here called all categories so notice this dynamic drop down list this data validation here is going to include everything here including.
The all categories but this drop-down list inside our tasks is just the simple task so every single task that we create we can save a task new task and delete test every task is going to have a task name you have a project notice we have a list of projects and i'll go over that with you and also we have a priority urgent now priority urgent high normal this is the other thing that's going to come from this again we have our priority list and i've assigned a color and i'll go over the number in a moment and i've assigned a color for each priority and a given number and i also want the show all above that why is that because inside our kanban board if we're going to show.
Priorities i also want a data validation for all four of the priorities and a show all so when i click that i want to make sure that all priorities show up as we saw in the sample same thing with all categories and i also want to display cards from a certain date i want to make sure that that date anything after a certain day right we don't want to show the sometimes we don't want to show every card that care completed in history maybe we want to show it based on a specific date so only a cards from a specific date maybe after a specific date right if you want to show only cards for the current month or something like that we have a date validation here when we make a change to a card it changes an update notice we have an updated date here right i want to know when.
That last card was changed whether it changed from backlog to do and so on and so forth we also have the stage what does the current stage of that is that on backlog is it to do is in progress if we create a new task it's going to be probably set the default as the first right so we can do that when we create a new task we may want to set the default here now when i click new task notice we got that macro working i'll go over quickly in that macro however if you've watched any of my trainings before these kinds of things are relatively basic these forms that we've done so we're going to go a little bit quickly over this form style because i want to put the focus on this kanban on this one this is the one i want to really focus on so this is just a basic form i put these in yeah i've used conditional formatting because they're required okay as soon as we fill.
It in and we put in site design as soon as we add it that yellow is going to go away we're going to use conditional formatting for that so if we click on the home and we go into conditional formatting and manage rules we see that we have a conditional formatting we edit that rule we see that it's going to be format only cells that contain blanks and i want to assign a yellow background to those cells that contain blank what that's going to do is that's going to alert the user that tell them that these required if we try to save a task it's going to tell us to make sure to fill in the.
Required fields so as we fill in those fields we can set a priority we set us assigned to a member we give it a category and then we give it a details then we can then save it because it's going to be required so when we save that task it's going to save give us a message saying the task is saved great so we have that we know it's been saved now let's take a look we've been through the admin screen we understand this i'll go over these numbers a little bit later but we've got everything this is our admin screen is simple what we do is we have a list of task cards now remember those cards in the sample all those cards that got created every time we created a new task.
Is going to end up in this list notice the one we just decided we don't have a we didn't have a project id i'll go over that in a moment with you and but we want to make sure that we have everything over here i'm going to show you how we add these member id and project that's going to be with the formula that we're going to go over in index match and then an update date as soon as it's been updated we're going to be able to update it so we've got a list of that so we've got all the task id task name so everything is going to be sorted inside this little database table here then we have projects it's going to be very simple just a regular we have a project id a project name.
And an open or closed status right sometimes if we close a project we may not want that to appear so we've got a macro that's going to allow us to show remember we saw view close project or not so we can view that or not we also have team members now a team member has a member id their name and email this could be helpful if we decide to add some email automation later on a picture remember we have the picture names now those picture names coincide exactly with the picture names that we've set here inside our folder we want to make sure that those names are exact so we have that and we also have a status and active or inactive we may want to filter only those employees show those only those employees with an active status and then run that through an advanced filter so we can have that okay so we're going to go over that so that's basically.
Everything that makes up this individual we also have some named ranges that we're going to go over
Already so let's go into the named ranges and take a look at some of the name ranges i've created it saves us a little bit of time we've got categories here notice we've got the categories here those are going to be all the categories as the data validation the criteria and the exact those are all created based on advanced filters so that's done in vba we have a member id using offset we've been over this offset if you haven't basically offset allows us to create a dynamic named range as our named range grows we see that we also have that so it's going to be using offset we're going.To always start out with the header notice a2 is the header why do we start out with the header that way it's not going to present an error when we have no data or we delete rows it won't have it because that header is always going to remain but if we're going to include that header inside a1 we want to make sure to have one row down we're going to offset it one row down we're not offsetting any columns then what we wanted to determine how many rows that we're counting we're going to use count again we're going to use that header row but we want to exclude it from the results that's why we've got -1 and we want a single column i like these way as opposed to using tables because i can be more specific i can really focus on the names individually and i can use those names in formulas.
And it's a lot clearer for me i know a lot of you prefer using tables it's more of a personal preference right so i i personally prefer i find tables a little bit limiting okay so we have a member name you're also using the offset i've got a priority inside of course we have the priority that's set here in the admin we have a project id again using offset project id and project name so as you can see it's there's some consistency inside here stages of course that's from our we have a stages we're going to need to know the named range of that i want a task id based on the number of tasks and a task name so you see it's really consistent each one has a name each one has an id and that's it that's pretty much it for the named ranges so we're going to create that and.
That's going to help us inside our code all right so what do we want to do well the first thing let me just go over very briefly how we save these tasks now notice i'm going to use what's called data mapping if you haven't seen this before i want to take this task name okay located in f3 and i want to map it to the task card so notice we've got this f3 this data mapping is going to be consistent i also want to do a task id tab project id and a member id so when i select let's say a project id i want to have that project id i now got the project name but i want to show that project id and i want to show it to right here so we're going to write a formula to do that and i'm going to start out right here it's going to be equals.
If error just in case there's any error i want to show blank so the first thing what i do is i want to index i'm looking for what am i looking for i'm looking for that project id so i'm going to look for project id that's what i want to index right here and i want to use what is the row well i need to find that project it's going to be based on the match and i'm going to look up this project name and i'm going to base it on that project name that named range that we created which is right here i want an exact match so it's going to be 0 and i want to use a single column which is here and i also want if there's an error what i want to show blank so that's going to give us that project id i also want to do the same thing for member id equals in this case what we're going to do is.
Again if air also in case there's an error i want to show blank this time we're indexing as well but we're going to index in this case the member id which is here and i also want to use the row number i want to use the match to find it it's going to be based on this member right here if we look it up and based on that member name that's why we created a member name i also want an exact match here and i want to do a single column if there's an error i want to show blank here so that is the formula for member id so if i save that tasks right here there's some one that we just it's going to automatically save again that we go back into the task cards we now have a project id.
We now have a member id we don't have any date because there's no but no updates to that as well but if we put in a date it's going to show up okay so we have those formulas that's where i'm going to save our work now what i want to do now is go over just some briefly some of the code here and i have that inside the vba so what we'll do is go into the developer and visual basic if you don't have this you can use alt f11 to get you there that'll be a quick way and then you can show that okay so we've got some kanban macros we're going to create these macros very soon and we've got some task card creators to help move things along what i did is i created some macros already and they're relatively simple if you're not if you're not new to vba they're very simple.
If you're new to vba i can go over them briefly with you but remember this particular training we're going to focus more on the kanban macros and less on the task card first thing we did is create a new task remember that was the button that's the macro that's been assigned to this new task button so what do i want to happen when i click this new task button i want to clear the fields basically all i want to do so and that's going to include also this task id whatever task has been selected i want to show it inside this cell b7 so i want to make sure to clear out b7 as well task row is going to be based on this task id notice every single task has a task id so what.
I want to know is i want to know the row that's associated so if i put in 1 here i want row 4 to show up so inside the comment if i put in that task id 1 i want four to show up again we're going to use a match formula for that so we're going to match basically whatever's in b7 based on the task id we're going to add 3 because this will return 1 right it's the first one found but i want the row number not the first one found to do that i need to add three because i want that row number right.
When i click here i don't want the i don't want the first one it's been the first one found but i don't want one i want four i want that row number so we're adding three to that so that's going to give us that row number so we want that and i also want to know the next task id and to do that we're going to use the max formula remember this tax id is task id is based on numerical so we want to make sure when you use the max formula plus one it's going to be the maximum of all the task ids plus one if there's an error why would there be an error there would be an error if we don't have any.
Tasks at all there's an error what do i want you up i want one one is the default value if there's an error that'll be the first task id notice start at one that would be if there's no data at all otherwise i want to show the next one so that we have that next number available so when we add we know it's going to be 23s the next one added so that's it for the task so basically when we create a new we're going to clear out some fields here i'm going to set j5 what i want to do is i want to set the initial stage remember when we click we we set this back up right i want to know that initial that first stage which is located right here inside b6 i want to set that.
I want to put that directly inside here inside j5 so that's all we do with the next line of code j5 is going to equal admin i'm going to set that initial stage and also i want to select f3 but i only want to select it if our current sheet is kanban if it's not this is the sheet name kanban that's the code name that we said if it is i want to select this will present an error if the current sheet is not combat it will create an error when we select we cannot select a cell in a sheet that is not currently active so we want to make sure that the current sheet is kanban and if it is then selected all we're going to do is just going to select f3 that's going to allow the user to enter the task name first so the next macro we have is going to load that when i select it.
Basically when i put a specific task id in here i want to be able to load that task so when i run that macro first thing we want to do is make sure that b8 is not empty b8 is going to be the task row if we don't have a task row a row that's associated we cannot load that tab so when i run this macro we can do just that it's going to load all those task details in here i just ran that macro and basically what we're going to do is we're going to go through data mapping we're going to go through all not the tests we're going to start out here because we've already got the task id here the task id is already located in b7 so we're going to start out on column 2. starting out here going all the way to equals column 11 right all the way to 11 we're going to.
Take all everything that's in that task row we've already associated that task row because we know the task row is located in b8 we can assign that to a variable we can then take all the information and put put site design inside f3 put the project id inside of b3 we can put the inside h3 and do that so on and so forth so we can do all of that very easily actually we don't want to put it in b3 and we don't want to put it in b5 so i'll make some adjustments for that why don't we want to put.
Number one in b3 because we have a formula here we don't want that so on column if it equals column right i need to make that adjustment column three right and column 6 we do not want to add those in right because that's automatically done through a formula so 3 and 6 we do not want so we're going to make that adjustment so how do we do that so if here task column does not equal 3 and task column does not equal 6 then do that right so that way we can have that so we want to make sure that it's.
Not equal 3 and this does not equal 6 then what we can do is take all that information and add it to our tasks right so that's going to load up so we're going to take it all the way from our tasks and bring it into our kanban sheet and nice side those cells and that's going to ensure that we do not overwrite those formulas for our member id here okay so the always going to loop through that so that's going to load all that information from our the row here and going to bring it all in.
Here so notice when we run that now it's not going to over so we run that just go ahead and run that and we make sure that it doesn't overwrite those formulas right we don't want to change what's in b3 we don't want to change what's in b5 it's a one way in other words we want to bring it from here in to our database but we don't want to bring it from our database from back into that we don't want to erase these formulas here okay great so we've got that covered so that's how to load it but when we save or update what i want to do is i want to make sure this is a cool trick i've got six different fields one two three four five six different fields that are required here.
So i don't want to say if this is blank if this is blank if this you know so what i'm saying i don't want to put so many if then if they're all checking each one so what's a great way to do that i want to check to see if six different fields are nice so when i click new task i want to make sure that and if i click here so what i want to do is i want to count those fields and i can do that right here so required if we look at this i'm going to use a formula in b11 called count a i want to count the number of texts f3 h3 j3 f5 h5 and j5 using count a if that number is six i know.
We're good to go so notice it's zero but as soon as i start filling in those fields that's going to change if i put in a category if i put in a design and if i put in a member now we only have three blank fields so notice it's three so as we add in a task name as we add in a project so it's going to change notice that number now that it's changing here so we can see that we've given it a priority and now we have all six filled in so we're gonna use b11 so if b11 is anything other than six then i know that they have not filled in the required fields.
So we're gonna use count a for that so that's a great shortcut inside vba to do that so we can say if b11 does not equal six then we know please make sure to fill in all required fields we're going to exit the sub we don't want to move forward unless they filled in all those required fields all right so what we want to do is now i need to determine if the task is new or if it's an existing we're going to use the same macro save task is this same macro that we're going to be using whether it is a new or an existing task so how do we know that well it's going to be based on what is in v8 if i delete this when we click add new here's the new button right here.
It's going to clear out whatever's in b7 if b7 is cleared this formula based on b7 is going to go blank i notice that there's no row associated with this task if b8 is blank then we know it is a new task in that case we must assign it a new row it's going to be row 27 it's going to be task id 24. that new row is going to come in from the first available ones and we know that we have a next task ideas for so those are the things we're going to do if it is the new task.
So if it b80 empty then we know it's a new task so i'm going to type in new task here and then what we're going to do is we're going to say the task row is going to be the first available we're going to say the next task id is going to be based on b 9 so whatever's in b9 that's our nest going to put that in b7 we're going to take basically whatever is located right here inside b9 and place it directly inside b7 i'm also going to take whatever's in here and i'm going to place it directly in the first column located in a27 so we're going to do that in those those two lines of.
Code so here we're going to take that next task id put it in b7 i'm also going to take it in that row that we've just created here and i'm going to put that nest tacit e these are the three items that we do if it's a new task if it is an existing task we need to do just one thing and that's basically take whatever's in b8 that task road that's existing task grow and assign it to a variable so we can do that just right here inside task grow so then everything else regardless from is going to be automatically if it is a new task or if it's an existing counts we're going to do both of that.
So then we're going to run our loop our data mapping basically we're going to take whatever cell is in row one and we're going to place it so again looking in here we're going to look to row one whatever is in f3 inside here f3 and we're gonna place it directly inside here place it directly on our row and we're gonna do that from all the way from two to eleven gonna place that data in here that's gonna create it and then we just wanna then what i wanna do is i wanna run our macro that macro we have not created yet it's gonna refresh that board that single macro is going to refresh and show all those cards and card tasks down here that marker will do that and then what that macro will be creating together and then also what i want to do is i just want a message box saying the task has been saved deleting the task we want to make sure that there's a row in b8.
We're going to give them the option to make sure that they want to delete that task if they have we're going to set that taskrow inside a variable we're going to set that task id inside a variable and then what we're going to do we're going to delete that and what i want to do is refresh the board and then task me to create a new task probably don't need to assign this okay and then task display what do i want to do task display in that case i'm going to go over that a little bit later on but basically what i want to do in that case when i select on the specific tasks here i want to display that task here so in other words you saw me if i click this button here i want to display that inside there so that macro is going to take care of that and.
So that's all we want to do there so we're going to run macros called task show this is the task i want to make sure that we're going to unhide it we're going to have the ability to hide it right i don't maybe i don't want to show it i only want to show the board so when i select on a specific task i want to unhide those rows unhide that which is basically going to be unhide this and then i want to display that so task show we're going to if it's hidden we're going to chew and then what we want to do is visible so if i run this macro all we're going to be doing is unhiding and if they're hidden we're going to unhide them using this column and then what i want to do is i want to show a specific group a group of shapes what group shape that that's this group of shape right here this group if we take a look at this group and that's going to include the save task the delete task the high task that specific group of buttons is called task group i only want to show that.
If we're showing a task otherwise i want it to be hidden so that's how we're going to do that inside that macro and then also we want to do is hide task i want to have a macro that's going to hide it so when i click here it's going to hide those rows and we're also going to hide this task group so when we hide it so notice that we've done that it is that macro that we have signed to that button so when i click show button you take a look now we have a button called hide task this button has been assigned a macro if we take a look it's called task hide that is the same one that's right here so when i had it basically all we're going to be doing is making those rows 3 rows 3.
Through 10 are hidden and we're going to hide that task group that shape group of those three buttons so that's going to hide it okay great so now that we've got that that is pretty much it for all of the macros on the task we're going to focus again put most of our attention on these macros here kanban macros so the first thing what i want to do is i want to create a few buttons i want to create teams so that i can filter out i want to create a set of buttons you saw it in the sample where i'm creating buttons here these tabs for every single member that exists so we can do that with a sample so what i've done is i created a sample shape this is basically a two sided rectangle two round sided.
Rectangle and then all i've got is a circle in here and just some text in here and i've created a group on that again three components of this basically all we have is actually two components we just have this rectangle here with two round sides and we've got a circle in here i've given the circle name called member peg mempic okay i've given this tab this tab shape called member tab and the entire group those two shapes within the group i've given it called sample member group so what i want to do is when i click this button refresh for every single active member for every active member here notice we have four active members one is inactive i'm going to.