Learn How To Create Your Own Drag & Drop Kanban Board In Excel [Masterclass + Free Download]

hello this is randy with excel for freelancers and in this week's training i'm going to show you how to create this incredible kanban board in excel it's going to be complete with member filters we're also going to be able to do project filters based on a certain project or any project we are also going to show you how you can do this automatic move the kanban to the board along with a single click and we're going to show you how to do a drag and drop we're going to be able to drag and drop any card anywhere else it's going to be incredible training creating brand new tasks or editing tasks we've got that too and a whole lot more i cannot wait to share this with you so let's get started all right thanks so much for joining me on this training i cannot wait to share it with you well so what first of all what is a con bond board well a kanban board is a work management system it's designed to visualize your work and it comes from a japanese term meaning visual signals so basically what we want to do is we want to create a process flow from the beginning on the left all the way when things are completed on the right and we want to be able to move them very very easily from one section to the other so if i move this here it's going to go all the way down and keep moving it as we process now i also want to be able to drag and drop different types of 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 run an advanced filter i only want to know those members that are active i want to create the results right here then what i want to do for every single member here i want to create a tab and i want to use create inside that i want to create their pictures i want to take this picture that we have insider file and i will put that inside this picture here based on that name and i want to create that picture inside this particular circle so i want to create the background how would we do that well if we take a look at this you we can do it inside here all we need to do is select it if we're going to do it through just all we would need to do is do the shape fill and we can shape fill based on a picture so if i want to add that picture in here all i would do is just select on a picture and i would do it just like that but we want to do it through vba right so that's how we do it through manually but how do we do it inside bva if you want to clear that picture out all we need is click on format shape fill and then no fill so what i want to do is i want to do it through vba so we're going to do that so what i'm going to do inside vba is i'm going to duplicate this sample then i'm going to give it a name a specific name and i want it to appear all the way along here for every member so we're going to do that that is the macro that we are going to write right now okay so how we're going to do that well inside the vba we're going to go here and what i want to do is called member refresh now i've created some variables that can help us and we'll go through those variables as they come up inside the code they've all been defined it's going to save us a little bit of time on writing these macros okay so first thing we want to do is anytime you refresh shapes what i want to do is i want to delete all the existing i want to delete not this all button it's not associated but what i want to do is i want to delete all the existing shapes so if we call them a specific name we give them a name a distinct name we want to make sure that it's not the same name not the same characteristics as our sample we don't want to delete our sample so we're going to do just that i'm going to make sure that they all have a name so we're going to deal directly with the kanban sheet so kanban and to make sure that we have the right sheet i'm going to put the dot here make sure that the intellisense comes up to make sure that we have that then i'm going to tab over and i want to do 4 each now i've member shape now i've defined member shape and right up here as a shape right here so member shape we've already defined as for each member shape in dot shapes so that's gonna be inside that so we're gonna do something i want to close our loop next member shape so inside here we want to do something what do i want to do i want to check to see if for every single shape on that sheet every single shape i want to check if it includes the name member i want to give it a unique name when i create those we're going to derive it it's going to include the word member so for every shape that has the word member in it notice our sample doesn't have the full word member sample has right m-e-m-b so if we take a look at our sample m-e-m-b and our picture has mbmb but i want to create the full name member so for every shape that concludes the remember i want to delete it so how do we do that if in string first we need to check for that i'm going to check the member shape dot name so i want to check that name what am i looking for i'm looking for the word member if it exists does not equal 0 that means it exists then what i want to do i want to delete it member shape dot delete okay so that's very simple now that deletes every single shape on the sheet that includes the text member now make sure that no other shapes other than the ones we're about to create so now that's it that's pretty much it that i'm going to focus again on for now our members sheet so we're going to do end with now i want to focus on our membership because it is this sheet here that i want to run our advanced filter i'm going to run it i want to determine the last row here using a then what i want to do is i want to run advanced filter have our criteria i only want to have tabs for those members who are active so notice this one status and active so only those with active one up have those results come here then i want to loop through those results starting with three and the last results row and create a tab for each one of those so we're going to focus on our member sheet this sheet is called members notice we have member sheet located right here members so with members again using the dot to make sure we have the name right then what we're going to do is we want to determine the last row what is the last row we're going to use that i'm going to write that up real quick here and that that's going to be basically equals the last row so i used auto hotkey to automate that notices real quick so last row dot range a that's going to be the last row of our members then what i want to do is i want to make sure it's not less than 3 if it is that means we have no data so we're going to do if the last row is less than three then we know it to exit the sub the last row is less than three then exiting the sub okay assuming that it's not we can run our advanced filter again i'm gonna use auto hotkey that helps make things a little bit faster it's a free software auto hotkey okay so what are we going to do now i need to update this advanced filter it's going to be based on a2 through e in the last row so that's the first part we want to do so let's bring this down so we can see both the data the data and the table and we can also see the code so i'm going to bring this down here and we can see both okay so it's going to start at a2 all the way through e going to use e in the last row make sure our variable here is the same as this and the last stitch would be last not result row so this is the last row so and then we're going to run our advanced filter based on criteria what is the criteria it's going to be located in j2 so we're going to update this to j2 through j3 that's our criteria and what do we want those results go i want those results to go through l2 through o2 so we just need to update this from l2 through o2 now i want the last results row now we can now we can automate so what is the last results row so we're going to determine that based on l so that's going to be the last results row is going to be equal to in this case l we're going to use l that's our required field here which is going to be our member id it's going to give us the last row if the last results are less than three then exit the sub okay so assuming that we actually have data what we want to do that is run a loop so now i'm ready to run the loop so but i want to set first thing what i want to do is i want to set a left position i want to determine what the first left position is i want to keep track of it if i move from one to the other the other i need to increase the left position so that they don't overlap all in the same so notice if we take a look at this it's called all member shapes so what i want to do is i want to have our first position of that tab to appear just to the right of this so what i need to know is i need to know the left position of this shape and i also need to know the width of this shape because the width of the shape plus the left position of it is going to be the right of that so we can do that here so i want to set that the left position that's going to i already have that as double variable right here so the left position is going to be equal to kanban equal to when it's called the shape kanban that's our our sheet there and what is that that's going to be the dot shapes i want to focus on again that all member notice the member shape making sure that we have that all member shape i want to know what the left position of that is i also want to know what the width is so it's going to be equal to in this case dot left position of that plus i want to know what the width is so plus i want to know the width of this so i'm going to copy this here i'm going to paste it next and this time it's going to be plus the width and i'm also going to add a little spacer so i also want plus one so i don't want them right next to each other so that's our starting left position as we add tabs members this left position is going to be increased so that's going to be the first place so now we've got the initial left position now we're ready to run our loop again our loop is going to be from inside our members here from 3 to the last member row so we can do that here so 4 the result row equals 3 to the last to last result row closing our loop next result row so this is what we want to work in i want to work in this loop i want to create a brand new tab for each of the members in that list okay so first thing what i want to do is i want to determine the member id so the member id also i have a variable on that is going to be equal to dot range it's going to be based on l and the result row and the result row that is going to be our member id member id so once we have that member id because that's i also want to know the member name so what is the member name so the member memb member name i've got that also a variable as a text right here member name right here making sure that we have the right one here just to make sure i'm gonna copy and paste that equals what's gonna that's gonna be located inside m so all i need to do is just copy this here and then update the column in this case m so that's going to be our member name so we've got our member name and our member id so far so we need that i also want to know what the member picture is so the member picture that's also a string variable is going to be equal to in this case what's located in o so let's put in our equals change this to o and we have our member picture member picture right because we want to place that picture and so now that we have that i also want to know what the picture file i want to know i this is only the name but i want the long file name so that i can pull in that picture so what is that going to be it's going to be equal to basically whatever is in here whatever is located in admin d3 adding the backslash and then adding the name so we can put that into a string variable here so let's do just that right now so inside this we're gonna we've got a brand new one also another string variable located here called picture file this is going to be the entire file name of that picture so i'm going to paste that in here it's going to be equal to admin right that's going to be the starting range what is that look at it d3 range d3 dot values and right and i want to add that backslash onto it so that's very important and the backslash here and what else and i also want to know the member picture that's going to complete that full file name member picture okay full file path of the member picture great so now we have that now we have all the components we need to build out our tab so the next thing what i want to do inside our kanban is basically i want to take this sample and i'm going to duplicate it then i'm going to give it a brand new name and we're going to assign it so that's just what we're going to do now inside the code so kanban got shapes we're calling out that sample member group that's the one i want to duplicate dot duplicate then what we want to do is assign a name to it we've just duplicated with that now i'm going to give it a specific name so that we can refer to that it's going to equal member remember that's the full name remember now i want to remember and what else i want something unique unique which is the member id so the member id once we do that that's going to duplicate it now what we do is i want to work with that so now i'm going to copy this so now we're going to focus just on that so with again kanban dot shapes which shape are we working with that one we just created i want to do a bunch of things with that so what do i want to do with that so we've got that let's remove the double quotes we don't need that there okay so what do we want to do with that well i want to do a bunch of things first so individually notice that this is a group but i want to work with the individual items within the group i want to put their name on this member tab i want to put their picture here so i want to work with the individual items inside that group which can be a little bit tricky but it's not after i go over with you so we're going to this is the member tab shape now we're going to focus on that so we want to work with the individual items so how do we do that with inside that dot group items we're going to focus on just a group of items i want to focus on one called the member tab member tab and what do i want to do with that well i want to give it a text right what is the text frame i want to give it the text what are we going to add i want their member name dot text range dot text that's going to just give it the name what do i want to do it's going to be equal to the member name that's the name that i've just assigned there so we've done that but i also want to auto size it some members might have a long name some members might have a short name so we want to make it automatically so that it sizes its width automatically so to do that we can use what's called text frame and then we're going to use auto size so dot auto size auto size it's got auto size but that's going to auto size the height and the width so we do need to make sure that we reset the height which may be necessary auto size equals true so it's going to automatically size it now what we want to do is i want to set dot height i want to reset the height just to make sure that the height is is automatically doesn't get resized as well we only want to auto size the width but not the height so the height is going to be equal to i want to make sure that it's going to be based on that sample equals to kanban dot shapes dot sample member group dot height so we want to use i want to match the height of that sample again just to make sure that it's set okay and then i want to sign a name dot name i want to assign it's right now the name is called member tab but i want to rename it because i want to name it unique based on that specific member to do that i'm just going to add the member id so the dot name is going to be equal to member tab and the member id so we're renaming it based on adding the member id onto that okay so that is it with the group items that's with this member so we've done that that's all we've done with this tab here we've given it a name instead of we're going to add member name and id and we've also added the text we've had their name as a text now we're going to focus on the member picture right so let's do that inside the code so we've done that within member tab shape here and now what we want to do is i want to focus on the member picture so member picture so what do we want to do with that with dot group items back into the group items this time we're going to focus on the member pictures so member picture so inside that picture i want to do some things with that make sure that we have the end group so what i want to do i want to add in that user picture that's the first thing so dot fill i'm going to fill it with what fill it with a picture fill it with the user picture and what is that picture that picture is going to be located we've already created it it's in the picture file right we've already done it here what is it here we've already defined it right here picture file make sure we get that variable right picture file right here so that's going to add picture file as a fill background fill background okay so once we've done that all i want to do is just give it again give it a name just like this i'm going to copy this and give it the same name so that we have a neighbor but this time i want to i want the member pictured so change this to member picture giving it a unique name and that's going to be important because what i want to do is when they click on it i need to make sure it's unique so that we can basically i want to set the filter based on the id so this id is going to come very very handy when we click on it good so we've got that there and also so we've got group items so now what we want to do is i want to add an and on action we could do that to make sure that we actually add a macro to that tab so we can do that with this dot so now we're outside we're outside of this meta picture but we're still inside this shapes group right so i want to add to this entire group here i want to add a macro so we can do that with on action and what is the macro that we're going to see is going to equal the member filter so that's the macro that we're going to write soon called member filter so we want to filter the members based on that so when we click on that that's the macro they're gonna macro to run to filter by member so we are already have that now what i want to do is i want to set the position of it what is the top position of that tab the top position of it is going to be equal to kanban notice we have to call it the sheet dot range a 13 dot top we can use any column but i want to make sure 8 dot top why are we using a 13 because 13 is going to be right here so i want to use the top position of this but i want to go a little bit higher so to go higher what we need to do is subtract so minus so minus the height dot height of that group height minus 2. so what's going to just set that top position higher right we want it higher than the top position of this higher by the height and then subtract 2 that's going to set that top position right now all we need to do is set the left position so that sets the height put set tab height and next i want to set the left position dot left is going to be equal to we've already put that inside a variable left position here remember that was the first thing we did here the left position is here so we've already set that up but now we just need to increase the left position set left position so that's that but we want to get ready for the next tab right the next tab we also need to increase the left position so how do we do that so i also want to make sure it's visible dot visible in case it's not should be when we duplicate it should be automatically visible but just in case making sure that that tab is visible now what we want to do is we want to update we want to get ready for the next tab but we need to update that left position so the left position is going to be equal to dot left the left position of the current tab dot left plus the width of the current right with plus one so what does that mean that means as i create these individual tabs i wanna i wanna know what the left position of that tab is plus the width of that tab plus one so that we increase the left position keep moving it over and over okay good so we have that so we have the left position plus one end width this is the end width of that entire group that entire tab and then the next result we have one too many end widths here i believe so i'll check on that next row and this is the end width of the sheet so this is the end width of the group this is the end width of the sheet here that we're focused on that was the sheet that our membership so good so we have with the group items here member picture here with the group items and ends with the member tab with the overall group here and then we have with the sheet up here with the membership up here so that looks good i'm saving our work okay so what we want to do is basically take this here and i've already done it remember refresh create that button which we've done here and this is the button here so we're going to test it out this right here refresh let's def member memb and b when they'd have to be there okay go through this and then fix this ariel here all member shape here that's going to be there and make sure we get those names right and then fix this also here too all right let's take a look at that and fill that okay running the macro again all right let's take a okay i like that that looks pretty good there we've got everything's pretty much set up we've got our macro here all macro everything looks very good we've created the tabs based on that looking good so we've got our tabs now so when we click refresh it's automatically refreshed notice that happens that's the macro we've just assigned so now that we have this refresh team macro we can move in to another macro this time i want to refresh i want to get create those cards here so now what i want to do is i want to create a brand called kanban refresh and this macro is going to create all the cards that fill in this so basically what i want to do is i've got another sample here in this particular macro what i want to do is i want to take this sample sample card i want to actually create a different i'll put the details in here i want to put inside this card i want to put the task name i want to add the task name i want to add the project name i want to put in the member again the member name and i want to put in that member picture here again i've got a circle here i want to put in that member picture inside here card member picture and i also want to when i click i want to put the member name inside this shape we've got another one another group called card member name this field here is just a text box and we've given it called card member name i've wrapped i also want to put this forward this particular macro here i want to push this forward using the move card button that's going to allow us to on a single click move it forward in the progress of our kanban and also i want to be able to click on this icon here and have that information show up to show the detailed task items to be able to edit or update that so that's all that's just within a group so we've got four different shapes inside a group right here so we're going to create a sample group that's called the sample card so i want to again duplicate this and then fill in all the information and then place it directly on the board wherever it's good based on that so let's write that macro right now it's called khan bond refresh so to do that we've already defined our variables to help move things along and also we're going to focus this time we're going to focus on our combine but what i want to do again is i want to delete all of the shapes called task card in other words every time i create duplicate this i want to give it a name called task card task card this one's called sample card so each one i want to the first thing we want to do is refresh is delete just like we did in the members i want to delete all of the cards accordingly so to clear out all the existing cards so to do that we can do again four you can run a for each one so do it something like this for each task shape and i've got again task shapes here located in here inside are shapes called task shape that is a shape so for each task shape in kanban calling out the sheet dot shapes closing our loop so next task shape so inside here what i want to do again if just like we did here in string this time we're looking for the task shape of a name what is the name we're looking for called task card that is the name that we are going to assign of course plus the task id so if it is greater than zero or does not equal zero here then i want to do task shape dot delete just deleting that so all we're going to do is run that so it's going to delete all those okay so now what i want to do is i want to reset the top position so resetting the top position so how do we do that so i want to clear out so i've got a top position as we move along down here i want to know what the top position of the last one is because i don't want them to overlap unless we've clicked expand all so what i want to do is i want to set the top position of each one so as we move the cards down we created what's called the top position i want to know the top position so that they're not overlapping i'm going to put that top position somewhere and i'm going to put them all the way down here all the way in row 100 that's going to be that so what i want to do is i want to reset these top positions all the way to a single place so what do i want the first top position it's going to be based on whatever the top position is i want it to appear on e14 so whatever the top position of this e14 is i want to put that and i want to put that top position here so as we add cards this top position changes it increases notice that they're all different so the first thing i want to do is reset inside row 100 from e through j setting that so we're going to write that in to the code right now so kanban dot range e 100 e 100 all the way through j 100 dot value is going to be equal to kanban dot range i'm going to set the top position based on whatever the top position of e14 is dot top that's going to set it that sets initial top position for each card call it card or card tasks i don't want a good okay so we've done that now we're going to but i'm primarily going to focus right now on our tasks what i want to do is i want to loop through all of these tasks right here everything that we've created here i want to create an advanced filter and then i want to loop through of course actually the results so what i want to do is i want to set a filter up again advanced filter we're going to have some criteria because we're going to be eventually creating a criteria through all of these right sometimes we only want a specific project if i've clicked here on a project i only want to sim a single project or maybe i only want to show certain priorities or maybe i only want to show certain categories or maybe i only want to show it based on a current date on a specific date to do that we need to create criteria and another advanced filter so we've got a list of criteria here inside our project id i'll place if i've selected a project d i'm going to place that project id what about a priority i've got a formula here that's going to help us determine so we can be based on whatever's in f11 so in our criteria here inside our comeback whatever is in f11 if f11s show all then i just want it blank right we're not we're going to show all of them otherwise if it's a priority here we want to show whatever priority that is so i want to show urgent so how do we do that if kanban board f11 equals show all then just blank right we're going to show everyone otherwise whatever is in f11 show also a member idea may want to put a member id or a category here let's color that the way we've colored everything else because it's a formula and i want to distinguish between what's in a formula sometimes we can do that so what i want to do is a category same thing we're going to do category category is based on h11 if h11 equals all categories then show blank otherwise show whatever's in h11 and that way when we set a category like such as design here we go and i want design to show up notice it's automatically based on that and also the same thing with dates now remember when using dates it's much better to use a number just like we have each numbers associated with the date so again i want less than or equal we can actually should probably be greater than equal greater than i only want to show dates on or after not great not less than because when i want to show dates greater than equal a certain date so if i have august 1st let's change this to august 1st because most of our dates are above that i only want to show cards above a certain date beyond a certain date we don't want to show the older cards right because they're there in the past we only want to show so i want to create a filter based on this this is the criteria so all the way from p to 3 is a criteria then what i wanted based on whatever criteria has been said i want those results to come right here inside those results we're going to loop through those results we're going to determine the last row we're going to loop through all those results all the way from three to last one creating a card for each one of those and placing that card based on the column basting coloring it based on the colors associated here placing it based on the stage right if it's to do right we know it's going to be placed right here in column f if it is in progress such as this one we know we're going to be placing it here in column g so that's what we're going to do but we're going to do that inside the code so we're going to focus primarily on the tasks so that's what we're going to do so with tasks that's the sheet that we're going to focus on then what i want to do is i want to determine the last task row and that's going to be equal to the last based on a so that's going to give us the last last row if the last task row is less than in this case 4 then exit the sub we need to make sure that there's data again we're going to run that advanced filter okay so we're going to run that advanced filter and i'm going to automate that and let's take a look inside that but that's going to be based on all the way from a3 all the way through k in the last row so we're going to update that a 3 and what we want last task row in this case the last task row a3 all the way through a3 all the way through in this case k so we're going to change that to k now what about our criteria our criteria is going to be based on let's drop this down here and then bring this over here our criteria is going to be based on p2 all the way through u3 p2 through u3 so we need to update that advanced filter criteria p2 all the way through u3 then what we want the results to come in i want those results to come in through a a and then through ak i believe let's take a look at that all the way through a a through a k that's correct a through a k so that's where our results are going to come now what we need to do is determine the last row of those results and so the last row those results is going to be equal to let's go one more line down this one right here the last row is going to be based on a a and the last results row this is going to be our last results row and if our last results are less than 3 then exit the sub nothing we can do if we don't have any results assuming that we do have results we can what i want to do is i want to then sort this is going to be really cool sorting based on priorities so here we've done pounds we've done sorting based on dates but i want to show all the urgent first and i want to show so this is the start i want to show all the urgent first then the high then the normal and the no so what i want to do is i want to do a custom sort sorting based on these so how do we do that well let's say if we're going to just do a custom sort here let's paste the values down here and let's just do let's do put in low down here and put in let's say hi down here and put in something like let's do normal down here okay so what i want to do is i want to create a sort let's say based on this so if we click in here and we go in to sort what i want to do is i want to do a custom sort and i want to base it based on this so based on what based on the values and i want to based on an order a specific order a custom order a custom list so what i've done is created a custom list and i've given it that list urgent high normal and low so this is the same way all you need to do is just type in here the values in next line so if i know we've given it called custom sort based on urgent high normal then low based on this specific order when we click sort it's going to automatically sort it based on that so we're going to do the same thing notice they're all now sorted based on that custom sort so i'm going to do the same thing but inside vba sorting all of those results here all based on these based on this priority here urgent normal load notice how they're all assigned so we want to do that based on that so i'm going to create a sort from a3 all the way through ak in the last row based on ae3 and based on all the that custom sort so how do we write that up in vba so we can do that based on here so we're going to first go with sort and then what i want to do is i want to do dot sort dot dot sort we're focused on the sheet dot sort notice intellisense didn't come up so now with dot sort sword fields i want to clear the sort fields that's the first thing we want to clear any previous sorts so now what we want to do is we want to add a key dot sort fields and then we want to use dot add and then a key so we want to add that key what is that key going to be that key is going to be equal to tasks dot range ae3 that's the one i was just telling ae3 okay and then what i want to do is i want to sort on it's going to equal xl sort on values we're sorting on the values sort on values next up what i want to do is i want to do a custom order so it's going to be the custom order we're looking for customer notice that's an option there custom order and what is that custom order it's going to be equal to we just need to set it now again here what is it it's going to be urgent make sure you spell those right urgent then it's going to be high then it's going to be normal then it's going to be low so that's our custom order and then also i want a data option on that data option is going to be equal to sort normal excel sort normal so now what we want to do is we want to set the range so now we want to set the range okay let's take a look at that sort on sort on the values custom order urgent high normal and low the date option is stored normal once we do that we want to set the range so what is that range going to be dot set range the range is going to be equal to tasks make sure we call out that sheet dot range aa3 a3 through all the way ak and the last result row so that's right and then all we need to do is dot apply we just need to apply that sort and that's it so that's going to sort based on that so we can close that out clear out the spaces and now we have our sort ready okay so we've got our and everything stored now we're ready to loop through that and add our cards so how do we do that so we're going to run that loop for result row is going to be equal to 3 to the last result row and then all we need to do is then close our loop next result row so once our loop is closed we're ready to move on okay so what i want to do is now we want to get a lot of variables put information into the variable about that task so we're going to let's put it get task variables and bring this up a little bit here okay so some of those variables here are going to be the task id task id already into a string variable here it's going to be equal to dot range i want a a all right that's going to come from and the result row and the result row that's going to be our task id task id okay so what we want to do is just copy that and then next up i want to know the task name so put in the task name also a string variable and that's going to be located in a b it's going to be our task name and then also what i want to know is i want to know the project name so project name also another variable and the project name is going to be located in a d a d is coming from a d that's going to be the project name and also want to know the task priority task priority also set as a string variable already priority and then also if you're never sure you know you got the spelling right put it in lowercase like this priority i use upper and lower case and if it changes we know we've got that the right variable correct okay so what that's going to be that's going to be located in side a e column a is going to take on our task priority so we need to know the priority because that's going to set our color of the task priority okay so once we have the task priority i also want to know the member id member id and that's going to be located f so we've got f and that's going to be the member id member id very important because we need to pull the picture and we also need to pull the member name so we're going to need that information and also i want to know the category category and i got to know which column to put it in so the category very very important task category and that's located in ah category is going to be not the stage is going to for the column not the category category we need to filter by i want to put the category inside the text inside the card i want to know what category it is is it development meeting release or whatever the categories you decided so now we want the task task stage this is going to determine what column it is task stage is going to be located in a i this will determine what column we place it in this is the task stage what stage is it on okay once we have all that what now i've got all that all right we've got all the information that's important i'm going to save our work here and now what we want to do is i want to know if we found the priority i want to know what the priorities i've got a list of priorities located here inside a named range called priority what i want to do is i want to find it so i know what color to add to it so i want to know what color to add so how do we know what color so first thing what i want to do is i want to determine to make sure it's found in the list so i've got some named ranges here under the name so found member we're going to look up the member found priority is arranged found the stage and found the task so those are all ranges that we're going to work with inside this macro probably the next one so first thing we want to do is set the found priority it's going to be equal to them would do a dot just to make sure that we've got the numbers equals to just to make sure the intellisense came up admin right that's where our range is located dot range and then this is priority that is the named range that we've created and so what i want to do is i want to look within that range find using find and what do i want to look up i want to look up the task priority that's the one we just assigned inside that variable i want to find that and what i want to do is i want to look in excel values and i also want to look in excel whole so i want to look for that and see if it's found that's going to set this can help us set the task color so we want to find that task we know what color if i want to know what i have signed these numbers so i want to get the task number what are these numbers here well inside excel if we add a specific task color so when i click on let's take a look at view here under the home we have a bunch of colors so notice each range is associated to a color actually so inside vba assigns it a color based on the theme so it's like theme color 2627 i'll show you a little bit more about that but basically it's a sign if you run a macro and you add a color it's going to do that automatically how do we know that well let's do that let's just run a little bit of a test so we know exactly how we got that so i'm going to insert a shape here any shape would be fine and there's the shape so now what i want to do is i'm going to run a macro here we'll call it macro 1 and i'm going to format it based on a color so i'm going to let's just say we give it this orange color here i'm going to stop that macro now now we're going to go back into vba we're going to take a look at this this module here notice it says mso shape style preset 25 we know the color associated with that is 25 everything else is the same so if i know what specific shape style presets on 25 is yellow then i know so i just need to run that macro each time i change a color and we know what number to associate it with that then all we need to do is apply that number inside vba to automatically set a color so that's all we need to do on that relatively simple so that's all we need it's just a very easy way i'm going to delete this module right now we don't need it so we're going to remove the module we don't need to save that so what i did is i basically ran that macro and then just assigned a number for each one so if i pull that number out i know which color associated with that is so that's all we need to do so what i want to do is i want to look for this find it then inside column g and the found row we can then put it in there so that's all we need to do there so let's go inside vba and set that up so first thing we need to do is look for and make sure we found it so now we need to check if not found priority is nothing then it's been found right so we know it's been found then what we want to do is a few things is nothing then what we want to do is we want to set the shape style actually we don't need the end if we can do it all in a single line of code that's fine too i want to set a variable that variable is a string variable we're going to call it shape style it need to go up didn't need to go up here but i've created a shape style as a string right here so it is this shape style that we're going to use that we're going to set that based on that so we don't need the end if because i'll do it all in one line then the shape style is going to be equal to admin dot range g column g and the found priority dot row if this doesn't come up we know we've got something wrong with the variable so dot row dot value what is in that dot value that's going to set up that's the shape style here so we can do that else just in case so we know the shape style is going to be let's say 25 24 giving it a name else what if it's not found else then in that case i want to set it as a default shape style is going to be equal to let's just say g6 and put that default let's put a default i'll put this default 25 here adding this so that way we have it 25 just going to put it just in case it's not found we shot a default color there so let's do g6 shape style is going to be equal to let's do admin dot range g6 setting in default just in case it's not found in case we didn't find the color g6 dot value setting that okay so now that we have that in there and that's going to set the shape style color set shape style color okay good now that that's done we can then move on now what we want to do is i want to look for the members right i want to find the member we've got the member id here now i want to make sure that we find the member i want to pull their name and i want to pull the picture so again inside our members i want to look for that we've got our named range here called member id once it's found i want to get their name in case it changes i want to keep whatever's here and i also want to pull their picture up so i need to know that to do that so again let's do that we're going to again set in this case found member already defined as a range is going to be equal to members focused on the members sheet dot dot range using that named range id member id is the named range where those member id are located gonna dot find in this case dot find as well and this time we're looking for we're gonna look for that member id already inside a variable then comma i'm gonna do again excel values excel whole want to do hole right here so now that we've determined that we're going to set that found member id based on the values based on the hole looking for that member again if not found remember the not and the nothing cancel each other out it means it's been found if not found member is nothing then it's been found right then okay this one we can do end of because there's a few things we need to do on this i need to pull the member picture i need to pull the member name so that way we're going to use an end if if not found members nothing these cancel each other out that means it's been found member found so now that we know the member's been found what i want to do is i want to pull the member name member name is going to be equal to where it's going to be based on the members dot range member name is located in column b and the found member row found member dot row it's been found so we know that that's the member name okay i also want to get the member picture member picture is going to be equal to again nearly the same thing in this case but of course it's a different column in this case the picture is located in column d so we're going to set that to column d and update the note here called the member picture i want to put that member picture inside that card in our special circle so we're going to need both of those information i also want to set the picture file just as we did up here remember we had a picture file up here also we want to know the full picture so i can just copy that here we already set it up here and then i'm going to bring it down here all the way down here so the picture i guess look at it on the entire the picture file is going to be equal to here the admin d3 the member picture we've already defined it's a full file method just as we did before that's it so i want to pull those three things getting that information setting it up that's everything we've done assuming that the member has been found next up now what i want to do is i want to find the stage right now i've got the member information but now i need to know what column to put it on if i know i would need to look for it here but i've already got the stages located right here so i've got them called stages in the admin i want to find out if it's in the number one position i need to note i know to place it inside column five column six column seven so i know where to place that card in which column to do that we need to locate it and see which one it's located on inside our stages so we can do that with just a few lines of code here so again if we can argue let's set it first set the found stage is going to be equal to again admin just as we did before dot range looking inside the stages stages here that's the named range we've created here dot find again using find just as we did before in this case we're going to find what we're looking for we've already set it up here the task stage this is the variable that's what we're looking for here find it where are we looking excel values again i could just copy this here we've already done it here a few times and then just paste it down here to make sure we get it right so we're going to look for that now again if not found stage is nothing then it's been found right then we know it's been found so then okay we look for that but in case what if it's not found if it's not found we don't even know where to place it so we really should skip that right so we can let's do this let's change it up a little bit if found stage is nothing then it means it's not found go to let's call this next task right because if it's not found i don't we don't know where to place it so we should just skip it so in this case i'm going to put next task down here and it's going to skip everything else we're not going to place it if we can't find a place to locate it so we don't need the end if here here we're going to skip it so if it's not found if found stage is nothing go to next task nothing else so assuming that it is found we can then continue on so to do that what we're going to do is we're going to assign a task column i need to know what column to place that task on task column is going to be equal to the found stage dot row plus one why is that well remember i need to know if it's on backlog i need to know to place it in column five that should be did i do minus one this should be minus 1 not plus 1. right why is that because if i want to place it in if it's found on row 6 if it's found on row 6 here i need to place it in column 5. so we need to subtract 1 from wherever it's been found so there we go so minus 1. so the task column we've already set that now what i want to do is i need to know the top position what is the top position that we're going to be placing that so the top position is going to be equal whatever is in e and the specific column so we can do that it is going to be equal to kanban remember we put it on row 100 that top position is going to keep track of it kanban top position dot cells because we're using a variable here in the column and the row we want dot cells we know it's row 100 in that case and the task column that is what's going to keep track of our top position dot value get top position i'll show you that to you one more time so we can get top position and as we increase as we add cards this top position is going to change as we add a card it's going to change as we add a card so we always know the top position good so we've got that set up so we're going to set that top position all right so what do we want to do now now what i want to do is i want it we're ready we've got everything ready so i'm ready to create that card we've got a sample shape here just like we did in the members i'm going to duplicate this and then we're going to make updates to the current one so the first thing we want to do is take the sample card and we want to duplicate it so let's do that right now kanban dot shapes that sample card dot duplicate and the first thing we want to do is give it a name so what is that name that we want to give it well it's going to be going to be equal to the task card task card remember everything's going to have it's called task card because that's what we deleted up here the first thing we did is we deleted all the task cards so we want to give it the name of task card but i want to add to that because i want to know what task is it what specific task and task id so we're going to give it the word task card plus the task id that's going to have a unique name for each group task card with task id so notice that every time i type in width it adds end with which is automated it's usually good but sometimes a little bit annoying so that's also auto hotkey so we've given it a name now we can work with it now we've given it we duplicated it we've created a unique name now i want to work with that specific group so how do we do that so i'm just going to copy this here task art so with kanban dot shapes putting in that shape task card and task id we're going to work with that specific shape so task id so now again i want to work with the individual items i really don't want to work with the group itself right i want to work with the individual items inside the group i want to work with the card back i want to work with this so that's the first thing i want to do is work with the card back so we're going to call with group items card back so the first thing is setting that up so let's do that with dot group items individual items inside that which one is the card back i want to change the color add some text and do a few things with that so that is it card back that's going to be called set back card details so inside that we're going to do some few things so first thing what i want to do is i want to assign a color based on now i tried to use a dynamic variable it didn't really work and i'm going to show you this so we're going to use select case on this and i'll show you why select case and it's going to be based on the shape shape style we've already defined that shape style shape style so in this case case is equal to 24 so in that case i want to set the shape style so that with this card back i want to set a say cell dot in this case shape style is going to be equal to right 24 right so we just have to search down here for 24. now i tried to use this as a variable but it didn't work in other words to have a single line where we said it just didn't work so we're going to use this select case as opposed to what i meant is that this is a text and this as a variable didn't work so we're going to use select case that's it great so that's for 24 so now all we need to do is just duplicate that a little bit and we're going to do the same thing so in this case 25 let's take a look at what we have in here so we have 24 25 21 and 26 so that's just what we're going to do here 21 24 the next one's let's drop this down here so we have it we can see that 25 21 and 26 so 25 changes to 25 relatively easy a little more code than i would have preferred but it works good 21 is going to be the next one and then the last one is 26 21 the order doesn't necessarily matter 21 and then the last one is 26 here so if the shape style is 26 and then put then 26 otherwise it's going to stay the same it won't change so there we go so 21 24 25 26 and actually let's do i think i wanted to change i don't like blue on this one i'm going to use green i want to change the order around green and then blue i think it's 22. i think i had it as my my notes showed 22. so i'm going to change that to 22. so 22 they're just different styles you can mess with them see which one you like 22 i like better 25 24 25 21 and 22 and select so that's it so what that's going to do is going to change it based on the shape style based on whatever shape style is it based on this shape style which is the number so now we've set the background so what else do we want to do with this groups items i want to set the text so the text inside that text frame two dot text range dot text we're going to set it up what do we want to do oh i want to add the task name it's going to be equal to the task name and what and i want to add some more i want to add vbcrl a new line that's autohotkey that help automated that i want to add also in the project name i want to put that project name here project name here and i also want to add in the category so adding the category what is that category so i'm going to add in those three items task name and then a brand new line the project name and then another line the category so putting that all that information in now what i want to do is i also want to assign a unique name for this background so dot name is going to be equal to call it card back and the task id i want to make sure that everyone's got a unique a task id so that's going to assign a unique name for that that is it for our back right that's it we just did so with those three things what did we do here so let's just take a little bit of review with this back here first we assign the color then we assign the text and then we we change the name from card back we added the card back and the id so that's it now what i want to do is i want to focus on two other things i want to work on this circle here this one right here i want to take that information here and i want to add to that okay next up after that what i want to do is i want to focus on this circle here this one here card member pick that one there hard to select card member you got to be right on the border there card member pick i want to add the picture in there then this hidden one here card member name i want to add the name to that so those are the what we're going to work with right now so this one here first thing what i want to do is i want to add dot group here let's go ahead and do that dot group items and which one are we going to focus on the move card button i want to add a task i did that this is the move button we also need to work on this this move button here when i move a task to another one i need to know what task number so we need to assign a specific task id to this also everything being unique so we can do that so group items here move card button want to do something with that dot name giving a brand new name is going to be equal to again move card button and the task id right so everything is going to be giving it in let's call this the move move task button so we've got that also what i want to do is now that let's do the member picture so again dot group items that which group item we're going to focus on here we're going to focus on this member picture the one that's hard to select inside here that one there card member pick so with this one i want to fill it with the user picture just as we did so group items pasting that in a card member pick this one dot dot fill what i'm going to fill it with fill it with the user picture dot user picture and that is going to be of course that picture file name that we've done right here this picture file that's the one we're going to got that full name right here i'm going to place that right down here that's the picture file add user picture okay so once we have that we're ready to move on now what i want to do is i want to focus on the name so again dot group items this is one what are we going to focus on this is the name field this one right here card member name so i'm going to copy that and now what we're going to do is we're going to add that card member name dot text i'm going to add the text i want to add that name in there so card member name it's going to be here dot text frame dot text range dot text equals the member name member name okay ad member name okay good so we've got the member name there located in there that's gonna take care of it now what i want to do is i want to do the view card button remember we also got a button here this little button here right here when i click that i want to view the card above so that's called the view card button so we're going to focus on that what do i want to do that just basically want to rename it based on the id so again dot group items because we're focused on it that individual view card button dot name i want to rename it it's kind of the same macros so associated with it but we're going to rename it so view card button is going to be equal to view card button and the task id so everything gets that task id so we know so we can differentiate between the view card button with task id shouldn't use the word with otherwise going to automatically end with okay so we've got the group item we've added all that okay so now we've done everything we're ready to set the position so now we're going to set the left position so we've added all the right information already we've updated this we've updated the button we've added the picture we've added the name we've added the text now what i want to do is i want to place it directly wherever we want it so we're going to set the left and the top position so dot left is going to be equal to kanban dot cells in this case let position we can use any row it doesn't matter one but the column is going to be very specific to the task column that we just sent task column dot left okay so that's it dot left all right so we've set kanban.cells any row task column.left and the top position dot top is going to be equal to the top position we've already put into a double variable top position set top position of the card and this is going to be the set left position set left position okay so now that we've got the top position we've got the left i also want to make sure that we have the width right just to make sure that we have the right width i want the width to be whatever the width of the column is so we can set the width is going to be equal to basically it's going to be this right whatever we have here plus the width so the width is going to be equal to this cell dot with dot width that's going to make sure that we set the width of that and i want to do it a little bit less than that so it's going to be that width minus 2 so that should be fine okay that sells all right so kanban equals cells the width of the column -2 i don't want it to take up i want to be slightly less which should be sufficient and now what i want to do is i want to set the height now the height is going to be different right we we might want a full card height remember i showed you that they're going to be expanded but i want to know based on this notice expand all tasks or we can reduce them so the height might be the top position so it might be different so we want to set that actually what i would want to do is i want to update the top position sorry about the height the height is going to stay the same i want to set the top as the next top position right but what is the next top position is it going to be the full width of the card for example let's let's duplicate that and go over sample the top position is going to be here right but what about the next one the next one is it going to be here right here right here or is it going to be here right remember we want to show whether they're expanded or not i need to know what the next position is and this can be based on whether we're expanding them all expanding is going to look the top position going to be here or the top is just going to be here so it's based on a setting here it's going to based on this setting here based on whatever's in b10 if b10 is true then we're going to set these to show up uh not on top of each other but below each other so that the next top position is going to be whatever the height of this is plus whatever this whatever the top position is otherwise we're going to set a specific top position here so basically i need to differentiate which one and that's going to be based on b10 so if we look and if we unhide these rows here we take a look at b10 b10 is going to be expanders show based on whatever this is so b10 is going to tell us the top position is the top position going to be here which it is now or is it going to be collapsed which is going to be about like here so now we just need an if then statement to differentiate that inside the code because we need to set that next top position for the next one so if kanban dot range or excuse me let's see dot range and we can use dot range b10 b10 dot value equals true then what do we do so then we're going to call this expanded right expanded let's spell that right okay so then if it's expanded then what i want to do is i want to set the height kanban dot cells we're focused on row 100 row 100 and the columns variable here task column and that the value of that what's the value going to be it's going to be equal remember we want to set basically all i'm doing now is resetting this top position based on this based on the the height of this plus a little bit it's going to be there in the top position so it's going to be the top position value equals dot top position of that let's say plus the height plus dot height of it and i'll add a little bit more onto that maybe plus two plus two because i don't want them to be directly else else what does that mean else that means it's currently the class view in other words i want to set a specific top position based on the top position of this and then we'll add let's say 25 under that so we have that there so else let's say not expanded not or collapsed not expanded it's called the collapse view expanded or collapsed view there's that c again collapsed way we can have two different views based on that collapsed view so else this one we're going to set i want the going to copy this here but instead of the height we're just going to add a specific one let's say plus 25. so that's what we can set two different settings based on that okay good i like that there that looks very good so that's end with that's just about it for this macro here we'll correct any issues there's usually a few issues next hassle end with this is the end with for the specific group that we're working with the combat this group right here this is for the shape id group card this is for the card back individual group items so this one now we got to end this and width is for that entire group gonna loop through all the rows okay so that looks very very good so let's uh update that and we'll go ahead and save our work before running our macro and then we'll focus on any so i'm gonna run that macro see what other issues we're gonna face application we don't need that let's take a look at that that should be apply not application right we're applying sorting i know everybody saw that but me okay and then we'll take a look at this issue apply okay let's take a look and the method of range this found member remember i usually when you have this you've uh we probably have a different one so we want to make sure that we're using the fine okay i believe this should be member remember not member id okay and take a look at that and then all right let's take a look at that okay all right let's say we've got one let's continue on okay let's take a look at that okay we've got a specific design urgent let's take a look at the task cards one result here that looks pretty good here we have a stages backlog urgent so it should show up in red member id let's take a look in that we can get rid of we don't need this one this sample we can remove here that was just for our purposes here this one we can also remove here we'll remove some of those filters so we can see more of it okay all right i like that everything looks good now let's what i want to show is in this case all priorities we're going to show all categories and then let's refresh that now i've already assigned this here refresh comma this particular macro has already been assigned if we take a look at this refresh so we've already done that previously so we're going to refresh that okay that looks good i do want added screen updating we need to update the colors a little bit but everything is looking good all right let's take a look inside the code i don't want 21 i want 26 here 26 and 26. i like that better let's change that to 26 here 21 is not going to work that's okay let's refresh that take a look at those colors i got some nicer colors there oh that looks good and instead of gray i'm going to use a different color i want green i want to show green here green here 26 so we don't want that all right we got it 24 25 26 22. i like that that looks good 24 25 26 22 this should be 26 and 22. all right i'm pretty happy with these final colors 24 25 23 26 that's going to give us these colors here all those they're all prioritized notice in this case they're everything's good i like that we just don't need the bold on that probably not maybe the bulb i'm not sure so all if you want to update that i'm gonna undo the do some text here all right i like that let's looks pretty good make sure the top is set here everything looks good except for the bolt and it looks pretty good okay good so we've got that now what i want to do is a few more things i want to run some code that automatically when we select on a specific this icon here i want to show that when i select on it i want to show that when i select here how do we do that well we've already added that notice that there's been a macro that's already been assigned to the original if i right click here excuse me it's off the screen right click look at the macro say task display now we have that same one now we can go over that macro task display so let's go into that macro it was a very simple macro inside our tasks screen so all we're going to be doing is notice that we have application color application color is the name of the shape that called that particular macro so what is the name of that shape if i take a look inside the name of the shape we've renamed it remember review card button we added that and then we want the task id so if i know that the task id is set remember all we need to do to load a task is place that task id directly in here so i need to extract that tax id from that particular button that created it so we can then click on that so again we can do that with just a little bit of code right here so we're going to use the replace what i'm going to do is i'm going to take the view card button from the name of the shape that called the macro if i try to run this macro here it's going to create an error why is that because there's no shape that called this macro i called it directly from here so that's always going to create an error this particular macro when we use application color it is only going to work when we call the macro from clicking on a shape so what i want to do is i want to take out the words view card button and i want to replace it with nothing what is that going to leave that is going to leave only the id notice that if i take this text out here what it's going to leave it's going to leave this particular task id if i take that id and i place it inside directly inside here what it's going to do is going to generate the task row if i've got a correct task row i can then load that task using a macro so we can do just that so all we need to do is in b7 place that id there then task show run the macro that's going to make sure that we are showing that so notice if we've hidden it and now if i click on that it's going to automatically show it right we want to show that information and then what i want to do this filter button should be down here notice we're going to have those filters here all right keep that from moving here bring it down here a little bit so what i want to do is if they're already hidden i want to make sure that we're showing it so we're going to run the macro that shows it it's going to display all that task details in here and then the last marker there we're going to run task let's just going to display those details so that's it so we've already got that so now what we want to do is i also want to filter based on that when i filter i want to filter based on a specific member i also want to filter based on a project and i want to be able to move these so how do we do that so filtering is relatively an easy macro to write why is that because if i select on an employee here what i want to do again is i want to excuse me member employee whatever if i want to do that i just need to extract the member id so i need to get rid of this text i need to extract this id and where do i want to place it if you remember inside our tasks we have already some filters already here all i need to do is place it directly inside r3 then i need to run the same macro that we just created then it's going to say only those ids that are going to be those only those member ids that are one or only those that are three all right only those members with a specific id so that's relatively easy to write we can write that right now so let's write in that macro here so inside the kanban here we're going to go down here we're going to say member filter so that's what we're going to so with kanban that's the sheet that we're working on and i want to do something if i wanted but also again we have this if we click all what do i want to do if i select this one i want to make sure it's for all of the members so how do we do that we want to make sure that the member id in r3 is cleared out that there's no members before running that macro again so i need to know what shape called it if if it's the member shape if it's this shape that called the macro something called all member shape then we're going to clear out any criteria that may have been added so we can do that here if the application collar equals all member shape that means the name of the shape then do something else do something else so what do we want to do if it's all then what i want to do is inside the tasks dot range r3 remember that's when we just went over r3 is the cell that's going to handle that dot clear the contents clear any member id from that otherwise let's just let's put a note here let's put a note right here call this all members oh and else it's been a specific member specific member okay if a specific member then again we want to what we want to do is we want to extract that member id using in string we can do that using the replace so in this case i'm going to copy this here this replacing a value here that value is going to equal what equals using replace i want to remove the string replace application dot color and what do i want to remove i want to remove some of the tags so we can do this there's different things but here's a little bit of a tricky part they might select member tab 2 here or they might select the picture here notice we don't know which one they're going to select so i can't just remove the text member tab i can't just remove this text right because what if they click on the picture i want to make sure that the macro has been assigned to all of them so all i did was uh basically i want to remove seven characters seven characters so all i want to do is i want to know the left in fact the left of seven characters replacing that just remove that so removing the first seven characters if i remove the first seven characters here or if i remove the first seven characters here notice it's the same so we can do that it's going to leave me with the member id so we can do just that inside the code caller in this case replace what are we going to do replacing the application caller the name the shape they called it and and then all what i want to do is i want to do the left application dot color 7. i want to remove the first seven characters what's that going to leave me on and i'm going to replace them with what i'm going to replace them with nothing okay so what that's going to do let me fix that don't need it twice here no not twice okay all right so we're going to replace the application collar we're going to take the first seven characters out of that text whatever they called it and we're going to replace it with nothing that's going to leave us with the member id so and i'm going to take that member id i'm going to place it in r3 what's left after that all we need to do is just run the macro con all i need to do is run the this macro here kanban refresh that's going to automatically filter out those members here so pasting that directly here it's going to run that mac actually place it down here but either one is going to be just fine okay so that's it so now if i save the work so all i need to do is make sure that we've taken this macro and applied it to the sample if i if i assign that macro to our sample and click ok when i refresh this it is this macro that's going to be automatically assigned to this so when i click on fred it's going to show only threads and notice we have some kind of strange things going on here all we need to do is just use application screen updating in our refresh so i'm going to add that into here in our refresh application dot screen updating equals true and then i'm going to add it false up above so that we can do that but i want to make sure before any exit subs here so it's really the sort that creates that often so we want to make sure after last exits up equals false okay so now when we do that now we don't have that notice everything so now we're showing only heralds now we're showing only mary's now we're showing only fred and now we're showing all of them fantastic okay good i really like that but what i want to do also is i only want to set specific projects what if i only want to set a project i want a list of all the active projects or maybe closed or open based on this and i want to put them down here so that when i select on a project i only want those projects to show up inside here so how do we do that with just a little bit of code so let's do that down here inside our tasks we've just written this one member filter i want project refresh so i want to do something i want to create a refresh but i want to create a list of those projects i want to take all the projects here and i want to place them and i want to put the project id and the project name and i want it based on the status but how do we know whether we're going to show all the projects or only the open projects that's going to be based on an option here notice we have view closed projects so right click here and we format the cells we see that this connected to b13 so if we take a look at b13 we see that show closed equals true so we want to view the closed projects so if this is true that means we're going to be showing all the projects including those of which have been closed so we've got a criteria here it's going to be based on b13 if condom board b13 equals true then we're going to have no status right because we want to show all of them otherwise we will only want to show open so if i then select this viewing the close tabs this goes to false inside the project this goes to open so now we're only going to show those that are open so the criteria is connected to that option all right so to do that all i want to do again is run an advanced filter based on this using our criteria having those results come in here then what i want to do is i want to take the project ids and i want to take the project names and i want to bring them into our kanban board i want to put the project ids right here i don't want to put the in column b and i want to put the project names in column d so that's just a little bit of code to do that so let's do that right now so to do that we're going to be focused on i want to clear any existing projects first thing i want to do is any existing projects all the way from b15 through d and down i want to clear the contents of all those cells so we can do that right here kanban dot range b15 through d9999 dot clear the contents clear contents okay so we're clearing everything else out so we're going to focus pretty much on the project so with projects that's the sheet here making sure we got the sheet name and then what i want to do is again the last row equals so we're going to make sure the last row is going to be equal based on a99 get that last row okay so the last row is less than three then we're going to exit the sub now we're going to run our advanced filter now the advanced filter is going to be based on all of our columns here so we're going to look in the project it's going to be based on relatively a2 all the way through c in the last row so we just need to update that a2 through c the last row advanced filter our criteria is going to be located from g2 through g3 and our results we're going to appear in i2 through j2 so we're going to change this from i2 through j2 and also we're going to focus on the results now i need to know the last results row so last results row is going to be equal to 3 i'm going to switch that order up a little bit so so the last results was going to be equal this case is going to be based on column i getting the last results row i want to make sure that we notice if it's less than three then we can exit the sub that means there's no results row okay so we have the last results row now what i would like to do is i'd like to sort them right based on ascending i want to sort those projects so it's equal to fine so with dot sort running that sort i want to sort the projects based on names so that they're alphabetically first thing i want to do is sort the fields we're going to clear those fields so sort fields clear and after we sort the fields i want to add a key to that so sort again focus on the sort here sort fields and we're going to add and this time we're going to add a key so what is that key that key is going to be located here inside based on the name so it's going to be j3 so we're going to set the projects have to call out that sheet dot range j3 is where it's going to be going j3 all right so what i want to do is i want to sort on sort on in of course it's going to be value sort on equals excel sort on values and the order i'm going to use in this case it's going to be ascending so the order is going to be equal to ascending excel ascending right one from a to ascending and then also what i want the data option is going to be normal data option as it always is option it's going to be equal to excel sort normal okay taking a look at that to make sure we got it right projects j3 sorting on sort on values the order is going to be excel ascending the data option is going to be sort and normal okay so now all we need to do is set that range so set the range is going to be here set the range what is that range going to be equal to in this case we want to call out the sheet again projects dot range and the range is going to be based on i 3 all the way through j i 3 through j and the last results a last result row that's considered and the last thing is dot apply make sure we get that apply right okay so now we got applying that application very helpful okay and then end with so that's going to sort them once they're sorted alphabetically i just want to bring them inside our kanban so we can do that with this kanban and i'm going to bring them into two different statements because i also want to bring the project's id in column b and i want to bring the project name in column j so kanban dot range b15 starting out in row 15 through b and the last results row last result row now of course our results are gonna come from row three but they're going in fifth row fifteen so we need to compensate for that by adding 12 plus 12 on that dot value is going to be equal to inside our project's dot range i3 i3 through i in the last results row and the last results row so once we have that that's going to bring in our project ids so because the project id is very important we're going to have to filter by project id so we need to know that okay what i'm going to do is i'm just going to copy this and we're going to update that for the project names so our project names are coming in column d so we're going to same row so that's not going to change and then of course they're going to directly come from column j so inside j inside our projects is where we're going to come from j okay so that's our project names so once we have that we're pretty much set up for that we don't need to do anything else so we're going to run that and make sure that we have that and of course i want to run this macro directly when we select that option box so i want to know that here so that's the macro that i want to run when i select this view open or close i'm going to assign that macro and place that in there it was already assigned so when i click though that's cool i forgot to show you that that's working good expand all notice we have i forgot to show you that so notice it's working so remember we're adding 25 to that and forgot to mention that so that's cool okay so this one view projects i already have some conditional formatting notice our project ids came in here all for reviewing clothes but i want to view unclosed it's only going to show three right because we have that criteria inside the project now only showing open so john's custom app is not located in our results because it has been closed already and it is that for therefore not okay so now what i want to do is when i select on one of these projects i only want to show this only those projects from that so how are we going to do that well what i'm going to do is i'm going to take the project id associated on the selected row and i'm going to place that project id right inside here once i place that in here it's going to we're going to run that refresh and it's going to only be those projects here so how do we do that well that's on the selection change event so let's write up a selection change event when a user makes a selection change on this and i want to make sure that there's actually a value in b okay so that's going to be on the sheet itself focused on our kanban sheet right here this is the sheet we're focused on here and it's going to be a selection change right here so if the user makes that selection change we want to do what is the target here so inside the selection change first of all if they select larger if target dot count large is greater than let's say two then exit the sub then exit sub this prevents errors so now we have we're going to focus on this what are we going to focus on i'm going to focus on d if they make a selection based on between d15 and d let's just say 999 large auto project is nothing and i also want to make sure that b contains a value and range b and the target dot row dot value does not equal empty then do something then do something right i want to make sure that we actually have a project what do i want to do well all i want to do is take whatever is located in b in the target row and place it directly inside p3 so we can do that from here so root tasks dot range p3 dot value is going to equal to range and again all we've done here we could do this range b and the target dot row dot value but i also want to do something else i also want to place i want to know what row we selected so we can highlight it i've added some conditional formatting here i want to know that selected project in b12 i want to know that so how are we going to do that well we can copy this here be in the target row and i'm going to put that inside b12 so range b12 dot value is equal to b in the target row set project id we can do the same thing here setting the project id setting the project id also in the task that's going to be for the criteria set project id for the criteria to run our advanced filter once we have that criteria all we need to do is run our macro to refresh it kanban refresh there we go so that's it so all we do that's it and now we also have conditional formatting that's already been set up so i'm going to save our work and now when i select a specific it's only going to show those projects notice fretter's website photoshop so everything else grocery store and i've already had some conditional from it let's just take a quick look inside that conditional formatting and of course conditional formatting manage rules i've set it up based on specific rule that rule is if b12 is equal to b15 whatever we've selected b15 meaning the row the selected row the target row here b starting at b15 so we know that's the one it's going to affect and we want to make sure that b12 is not blank that means whatever project we've selected that means whatever be in the target row if it's equals this 4 here is equal to 4 here it's going to highlight that row 1 here it's going to equal 1 here it's going to highlight that row and we've all now what about all projects what if i want to select all project if i select d14 i want to do something i want to what do i want to do well basically what i want to do is i want to clear out whatever is in key and i want to run the macro again so we can do that here if we select d14 so in fact down here if not intersection in this case d14 d14 if they select on d14 we're going to do something else then i want to clear out whatever's in p3 so we can do that here range so we can do a few things actually i want to do a few things b 12 i want to clear out the contents there so i want to clear out any selected b12 dot clear contents okay clear clear out project clear clear out project id and i also want to clear from the tasks tasks we've got it down here already task p3 i just want to clear the contents out here so we can just copy that tasks p3 dot clear contents and then refresh the compound so that way if they select clearing out the criteria and selecting it so now when i run that combat and i click on all projects let's select something else click on all projects we're going to select all projects notice we could also highlight that row but i think it's okay that we do so now we're showing all projects again okay great so i've shown you how to use the criteria but now while we're on the sheet why don't we set the criteria here here and here if the user makes a change to f3 h3 or j3 we want something to happen so let's see that's a worksheet change so if not intersection making a change again to f11 j11 or h11 so let's update that f11 j11 or h11 then what do we want to do then it's relatively simple any change they make then just refresh kanban refresh and why does that why is that going to work automatically why do we we don't need to set any of the criteria why do we why do we not because our criteria are already set here based on the formulas notice show priorities show categories and display from date those are based on formulas so if we look in here and we see the priority based on a formula priority based on a formula category based on a formula update date also based on a formula so we've already set our criteria here so all i need to do is run that so now if i only want to show a specific priority let's say i only want to show urgent it's automatically going to show that based on that or if i only want to show high it's only going to show that our criteria is already set based on a formula and based on the date or maybe we only want to show a specific category let's say we want to do design i don't know if we have any design here scope we've got scope here scope we only want to show scope so now we can filter beyond that okay we i should probably have a clear one here right i don't have a clear in other words it would be nice to have one button that said went to all categories or something like this but that's okay you get the point you can create that okay fantastic so we've got that already set up now all we need to do left is just the two macros one to move it and the other to allow us to drag and drop anywhere we want that's it and then we'll be done okay so basically what i'm trying to do is when a user selects on this arrow i want it to go to the next available position down in the column below so if they select on this it's going to move over if they select on this it's going to go move to the next column the first available space based on that so we can do that in just a little bit of a macro back into the kanbans we're going to call this con mon move cards so what do i want to do on this one so this one we're going to focus on this one the con man of course so it's going to focus on with kanban and now what i want to do is i want to know i want to isolate that task id inside this button here right here we have that it's called move card button 19. we know that 19 is the task id so to isolate this i need to remove the words move card button that's what i need to remove from that to extract that task id so to do that we're going to say the task id is going to be equal to replace we're going to replace it with what application column that is the name of the macro this should be the name of the shape that caller and we're going to remove what we're moving this move card button we're going to replace it with nothing what that's going to do is going to leave only the task id once it's called isolating the task id isolate task id once we've isolated it we can work with it so we know the card name let's set the card name the card name we know is a very specific card name the entire card name we want to move this entire card name is called task card and then the task id so we can set the card name to do just that the task card here and plus the task id so doing that we just need to set that inside a new variable card name is going to be equal to task card and the task id set card name because i need to move it so i need to associate that so if what i want to know is if they make sure that they moved it so we're going to set that up i want to make sure that the first thing is i they can't go beyond this right i want to make sure that they're not going but what is this equals column take a look at this so i want to make sure that it's not beyond column 11 or anything like that so i want to make sure the left position doesn't go beyond that right because they can't move it beyond that okay so the first thing what we want to do is if the shapes if dot shapes card name not someone working on that entire group dot left is greater than or equal to let's just use j1 right j1 dot range j1 i want to make sure that they're not moving beyond the last column they can't move it back not dot valley but dot left dot left what is the left position of the last column then i'm going to tell them something message box something like cards cannot be moved beyond the last column beyond the last column right they can't go beyond that and let's see okay beyond that let's just put in let's couldn't let's instead of last column let's put beyond uh let's add something dynamic in there because it could be beyond the and let's go ahead and put in whatever's inside j13.range j13 that way in case they change the name so for example i want beyond the closed column or whatever they've put in here i want to put in whatever is in j13 i want to put in that so beyond the closed column and space column a little bit more helpful to actually exit sub can't do anything unless we try to move it beyond that place okay assuming they're not in the last column we know we can't call we can't move it so first of all the task column is going to be equal to dot shapes card name i want to know the current column card name dot top left cell the top of the cell dot com i want to know the column so the column that's the column but i want to move it to the next column so the task column is going to be called that plus one good so we have that so i want to set the top position the top what is the new top position the top position is going to be equal to dot cells remember we're keeping that top position in row 100 so 100 dot task not comma actually task column dot value so that's the top position we're always keeping it there so focus on that with dot shapes now we're going to focus on that entire group card name we're going to replace it we're going to move it okay so what is that dot left the dot left position of that new location is going to be equal to kanban in this case dot cells one we can use row one the row the row doesn't necessarily matter but it's the column that makes all the difference so the task column dot left so that's the left position of that but i also want to set the top position the top position is going to be dot top dot top position it's going to be equal to the top position okay good so we've got that now what i want to do is i want to update the left position now what i want to do is i want to update the top position update top position i want to do it both in the existing cell so what i want to do is here's what i mean if i move this one over i need to update the top position to be here and i also want to update the top position of the previous one lower so basically this one's going to increase this one's going to lower okay so we can do that with the following lines of code but it's going to be based on whether we're expanding tabs or not right if it's going to be based on this i need to know whether how much we're moving over so it's going to be based on whatever is located in the b10 format control and then b10 right so we can do that with this lines of code so update the top positions if kanban dot range b10 dot value equals true then we're showing the full card then the height then else so else and end if so we've got so b10 equals true we're going to do some things kanban dot cells row 100 task column dot value is going to be equal to the top position top position of that plus the height of it right the entire height of that card plus two but what i also want to do is i want to do something basically like this but for the previous l task on the previous column the one that moved from minus right but in this case the top position is going to be equal to minus the height minus 2 in other words we're reducing right i have to reduce if i move this one from here to here i need to reduce the top position i need to reduce this one and i need to increase this one okay because we're going to reset that so that's going to be the task column minus one right the original task column minus was just subtracting it's going to be the top position minus the height minus 2. okay so that's going to cover it okay that's only if we're using the full cards but what if we're using this one what if we're using the expanded this one here we only going to increment 25 pixels up 25 pixels that separate them so we need to also update this i want to increase this one by 25 and i want to decrease this one by 25 to do just that so else let's just call this expanded and then we'll call this shrunk or not expanded either one okay uh shrunk because they're shrinking this one we want to increase this so in this case this one here the cell is going to be here is going to be equal to basically whatever the top position is plus 25 incrementing it just 25 and also inside this we're just going to decrease it by 25 so we can just copy this here this one the previous cell task column minus one is going to be the top position minus 25 that way we're going to set the existing and the next one automatically the previous one i should say okay so we've set that up so we've updated the positions of the top position we're good to go but now what i want to do is if i move this one to here what i need to do inside this specific task i need to update it from to do to in progress what do i mean by that this particular task needs to go from to do whatever it's wanted and inside column i and the task i need to find the database i need to know what row it's on i need to go into column i and i need to change it from to do to in progress inside the database so we need to do just that we need to find it first so how are we going to do that well we're going to let's call this update stage in database okay so to do that we're going to set the found task is going to be equal to tasks dot range then in this case task id we're looking for that task id we already have the task id we're going to find it what are we finding we're finding that task id we've already got that and then excel values excel hold okay so we're looking at that now we're going to see if it's found if not found task is nothing then it's been found then we know it's about what are we going to do if it's been found then i want to update that okay so we don't need the end of because it's one single line of code so we don't need that then what do we want to do inside the tasks dot range we're looking for column i and what is that row the row is going to be the found task dot row that's the row that it's been found on so we know the row dot value what is that value going to be it's going to be equal to whatever is in row 13 and the task column that's the name so value equals dot cells row 13 in this case and also what is that column it's going to be the task column whatever the value in that dot value is going to be our update the task stage that stage you're going to update that in there that's good that's and what that's all we need to do and we're going to test it out it is that macro that we're going to assign to that arrow so all we need to do is copy this macro here and apply that to the sample so this is our sample here right click here assign the macro paste that it was already assigned actually then all we need to do is just refresh you know once we refresh it's going to assign that macro here so we'll try that out all right let's take a look notice how notice how it jumped down it didn't jump out why is that but if i click it again it does well also explain something you see how it's so close let's take a look at zoom in look how it's so close to the edge it's actually in the previous column right so when i place these i really should place it right about here right it's too close to the previous column it's got to be a little bit over there so why don't we do that when we refresh that notice how they're just too close they're recording the previous column it works so why don't we do that let's update that when i refresh the column i'm going to place it exact a little bit over the edge not quite on that i think that's going to help and i'm going to make it more secure so how do we do that well that's going to be inside the refresh so i really want to get it right off the border so that's going to be inside our kanban refresh so if we scroll down here to the left position when we set that left position what i'm going to do is i'm going to add one i'm just going to get it off that border right there and now let's take a look expand and drink okay so now we've got it slightly off the border which is what i want notice it's not if we zoom in notice it's not on the border so it's going to record the right column now and all i need to do is just move it over perfect and move this one over and notice we've moved this one over it kind of collapsed it so what we want to do is we want to make sure we're updating it we updated it too much here so again updating it notice it's not setting right so let's update that a little bit to make sure that we have the right order so if we move this one over i'm going to make those updates inside the code right now so let's look take a look at this okay so all we need to do here is actually take whatever is in here and deduct the height so it's not going to be the top position it's going to fix that so basically i'm going to take whatever is in here and deduct the current height we don't need to do -2 that's going to be sufficient now i'm going to do the same thing inside here whatever whatever's currently in here and just subtract 25 that's exactly what i want here so it's doing that so now when i move something over here let's move it over here and now i move this one over here it's automatically going to move to the bottom i like that better okay very very good the last thing i want the two more things actually one when i select on this i want to be able to drag and drop it there's two macros so i'm going to be able to dress select on this and then drag and drop it wherever i want so if i want to move this back to a location i can do that with drag and drop so we can write two small macros to do just that one macro is going to be when we actually select the task the other macro is going to be when we check for the move the first macro is going to happen when we select a task the next macro is going to run a loop and see when we just wait for a move it's going to wait to see if we've moved it somewhere so first thing we're going to do is select the task so with kanban i'm going to do this all right task id is going to be equal to again i want to basically take out that task id determine that replace the application color and then this way we're going to remove that card back just as we've done before so card back we're going to remove that text and it's going to leave us exactly with that task id so again isolate task id very important okay so when i have that what i want to do now is i want to set a card name the card name just as we did before is going to be equal to the task card and the task id okay so what i want to do is i want to set the left position of that so i want to make sure the first thing is when i've selected it if i if it's uh let's say we have it on here the first thing what i want to do is i want to make sure let's see expand all there we go the first thing i want to do is i want to bring it to the front when i select it i want to bring it to the front so that we can see it right we may want to view it so we can use z order for that so we can do that in dot shapes let's do card name dot z order dot z order and what do we want to do i want to bring it to the front so bring to front bring to the front i want to bring it all the way to the front in front of everything else in case it's not visible in case we're shrunk in the cards so that's going to bring it to the front okay so put that in bring to front of other cards now once we've done that what i want to do is i want to set the current left position i know i want to know the current left position and i want to know the current top position because if it changes i need to check that has it been changed is the left position changed or is the top position changed so what i want to do is i'm going to record the current condition of the current location and we can do that through here i'm going to unhide these here and i'm going to put just this little table we're going to put that task id here i'm going to put the left position here in p and i'm going to put the top position here and i want to know if it's been moved or not so inside p9 is going to take the current left position inside p time is going to take the current top position i'm going to put that here so that's what i'm going to do right now so dot range p8 i want to put that task id there p8 dot value it's going to equal i want to know the task id equals task id inside p9.range p9 is going to take on the left position p9 is going to be equal to in this case dot shapes card name dot left position i want to know set current left position i'm going to do the same thing with the top position if i can get the spelling right ever okay so we've got that the current inside p10 is going to do that so all i'm do is going to copy this and then i'm just going to make some updates to that so instead of this p9 is going to be p10 p10 is going to take on the current top position so that we know i can check in another macro i can check to see if it's changed so check the current top position okay once we have that i also want to make sure that p11 is set to false so it has not moved i'm going to set it to false if that changes to true it's going to be that so dot range and i'll show you why that's important coming up in the next macro p11 we're going to set that equal to false set task move to false equals false set because it hasn't been moved once it's been moved it's going to go to truth set task move to false now we're going to run a macro now when we select it what we're going to do is we're going to a macro that macro is going to continually check for changes it is this macro right here called check for move so i'm going to copy this macro if i can hopefully i can i'm going to copy this i'm going to place it right here run macro to check for movement that's the map last micro writing today for move thanks for sticking with me on these long trainings i really do appreciate it okay so does this macro going to run all that we're going to do is take this macro and assign it to the back of this so if we take this macro we can use our sample this back here this one right here this card right here assigning the mac or right click and assign the macro sorry it's off the screen here assigning that macro so kanban select task click ok so that way when we refresh it when we refresh the compound it is that macro that's now assigned to every single one if we click assign macro we see it's already been assigned to that so when we select a card we want something now when i select this card i want the position i want 19 i want the left position the top position falls so everything is working good and now we're going to run that macro that is this macro check for move that we're going to run right now so with kanban again the same sheet we're going to do that first of all i want to know to make sure that p8 can't be empty right we need to have a task id for some reason p8 is empty we cannot move on we can exit the sub or select correct so if dot range p8 dot value equals empty then exit the sub nothing we can do if we don't have a correct task id okay so now we can assign the task id it's going to be located inside p8 so copy this here and equal to pa set task id once we set the task id what we're going to do is we're going to run account remember i want to wait a certain amount of time allow the user when they selected it to go that and actually one more thing i want to do actually i forgot to do that i want to notice how it came to the front that's perfect that's what i want to do bring it to the front but i also want to select this task one thing in here right here i want to do one more thing dot shapes this one card name i want to dot select i want to select that select shape that way it gives the user the ability to move it so when i select this now it's going to be selected now they can move it around right so that's one thing i wanted to do so inside this check for move once they've selected what i'm going to do is i'm going to wait a certain instance i want to create a count and then a delay and give them some time to move it so that this macro continually runs and gives them a certain amount of time to move it before the macro ends so we can do that so we're going to do 4 count delay this is already a long variable equals 1 to let's just say 100 000 100 000 that should be sufficient and then closing our loop next count delay and how long do i want to run this for i want to run this for a long time unless p11 changes to true so first of all i'm going to do events what this is going to do is allow us to do other things during when the macro is running so we want to be able to do that so but i want to do it until some point if for some reason if before the count is over if the user has moved it then we can do something else so if dot range p11 equals true then end it so equals let's do equals true then end right so what i'm going to do is i'm going to mark as soon as we've determined that the user has moved it we're going to end it right so we'll show you that in just a moment so with we're going to focus on this card here dot shapes in this case i guess we could set the card again we can do this here card name just we've done up here we could do it up here same thing here counts card and make it a little easier for us card name a little more clear card name so now with shapes card name we're really focused on this so what do we want to do i want to see if it's been moved well how do we know if it's been moved we need to check to see if the left position or the top position is the same as it was when we clicked it so we can do if dot left the left position does not equal what that's located in p9 kanban dot range p9 p9 dot value if the left is different or maybe the top is different right has the if the left position is not this if the top position is not this then we know the user has moved it so we can do that dot range p9 or dot top position does not equal kanban dot range p10 dot value then we know it's been moved then card has been moved we want to do something okay so we know it's been moved but what i want to do is i want to check for an incorrect move what if they move it all the way up here what if they move it here or what if they move it over here or way down here we need to check to make sure they've moved it in the correct position so we need to make sure otherwise if they moved it to an incorrect position we're just going to refresh the kanban so to check that we do that if in this case dot left is less than let's say e1 right they can't move it beyond the left of e1 right can't be left can't be farther left in column e so if the dot left is equal to con but i need to call out the sheet again because i'm within the card.range e1 we can use any row that's fine e1 dot left if it's less than that or right or maybe the top position is too far to the top so we need or maybe the dot left is greater than j dot left is greater than khan range we can use j1 in this case oh let's go k1 k1 j1 is fine that's the proper column dot left let's say -5 a little bit less than that or maybe the top position dot top position is less than what we can't be can't be less than let's say row 14 right we got to make sure it's not less than that or the top position is less than let's say kanban dot range what is the range let's call this e13 that's on any column would be fine e13 dot top position that one if it's less than that then we need to tell the user to please move it to a correct row message box please make sure to move card within the stage columns so now the first thing we want to do is i want to i want to refresh it so kanban without any changes kanban refresh so we've got that and then exit the sub nothing else we can do if they haven't so give them another opportunity after we've refreshed so assuming that they've made changes but the changes are correct they within that now we can do is we can set some information up so we know that that so i want to know the task column the task column is going to be what where have they moved it to is going to be equal to the top left cell dot column what is that column so that's going to be the top of the psalm of the new location top left column new column okay so we've got the new column and i also want to know the top position top position is going to be equal to remember we've already set the top position again based on row 100 equals kanban dot cells 100 cast column what is the position of what is the top position of for the new column it's going to be located in row 1 and a task column dot value so that's the new top position so now what we can do is we can set the left position we can set the top position so dot left is going to be equal to kanban dot cells we can use any row we want task column dot left actually we need to play dot left plus one remember we're not we're not going to be right on the left we want to move it a little bit over set left position okay good and also again we want to update the top positions here we want to update the top positions just as we did here on when we move them remember that okay so we can do that right here i want to update that here based on that the shapes calling updating the top positions updating the left um and the top positions here so we need to do that right here updating that so i'll add that in just a moment so in fact let's do this update top position top positions inside row 100 positions okay so how do we do that i'm going to paste that here because it's based on that so if b10 is true we're going to set the test column minus one setting the previous but this one we want to know the previous column we don't know what the previous column was well we can make it easy why don't we just add a variable let's make it a little bit easier let's do this call this previous column set that up pretty easily there and then we can put the previous column inside here and then we just set it up so we always know what the original column was that they've moved it from so all we need to do is add that column inside p12 when we select it we're going to set that up and then we'll just add a variable so here inside dot range p12 i want to put the value equals what is it it's going to be the column so what is that column is going to be that card name in this case dot shapes what is the shape card name dot top left cell dot column okay so that's gonna be in p12 so that way when we select something we want i'm gonna don't need this here in fact i'm just gonna pause this i wanna run this macro just yet we're not completed with this macro i don't want to run just yet so i'm going to reset that okay reset that so all i want to do is make sure that that would column nine right we want to make sure that that column equals column i think we need to add one to that that's perfect okay good we're good so i want to say that i want to know the previous column why do i want to know the previous column because i want to update when we move something i want to update this in the previous column so we're going to add that variable now i hadn't added that before so i'm going to add that in the long run just in case we put it in here so we can do it right here in the long variables i'm going to call this previous column previous column as long then what we want to do is once we know the previous i'm going to put that in a variable inside our new macro right here all the way down here setting that previous column i've got so many macros here right here is where i wanted to do it task id previous column previous column equals dot range p12 previous column so we're going to set that previous column because i want to reset that i want to know what's in p12 and reset it based on that so when we go down here now what we want to do is i want to update the top position of the previous column so not the task column the task column previous column we know the previous column right we want to subtract basically the height i want to subtract that remember and again here because we've moved it from that previous column right so we're going to take else and now when we move something i want to know the updated column if we're using the shrunk version we're going to subtract it by whatever that whatever the current value is minus 25 whatever the current value is minus the full height okay good so i like that so we've got the previous column now we can continue with just a little bit of our code inside here now that we've updated the heights of that so also what i want to do is when i move something i need to update the database just like we did before we need to update so if we're moving it from the to do we're moving it to the completed i need to know what we're updating it we're going to move it to the completed so i need to take this inside the to do here and i need to update it to to complete it or whatever it is so let's do that right now inside the code here so again we're going to do just like that we're going to set the found task just as we did up here and update it so we can just copy this since we've already set it up here inside remember when we moved here right here when we moved the task this one right here move move card here so again i want to just copy this all we need to do is update this update the stage just as we did before i'm going to bring that down right into here so right here so update the stage we're going to set the found task test range task id we've already done taking care of the task id right here so we can set the find just as we did if not found it found nothing all we need to do is update the tarot based on the task column row 13 just as we did before very very simple there so now once we've updated the task i'm going to set p11 to true so in this case so what that's going to do is going to stop the loop kanban dot range p11 dot value equals true so when we set that to true automatically if it's true we're going to end the duo we're going to end this loop as soon as we've recognized the change so that's all we have to do there okay i like that there putting that in here next to calculate clearing that out we don't need those extra spaces here extra space here and so this is the end width with our shape this is the end width with our sheet okay so we've got that and we're going to do is save that now what we need to do is see if there's any issues now we can uncomment this out now we're ready for the drag and drop to move okay that's going to work right there and then what i also want to do is i want to make sure that again just to make absolutely sure that we've set true regardless of that so before that i'm going to set p11 to true no matter what okay let's take a look at that let's refresh expand refresh and refresh our thing and take a look at that all right now when i select this it's going to be selected i want to move it over here perfect and i'm going to refresh that again to make sure that it sticks right that we have the new location i'm going to move it back over to the left here have it pinned down perfect and then again refresh here to make sure pin all right excellent excellent and like that way that looks let's click on here we can show a task we can hide our task and we have our drag and drop kanban board in excel it's been an excellent training thank you so much for joining me today if you like these trainings i've got a great mentorship program that's going to show you how to define design develop and deploy your own excel applications for passive income i'm going to include the links down below that will help us out that full course is now ready you don't need to actually go on a weekly module basis you can actually have the entire course all 132 hours at your disposal now alright thanks so much i cannot wait we'll see you next week don't forget to subscribe comment below and hit that like button we'll see you next week thanks so much [Music] you

Channel: Excel For Freelancers

Views: 119,811

Rating: 4.9472423 out of 5

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Excel Kanban Board, Kanban in Excel, Excel Kanban, Kanban Excel, Free Kanban Board, Free Kanban, Free Kanban Software, Project Manager Kanban, Excel Free Kanban Board, Kanban Free Excel, Kanban Board Excel, Excel Kanban Free, Free Excel Kanban Board, Customizable Kanban Board

Id: 3qbU5S8ZaEA

Length: 148min 45sec (8925 seconds)

Published: Tue Aug 17 2021