How To Create Your Own Tournament Generator With Leaderboard In Excel [Free Download + Masterclass]

hello this is randy with excel 4 freelancers and welcome to the tournament generator in this week i'm going to show you how to create this incredible tournament generator right in excel complete with all the match details you can imagine unlimited tournaments and a full leader board i've got so much to show you i cannot wait so let's get started alright thanks so much for joining us this week we're going to have a lot of fun this week we're going to focus on a tournament generator this is a really amazing application i think you're going to love it you're going to be able to create unlimited tournaments we'll be able to edit existing tournaments we'll be able to put in start date end dates and we will also be able to add and update match details where we can put in a location an event a team a scores for each team and we'll be able to select a winner and we're going to do all of that in this training so i hope you'll stick with us i bring these to you each and every tuesday absolutely free including this download you can even get this application absolutely free as well all you need to do is click the links down in the description and we'll get that available to you either with your email or your facebook messenger so we got that to cover i hope you'll do like this all you need to do to make sure you get these is just to subscribe to our channel i bring these to you of course every tuesday so all you got to do is click that subscription and the notification icon bell and we'll make sure to get that over to you right away okay if you like these applications you like creating these applications i've got an incredible single click reports dashboard application and 16 hour masterclass you're going to learn all the techniques to create the best applications you can within the most amazing dashboard that's going to include single click sorting automated filtering you're going to be able to do tab based reports whether it's graphs or sales or charts we're also going to be able to do drill down drill up functionality i'm going to show you that in the course along with a full on dynamic pop-up picture and of course we've got complete date ranges also custom reports and you'll be able to do toggle columns in each report so that is the advanced dashboard master class i'll include the links down below if you want to get that it'll help you and help us create these videos each and every week for you for free all right so let's get to it i've got a lot to cover in this application first we're going to go over an overview of it and we're going to show you exactly how it's going to work and then what we're going to do is we're going to get down to the nitty gritty okay this particular shape these are all shapes these shapes get recreated it's super fast so not only are we creating not creating each individual shape but we're doing interest so if i were to delete every single one of these shapes except for this particular icon this trophy icon this one just simply gets uh copied and pasted but if i were to delete all of these right just like that and all i need to do is just take out the selection and recreate it it's going to automatically get recreated each time so we can do that so it is super super fast in fact it's so fast that you can't even see the shapes getting deleted and recreated so i really love working with shapes because there's so many possibilities it's far quicker than any cell based changes okay so what happens is we can select a tour it's going to load that tournament and what we can also do is we can create a new one so if we want to create a new tournament we just give it a name so let's just say world cup and of course this can be for any type of event right not just soccer you can create teams or players or anything like that you can put in a start date and the team quantity save it and automatically it's going to be generated all we have to do is then select here a team or select any type of player these could be players and then you can set in a location for that if you want to do that we can put in a location let's just say freder's stadium and then what you want to do is maybe you can add a date and a time we'll skip that we don't need that we can put in the team one score right who what team one and we put the score and we could also put in a team two here and then we can select a winner from one of those two teams we can select the winner who wanted and when we save those details it's automatically going to set both of those it's going to set the color then which one won in green which one in red lost and then who advanced on and then you can select that and notice that we've already got this team here you can then select another team who did they play and then also we can select a winner based on that so it just keeps moving along saving those details and then advancing them so you can really set up any kind of a team also what we're going to be doing in our patreon account i'm going to be adding additional updates on this so that we can also select multiple team quantities so we will have additional team quantities so that all be generated based on the quantity of the team so whether you have 32 teams eight teams we'll see what we can do that's going to be put in our patreon account so each week i've got an update to these applications so i hope you'll join us for just uh a few dollars a month that's either with our silver or gold members we can create this i'll be creating updates based on your suggestions and your ideas so this is fully dynamic so for example if i decide to go to setup and i don't want to call them teams i want to call them players and it's not no longer a math maybe it's called a game or maybe it's a race or maybe it's a set as in tennis right so you can put it in here and then you can also award points how many points are worded to the winner and how many points are loaded word to the user and then also maybe we want to show the leaderboard so we can show a full leaderboard those leaderboards can be based on the tournament right so based on a single tournament like this tournament or based on all tournaments so for example this tournament only has a few teams here so that's how we're going to get that who's won or lost however if you want to base it on all tournaments all we need to do is select it so i'm going to show you how to do all that and more so it's going to be really fantastic training so once of course notice now it says players or players however we want to do it and maybe it's a driver maybe you want to race so as soon as you change it it changes all the way throughout the application maybe we want to say match or something like that i'm not a huge sport fan so if i missed anything i'm sure you'll let me know now it says driver so match details here driver so it's fully dynamic setup screen is very very easy right all we need to do is set a folder where our pictures are located and of course notice that i've got these flags that demonstrates the team so we'll go with uh let's say to this one here and you can see that we've got the flags associated with each country when you get this application you can set up any pictures players drivers icons you name it you can add a picture and it's a very simple database where is that kept it's kept here to call teams and players so all we got is a name we've got a picture icon here now that picture icon file name is associated with a file name inside the folder and i've got that folder set up i've got a folder with all the pictures here associated with that so you can see that now if you want to get all these pictures and play with it also inside our patreon we'll make sure for all members have these resources so whether it's pictures you can pick these up too i'll make sure to include them on our patreon platform as well okay so that's it and i think eventually maybe we'll have a column for total score but you can have that but we do have that covered inside this is mostly a database but i thought that kind of would be helpful in the future on a per team basis but we really do have that covered here inside our leaderboard here so we've got total points how many they won how many they lost or on a single tournament how many did they you know win or lose on a single tournament so we've got that and so that's it it's a very very simple database then what we do is we also have our our tournament database right notice that i just added a tournament notice i didn't add a name here so if i were to change this to 2022 of course i could change it also inside here so if i change it here now it's set up here so we can also load that if i want to change it i can make a change and then all i need to do is just save that tournament save it here and hit save and also i'm going to show you how to hide and show these columns so right now we're showing the leaderboard but maybe we want to hide that maybe we also want to hide our match details these are all of our match details right here and once we save it that becomes hidden so we only want to show it once we select a specific match here it is that match that will show up here so we have the event date and time notice when i select this match here it is those two teams that's going to show up and if i want to add a score in here so let's say afghanistan one so maybe they only got 80. it is once i save it that score will be saved so i select on it again it shows the teams the score even if i but if i maybe i want to change a team i can do that too i want to change a team to ruba smaller name and save those details it's going to automatically update in here so we're going to show you that so we've got the tournaments database indeterminate we have the team quantity we have the start date and end date very simple database here then we also need to save those matches i need to save the individual countries or teams or players i need to save their scores i need to save all this information in a database now this is associated with a specific tournament or event right here so that's going to be saved in our tournament match database right here for each one of those we're going to say we have to have a tournament id notice each tournament has an id right here tournament id one through four right so each tournament has their own id so we need to save those individual matches on a per so we have all of these matches that are associated with terminal id number one all these associated with two and four and nothing for three we also have a match id this is unique to every single match and i'll get into what this what these numbers make sense we have the location as you saw we have the date and time a team one who is that team one team one is always on the top so this is team one right here this is team two team dudes down here so each match has a team one and team two team one is here team two is here team one scores here team two is here in q6 so we have the scores for each one we also need to know the winner so we also have the score and the points right so this is the score and the points but what about the points the points are based on what you set up in the setup screen so you can assign these winner points points these are the points awarded to the winner these are the points ordered to the loser right so that way we can keep track of the total points that way if we know the points we know how many total points all right so what else do we have we also have so that's for each team so each team gets a team name the score and the points we also have the winning team and then i've got the database row that's associated with that four five this is the row it's based on a formula and that way we can keep track of what row relatively simple also you notice these let these numbers here along with the letters those are ranges based on that's data mapping so b2 is based on this the term and id so if we look inside the tournament sheet and we scroll over to a hidden column and we look at b2 we see that is the tournament id if we look here to k6 the location and then we go back inside our tournament we see that k6 is based on the location so that's data mapping when i save this i need to know where what column to place it in so we know what column to place it in when we look here we know it's going to be going column three here column c here and we do that for each one of them that is data mapping if you've seen my videos before you probably understand this is going to help us not only reduce the code but make it a lot faster and it's both for when we want to save this data save all this information to the database and when we want to select it then we want to load this data back into our excel form here data mapping is going to help us do that i'll walk you through that inside the code so that's it that's all we have it's very very simple as far as the database is concerned the code is going to be really amazing i'm going to show you a lot of things we're going to go over some formulas we're gonna work with shapes we're gonna work with connectors this is so fast even these individual connectors get automatically done through vba so everything is done through vba everything is recreated and it's based on some samples so we have three different samples here i've got this connector sample this sample gets duplicated and then it gets connected to the shirt this particular shape here actually it's a combination of two shapes it's a group right this background is a macro but there's nothing to do with that just yet so we have two pieces of this group what is this background shape it's a rectangle with rounded corners called sample bracket or sample brk we also have the picture located with that which is where which is simply a circle in which the picture will be embedded and that's called sample pick so this group simply gets duplicated and then it gets filled accordingly so if i change this back to let's just say team right and i change this back to matches five we can keep that at that so it's automatically linked so to do that what we're going to do is we're going to link it with the team name team name now i've created some to help us some named ranges in here so if we take a look at this one and we look up it's called team name if we take a look at this one this is called match namer match nm this one is called winning points win points and this one's called loose lows points okay so this is called picture folder so we have those five name branches in the setup that helps us when we want to refer to that either both in formulas or within the code so this the setup is very simple here there's not much there's a lot of room to add we can add a lot of things i'm thinking about adding like you know if it's a tiebreaker or draw or something what do we do back then or you know so there's a lot of really good features that we can do we can assign maybe we want to put like draw points like each one of them gets five points so i've got a lot of ideas that i'll i'll be doing on the update on patreon so we've got that so i wanted to be able to know what that is so now what is all this stuff so that's the name ranges for the setup and those are the shapes that we're going to be recreating this particular shape this last shape this is for the picture called sample standing picture this is the one that gets duplicated when i show the leaderboard notice that each one is associated has a picture that's been associated with it right so i want to duplicate this one and then simply fill it with the picture accordingly so we're going to go over step by step everything you do on that all right so we've got all that to cover let's just go over the few details here and see what they're for i want to know if they search for a name notice that we've searched for a name right 2000 and what i want to know is i want to know the row that is associated with the tournament that they've selected so this is row 2 world cup 2020. so excuse me that's the id that's associated that's fine the id so how do we get that well that again that is the id that's associated with the world cup id number 2020 right if the user is searching i want to make sure that there's an id that's returned if they enter something that doesn't exist or clear it out there's going to be no id associated with that so if they've searched for something and there's nothing here we cannot load it in so we would let the user know that we can't do it right so we're going to get something like that but if we do have it we want to make sure that we load an id so to do that we can use the named range first thing what we want to do is we want to index a tournament id this is a dynamic named range based on those tournament id so when i type in into the named range term and id i see that it is a dynamic named range based on those ids using the offset i also have another named range for the tournament name so we've got those two so with that in mind if i decide to index the tournament ids and i'm going to use a match based on the tournament name and i want to return the id based on the name that's found located in k2 if i can do that and it returns the id that i know the user has selected all i need to do then is take this id copy it here which is the actual tournament id so when we load it right all i need to do is copy it over here which is the actual tournament id here that the user has selected if i know the tournament id then i want to know the row that's associated with that right to get that i want to make sure that we have that available to us and to see that of course it's in a hidden row so all we need to do is just select on an id and we need to edit the tournament and you can see here in b4 we do have a tournament row that is the row that's associated with the id here so if we simply run a match and we add two to that we're simply looking for the row that's based on this id we're adding two because our first one starts in row three so we don't want the return one we want to return three if the user has to select the first one so if we select that that very first one here we want to show row three and not one that's why we're adding 2. we have the next tournament id which is based on the max formula we can use the max formula as long as all the tournament ids are numerical values we're going to add 1 to that because i want the next one so what i'm going to do is look for the max right i want to know the maximum in this case it's four if i add one that's going to be five so i want to know the next one right if i decide to add a new tournament it is this five that's going to be assigned to that new tournament id so i want to make sure that i have that available so we can add that in i want to know what selected match id these are associated numbers okay these numbers here are when i select it i want to know this is 1 for column 1 this is 2 for column two notice it starts out three for column three so each one is an id then this is one two three four right then each one has a number this is one this second one is two this second one is this third one is three and this is four right so i wanna know when i select a match i wanna know column one one match one and two column one match three and four column one match five and six this of course would be column two matches one and two so we're keeping track of it so it's an id based on one the column and two which of the two matches so this one would be column four one and two there's column four only has one you know only one and then we have the eventual winner of the entire match here the entire tournament here is here okay so that's how we derive the match id it's based on the column and the two numbers the count numbers so this column has eight so this would be of course seven and eight okay so i want to know the selected match row when i select a match i want to know what row is associated with that that means the so row associated here so this is seven right so term and id number one match id one seven and eight is located on row seven row seven i if i'm going to load that information i need to load the teams the score the points and i need to bring all that information into here i need to know what row is associated with that to get that row it's a little bit more trickier why is that important because in this case let's take a look at some of these things if you take a look at this 1 1 2 one one two notice there's two of the exact match ids but they're for different math for different tournaments this is for tournament number two this is for tournament number two so we can't simply match just the id we need to combine it right because two matches now of course if i decided to which i probably could have done is added one more but there was already enough numbers if i added one more number onto here and i combined the tournament id also in here then that would be unique so in other words instead of one one two it maybe i would put two one two so the first two but it was already confusing enough you got three numbers so i could have done that and then this would have been unique so keep that in mind you could do that then i would have had a unique match but that's okay because there's no problem all we need to do is use the sum product the formula which is a great formula to determine the row that's associated one with the term and id and one one two because i wanna know this row how do i know that 1 1 2 is either row 19 or row 4 well i can use the tournament id to determine that in a sum product formula and then extract the row so that's what we're going to do directly inside here so right here so what we're gonna do is we're gonna use the sum product there's two i wanna know two things i wanna know the tournament id is gonna be equal to b2 in this case one and i also wanna know when b7 equals the match id b7 is the match id right so what i have all those now just to so we let's take a quick look at the formulas and go into match so we can see what match id is match id is the dynamic named range for this so this covers all the match ids so where are we going to find that so we want to find those two instances there and i want to know also the match the tournament id right so we've got match tournament id so when i know all of those then i can get that information here so match tournament id match tournament id equals to b2 and the match id is b7 when those two instances are correct i want to return the row of the match of the term and id i want to return the row that's associated with that so that is going to return our row that means when we have the term ideas one and when this in those two instances i want to know that row so it's very important i also want to know what the team one points when i decide to award a winner i want to know which one so if i decide team one's got team one's the winner team two is a loser so team one is awarded ten points team two is awarded two points and that's based of course right here on our setup screen so inside our vba code when we award it now if i were to change that if i were to decide that uzbekistan is going to take that win i would just select here and change it and save those details so now notice if ubex9 has moved forward and it is them that has those 10 points right here team 2 now has 10 points team one has been awarded two points so that automatically keeps track of that right here so let's switch that because it doesn't make sense notice that we have that okay so let's return that back to the way it was because it's a nice look and then so i just wanted to show you oops let's click on that one more time so i wanted to show you that you can award points and it can be changed automatically now it makes sense as vietnam the country i'm currently in is um winning this most of these races okay so let's take a look at this so we have i also want to know the match id of that is when so notice right this take a look at this when i've selected this second one two column two one and two this match id is two one two right so if i decide if i've got to set a winner here let's go to let's go to a blank one let's go here okay so we've got some blank ones if i select here it's gonna be the same i want to know who the winner is right so if i in this case let's use 1 3 and 4. so the winner of that is going to be this one i need to advance it to the winner so if i select a team here one team here and i select uh let's do australia and let's do um andorra so if i select a winner here notice that the drop down list here is only the two options that i've selected here and i save those details i need to advance the winner on to this one this one of course is two one two column two one and two match two that's where we get there so i need to know the winner and i need to know the row that's associated with this so that one's just added here so if we look back on here we see that we've just added row 29 here we are in tournament id number three two one two and australia is team two so we've added australia team two so notice here there's no team one associated but australia is team two if we were to add a team here and i'm gonna add one here then we also have this so then we're gonna this is gonna be team one so if i select a winner that winner is going to be team one of the next one so here we have albania and save those details now we've got a match up so albania won and armenia lost now we have the next match up it's albania and australia so inside our tournament here now we have team one as albania and let's go ahead and freeze this column script i like that better because it's gonna be easier as we go down here freezing the page here okay so now we can see the header so now we see team one is all mania and team two is also obviously we have the same team probably don't need that but you can change that right so it's much easier that way obviously you wouldn't have albania twice but you get the idea here so they're different tournaments here so we can create that in this case australia and andorra australia won here and then we don't have team so it keeps advancing so we need to understand the winning match id so we know where to put it and the row that's associated i also want to know team one and team two who is those teams so when i select on it i want to know who team one is and who team two is team one is albanian team two why do i want them here because what i'm gonna do is i'm gonna create a data validation and notice that data validation is located in the winner here so that data validation here needs to be based on this and this so as soon as i select a different team or here the data validation here is dynamic is based on the two teams here so when i click on data validation here and click here we see that that's based on two cells b13 and b14 now those are right here so that's how we create it so as we whatever team is selected this is n6 and this is p6 so team one and team two this is n6 and this is p6 so we've linked them up and this creates our data validation drop down list and lastly we have a boolean all tournament option and this is gonna be based on that leaderboard here so we want to know this particular all tournaments are we showing all tournaments or we're showing a single selected tournament so that's going to be true or false based on this option we take a look at this here we see when we format the control this particular is linked to b15 and so that's it that's all we have inside there so there's a lot to cover but we're going to go step by step so it's a great little application and i want to show you so much with it so what we're going to do is we're going to get into vba and we're going to show you the basics the first thing what i want to show you is how do we create this let's go with a blank one here i think this one's almost blank so how do we create this basically blank map the first there's two macros one that's going to be created a blank one and the second one that's going to fill in those so let's go with the first one how do we create all these dynamic teams in a dynamic it's based on the number of teams we're starting out with 16 in this training we'll always have 16 but in future once on patreon i'm going to try to edit this and make that update so we can put in different team quantities okay so for a new tournament all we need to do is just create a brand new tournament so let's just call this let's call it global finals and then all we need to do we don't need to end it just save that tournament there's a macro that gets created that macro is going to create all of these shapes it is that macro that i'm going to go over with you right now so we're going to do that inside vba and developers and click on visual basic alt f11 will get you there and what we're going to do is we're going to look in the tournament macros and turn to macros is something called load empty it is this macro that gets created automatically when we save or add a new tournament right so when this automatically it's also going to be the first one in which we load it so this is the macro that i want to go with you over with you now when we save a tournament it is this one that gets created so tournament first thing we have tournament row last row last results row and result row and match row these are all long variables that we'll be using in multiple macros okay so for load empty basically what i want to do is i want to load it and it's empty meaning there's no teams no no pictures associated with it all we're loading is the empty shapes as a single macro and so to do that what we needed to do is determine different variables and different information so i will go over these with you as we reach them so first thing what we want to do is we have brk shape so brk shape is a shape here and what i want to do in this or otherwise known as bracket shape so basically i want to remove all the brackets other than this specific picture here i want to remove everything else so how do i do that how do i remove all of these yes every single one is removed so basically i want to take all these and simply remove them so if i delete them they're gone other than this picture here right this picture simply gets hidden and so how do we do that now when we decide to load it all i need to do is load it and it's going to load right back up so the first thing what we do is we need to have specify remove shapes based on a name there's a name inside each one of these remember it's called bracket one bracket so they're given each individual names so even the connectors have something called bracket connector so all the shapes that i've created on this other than this one this one's called not called actually this one's called a trophy picture trophy because we don't want to delete this one but what i want to do is i want to delete any shape that contains the text word bracket any shape that contains word bracket so here i'm going to use this bracket so that means every single one of the shapes and connectors here contain the word bracket so i want to remove every single one so to do that we're going to loop through the shapes for each bracket shape in shapes because we're focused on the tournament sheet this is the tournament sheet if the in string meaning we're looking for a specific string the name of the shape if it contains not equal to contains the word bracket it's greater than zero that means at least it's found once inside that text then delete the shape and we're gonna do that for every shape we're gonna loop through all the shapes on that sheet any shape containing the word bracket it will be removed deleted okay so that's gonna remove all the shapes so if i run this macro right here and i run it up to that point you see everything's gone except the trophy everything's gone right there so that's all we have to do and the trophy i want to make that i want to hide that temporarily just hide that trophy icon so we're going to continue with the macro and everything gets recreated and then that's going to be so how do we do that okay so the first thing what i want to do is i want to know the team quantity that's a variable that team quantity is located right here inside let's show it right when we edit a tournament it is going to display row 4 and that team quantity is going to be based on q q4 which in this case is 16 and in every case for up until this point it's always going to be 16 but the it's going to be dynamic eventually so team quantity in this case 16 quantity of the teams or players how many people i also want to know the shape quantity i want to create a particular variable called shape quantity right i want to know how many shapes it's going to be the team quantity times 2 which is going to be 32 minus 0. 30 in this case we've got 30. i'm not sure if we're going to be using that we will be using that a little bit later on but in this particular training i'm going to keep it because this will this is something that we may not be using i may remove it it's an original idea okay keep that in mind that this may be removed the shape but however i may use it as i grow i want to keep track of the number of shapes that were used the shape quantity is the number of total shapes okay continuing on the bracket column we need to know set the number of bracket columns i need to know this now remember keep that in mind right now it's always going to be 7 if it's 16 how many columns 1 2 3 4 5 6. there's seven columns here seven columns of brackets but i need to determine that because if i put in 30 if i put in quantity of 32 remember don't try this it's not on this download it's not going to work very well it's going to be pretty ugly however i will make that update on patreon so i want to know how many columns associated if we're going to be looping through the columns i need to know how many rows we're going to go down and how many columns we're going to be over seven so to do that we can just use the formula what we're going to use we're going to use the log of the team quantity in this case 16 divided by the log of that a right so in team quantity here the log of that divided by the log of 2 times 2 and this is going to get us the number of brackets this column going to give us the number of bracket columns in this case it's going to be 7 okay so if it's whether it's five or seven or nine it's going to be all based on basically on that team quantity with the division so this will do it okay i also want to know the original left position i want to know how far left over right if we're going to put it here there where is that left position going to go right if i decide i want to put it a little bit farther to the right and then move it over i would change this to 30. that starting position is going to be all the way to the left so if i you know double click there it's notice that that's moved over to the left so that's the starting position we'll keep that at 10 right now so that's the initial left position i also need to know i need to know the top position of the bracket one of these there's two top positions they're exactly the same one of them top position will change right as i go down here each one the top position is going to be changing then another one i want to stay always the same i want to know the initial starting position so what is that so the top position is going to change as we move down the the bracket top that's the top of the bracket this will never change i always want to know what this is so this is our bracket top position right every top position is the top if i decide to make it a little bit smaller let's say i want to only add one onto that and one onto that you're going to see as i load it it's going to be a little bit higher right so it moves a little bit higher so in fact i kind of like that okay so we want to keep track of the top position so both of these are going to be the same they're starting out the same however this one will increment as we build down those shapes i also want to know the column limit notice the call limit what is the link column limit is the limit of the number of shapes in a column this is eight notice we have eight shapes eight different shapes or eight brackets inside the column one two three four five six seven eight this one has four this one has two this one has two two 4 8. so i want to know the limit that limit's going to change the first column's going to have a limit of 8 the second column's going to have a limit of 4 and 2 and so on so as we keep track of that one so our first column limit what is our first column limit we know we have the quantity a team quantity of 16. so our first column limit is going to be 16 divided by by 2 which is going to give us 8. then we're going to take that same column and we're going to divide it by 2 once more and we're going to divide it by 2 once more again so that's how we're going to do that so the column limit is going to be the team quantity 16 divided by 2. that's going to be the initial column limit okay i also want to set aside i want to know i want a string variable called the bracket side i want to differentiate if we're on the left side which is going to be let's select these here the left side which is these or the right side i want to differentiate because if it's going to be the right side i need to move these things up and if it's the left side and i need to increment from two to four to eight if it's the left side i need to go from eight to four to two so i need to know the difference between what side as we're looping through these i need to know the difference between side left here or side right here so we need to put that into a variable we're starting it off on set set the initial bracket side and i also want to know the height what is the height of the bracket right that's the initial height so basically i'm going to take this sample shape here and i'm going to determine the height of it and i'm going to put that into a variable that's going to be called the bracket height i want to know the bracket height and i also want to know the width of the bracket same thing i'm going to take this sample team group this shape here sample team group that's the same as this shape right here and i want to know the width of it right the width of it's going to be because i need to base space these out accordingly basically it's going to move over to the right but i need to know how much to move it over i need to know the spacing that's going to be in the middle here and i also need to know how far we knew so i'm going to put that into a variable the height and the width of it and i also want to know the top space what is that top space going to be that there's going to be two top spaces notice there's a space between teams here two individual teams and there's a space between different brackets so in other words the spacing between these two is different there's and there's a spacing between the individual when they play each other there's a smaller space so we keep track of those spaces let's reset it so keep in mind there's different spacing and i want to differentiate that different spacing into variables so we do have here we have the the top space the bracket top space is the initial bracket spacer so it's the bracket height plus 15. and i also want to know the right spacer the right how much are we going to move it over the width of that what is that that's the space between the different brackets so notice there's a space in between each of the brackets right so i want to add that space so the initial one is going to be 20. if i reduce that to 10 and we reset it we're going to see that they're going to be a lot closer right a lot closer together right so we don't want it that close together but you can see they're a lot closer together so that's how you can see the differentiator this is the space between the brackets the width spread this is the height of the space between those brackets okay so we have that i also want to know the existing match space now the existing match space remember we've got space between existing teams inside a single match they have two teams now there's a space between them if i want to make this smaller right and i want to old at only five then they're going to be a lot closer together if i run this macro here you're going to see that they're now a lot closer together see that they're a lot closer together you might like it so it gives you an idea that initial space between the two teams that are playing together players that are playing together so we'll return this back to 15. okay so that's the space let's let's put a little notation here a little comment here space between let's call it teams or players teams or players uh inside match okay so we want to know that spacing and also what if it's a new match up spacing what do i mean by new matchup right so notice that there's a space between this match up here and this match up i want to set the space this space here how much is that if i reduce that to 15 you're going to see that the space between is a lot closer so you can see as soon as i make these in but you see now there are now it doesn't really make sense you see they're not spaced properly but now the team's almost the same so we really want to differentiate that and that's why i've done it here so that's why we have a space of 15 pixels between existing and 25 between new matches so when we rerun this macro they get spaced out accordingly okay so we want to know the space between that and then we need to know the new match okay and i want to set the initial column remember we're going to be looping through the columns this case we're going to have seven columns so i want the initial column to be one two three four five six seven so we're gonna move it on so that initial column number is one okay so now that we've got all of our variables defined we can start our loop the first thing is i want to know all from column numbers to the bracket clumps how many brackets seven in this case but from one to seven so we're going to loop first thing what we're going to do is we're going to loop through the columns and then inside each one i want to loop through all of the teams inside each individual column so first thing is that i want to set up the top position if the column number equals 5 we're looping through them then we're going to change that bracket to right remember we're going to go one two three four then i'm going to change i want to differentiate i want to change it to the right once we're once we get to 5 6 and seven these are going to be on the right side so i'm changing that string variable from left we're setting it up left right here i'm going to change it to right but that's only when we get to column number five so we're going to do some things if it's on the left side here we're going to do some things if it's on the right side here so individually we're going to do on the left side so if we are on two column we're already set up for column number one we're setting up the height for column number one everything's set up for we don't need to make any adjustments for column number one however we need to need to make some adjustments for column number two here right the spacing's different on the teams so how do we do that so the column number if it's if it's column number one we're not doing anything nothing right however we're going to use select case and if the case in this case is column number two we're going to set the top position remember this top position is variable this is the top position that's going to move down down down down down down or up up up right depending upon the side so if it's we're moving down right i need to set the top position just be based on this this top position never changes right but however the top position does change so we're going to add 15 right we're adding 15 what that's going to do is going to bring it down a little lower if i were to change this you know to 25 it would be the on column number two we run this macro it would drop it even lower which we wouldn't want but you see now they start lower so it is that a column so that's how we change it and i also want to know what is the existing match space the existing match space is the space between the matches notice here we have a certain amount of space between matches but here we have a different number of spaces between matches right we have a larger space between existing matches so we need to set that up we need to change that and here we have even a larger space between the two matches so what is the space between the matches it's going to be the existing space which is here and here plus something because it's going to grow so we need to update that the existing match space is going to be whatever it currently is plus 42 that's going to increase it okay then all we're going to do is for column number three we're going to increase that top position notice column number three is even lower and the existing match space here between them is even larger so we're going to change that what if it's four in column number four we're starting it out even lower right and the match space is even a little bit less so for column number four a little bit different here column number four the top position is going to be even more right the starting the top position the original top position plus 120 because we wanted to be here loader the existing match base is 40 right and the column limit is 3 right i want to set the column limit to 3. i only want three why is that one two and three i only want three in this column i'm specifically setting the column limit because i want three one two and three right previously and i'll show you in the code the column limit goes from eight to four to two so the column limit is based on a very specific formula however this one column number four we have a very specific we want three teams to show up inside this column column number four so that's why we're setting it up right here okay so what if it's the right side bracket here we simply added to the top position but here we start need to start deducting right so we have the top position if the case is 5 right five we want to make sure it's the same as three one two three is the top space and five both have the same top space so we wanna make sure that three break 52 is exactly the same here now the existing match base needs to update because we've changed it here so we also need to change it here so the existing match basically be whatever it is plus 108 okay so we're just simply adding we're reducing it right the existing match space minus 74 here and plus 10 here so we're updating the match spaces here because i want to know the space between the matches here then it gets reduced the space between matches here and then it gets reduced even more and we want it to match whatever it is on the left side so as we look we're just setting the spacing accordingly based on the column column now of course as i add more here we will add more cases here so we can do that so we can have even for column nine or ten so we'll be adding on to that so now once we've set that up on a per column basis we've set up all that spacing now we're ready to run our loop for our brackets inside our column we're running a loop from one to eight from you know one to four or from one to two we're running that loop so we can loop down so inside that we're gonna run it to the column limit this column limit remember it's gonna go from eight to four to two then it's gonna go to three then it's going to go up again 2 4 8 right so it's going to go down first then up so for the column count equals 1 to the column limit this is dynamic first thing what i want to do is i want to duplicate that sample shape that's the sample shape located right here inside our code this sample shape must get duplicated so that's the first thing we're going to do then i want to give it a very specific and very unique name bracket remember it's always going to have bracket the word bracket and that's very important then it's going to be associated with the column number i need to know and then i want the column count so notice this one let's go out of here this one is bracket underscore one one column one row one this one column one eighth position or eighth bracket right same thing here this one would be two four column two and there's the fourth one so we wanna set up it so giving them very unique but making sure that each one contains the word bracket because when we need to remove it all we need to do is check to see if it contains the word bracket and remove it so we're going to give it that very specific name once we've given it a name we can work specifically with that brand new shape we can call it out with the shapes this brand new exactly shape that we created new bracket okay that's the new bracket and here we're duplicating the bracket so we'll put a little note duplicate and name and rename okay we want it we don't want to keep it on that sample now also keep in mind that we have a data existing is already specifically linked to the team name so when we duplicate it it's still going to be linked the only time it's going to change is when we assign a team to it okay so we have it here so now what we're going to do is we're going to assign it the left position that left position is going to be dynamic based on whatever we've initially set it up here and then it's going to grow we also want to know the top position of it and then what i want to do is i want to rename the bracket text right inside this group all we've done is simply rename the group here this is group it's two shapes in it we've already done that but what i want to do is i want to rename the individual shapes there's two shapes as part of this group so when i look on our selection pane and we scroll down to the one i've selected we see that this is a group and there's two shapes that make up this group one's called the bracket pick brk pic underscore one underscore one and this one's called bracket text so inside this group we have two individual shapes one is the brk txt underscore one now keep in mind there's one thing i want to point out notice they both have the same number of characters and notice that they both start out right one starts b r k p i c and b r k t x d they both contain six characters that's going to be a little bit important later on when we select both have six characters okay so we've gone that we understand that we're naming each individual to in order to name that we need to call out the group right call it out the group item sample group take in that name and then rename it updating that the group items currently their name is sample back and sample picture what does that mean that means when i duplicate it notice the individual when i duplicate it it's going to maintain its individual name sample back be it bracket or sample pick right it's got its individual name but i want to rename that individual name to something so we need to call out the group items because we're already inside this new group we're going to call out the name of it and we're going to simply assign it a brand new name b-r-k-t-x-t b-r-k-p-i-c underscore the call number in the column count rename the bracket text rename the picture bracket picture let's call this picture it's going to be picture okay so that's all we do so now all we've done is we've placed it and we put it in so so now what i want to do is i want to add the left side brackets right so now how do we do that all right let's update this to connect bracket connectors okay so i want to edit okay so if the column number is greater than one so basically for the first column i'm not adding any connectors onto it right there's nothing on the left but for the second column for the second i want to add brackets to the original so for column number one we're not doing anything so column number has to be greater than one and less than five this is only for left side we're going to add those connectors this text is going to add those okay so what i want to do now is i want to take this sample connector it's called sample connector i want to duplicate it and i want to connect one end assuming that we've assuming that we're already on column two i want to connect one end to the original and one end to the original team so how do we do that okay so if the column number equals four and the column numbers then go to skip right and column number four this column is something very different so we're going to skip that okay so first of all i want to know what the previous column was is if we're on column number two i want to know the previous column i don't want to put that into a variable the previous column is column number one why is that important because i'm connecting shapes from column number two to column number one so i need to know both the current column and the previous column because i'm going to make that connection so we put that into a variable called previous column column number set the previous column remember we're only doing this starting at column two and on so unless this is only for left side because it's something different so i also want to know how many items are in the previous column right if i know that our column count in this case is four what is our previous column it's times two right our previous column is 8 right so i want to know the difference i want to know this is 4 and this is it i want to put those into a variable so our previous column count in this case for column 2 will be 8 so it's basically the column count how many of you on up to that so column count times 2 that's going to set the previous column count just putting that into a variable now we're ready to take that bracket that sample white bracket that i showed you we're going to duplicate that and we're going to give it a specific name this is going to be called bracket connection one for each shape right so for example on this team too i've got to create two brackets i've got to create this one and i got to create this one and then i got to connect it one to this and one to the top so if i'm on this one here let's select this if i'm on bracket two one i need to connect this one of them to here bracket one one and the other one i gotta connect to bracket one two so i've gotta make that connection one here and i gotta make the connection in here okay but how do i know where to connect it how do i know to connect it here here here or here right well basically each each of those points is assigned a number so if i'm going to connect this let's start out it goes in clockwise motion so here this is point one so if we look at this we have point one point two point three and point four so keep that in mind i'll show that to you 0.1 0.2 point three and point four so this one is going to connect from point two to what's on this one again same thing one two three four so i wanna connect point two this is point one on the top point two to point four point two to point four right on these so this one also point two two point four of the previous one so that's the connection we want to make so the best way to do that is simply to recognize which shape we're connecting and which point we're doing that so we can do that here so once i duplicate it i give that bracket a specific name based on the column number and based on the column count once i have that i can make that so we have two different brackets one here and one here so our first bracket with the first bracket we've connected here and then all we're going to be doing is we're going to set it focus on this we're going to focus just on the connector format i want to make that connection now what's the two points i want to connect well if begin connect begin connect where is it going to begin it's going to begin here and it's going to end right here so if it's going to begin here where is it going to be getting it's going to be it's going to be beginning at point 2 connector 2 and it's going to be ending at 0.4 so point so the 2 is going to be connected to this one right here bracket connector 1 2 1. i need to connect that to this original shape here bracket text 2 1. so to do that we can do this so the beginning connector is going to be able to bracket connect the column number and the column count and what point are we connecting it to remember i said from 2 to 4 from 2 to 4. so here is the starting point at point 2 0.2 is this left side we're connecting it to the right side which is 0.4 so now our and connect our nconnect is that previous column remember this is where that previous column is and i want to know the previous count mine the previous column count minus 1. the previous column count here is 8 right so minus 1. so we want to know that subtracting to 1 that's going to get us our previous right so how do we know that right how do we know that well this column count what are we on we're on column count 1 here so we need to subtract it right so what is that going to be 1 here so we know that continue 1 to 1 in this case 1 to 2. so our first one is going to be just that 1 previous column count column count times 2. so if we're 1 right we're column count we're on the first shape one times two is two so our previous column count is two so but i need to connect it to shape number one so shape number one is two minus one so this is going to get us our one i need to know i want to connect to the first shape this one is going to be simply our previous column count right to shape 2. now the second one so that's how we do from from point 2 of our first shape to the previous column so that's our first shape from point two to previous now this that we know this is shape one this is shape two two right so i wanna connect it to the two i'm going from our column here here to two so next one we're gonna do the exactly the same thing we're gonna duplicate that connector name we're gonna give it a unique name then what we're gonna do is we're gonna connect it to the previous ship we're going to make that original connection notice the beginning connect is exactly the same as this our beginning connect doesn't change because it's connecting from the same point right we have two we have two brackets here both of those brackets have the same exact beginning connect they just have different end connects right the only difference between this team bracket one one and this one is bracket one two is the last number it goes from one to two so again we're calling out in their second one previous column this one's previous column count our previous column count is two right previous column count equals column count we're on number one column the first one we're on number one this is two this is three this is four so it's just math so 1 times 2 is our second one this one if i want to connect this one right this one is this select this this is 2. so if i multiply 2 times 2 i'm going to get 4. if i want to connect it to 4 this is 4 and this is 3 that's why we subtract 1 on the first we subtracting 1 our initial one to get 3 and then we're using this one previous column count 4. and we're connecting it to 0.4 which is that right side connection i know it gets a little bit confusing but basically we just have simply two brackets they're starting out in the same shape they're connecting to just two different shapes that's all we have to do for our bracket that is for our left side add the left side bracket okay also i need to call out specifically for our winning our winning connector and trophy icon those are very unique right our brackets here are very unique are when we're in column four here we have very unique column i want to it's very neat because we're in this case we're doing the same thing from one the previous column but i also want to connect to that winning one so we need to call out that very specifically if the column number is four and the column count equals 3. why would the column count equal 3 3 is our winning this is 1 this is 2 and this is 3. right so if it's 3 i need to connect it very very specifically right because these are the same before here's the same four one is connect going to connect two one and two so there's no difference between this one and this or this one and this one however our last one has something very unique right because it is our winning team so we have a single bracket so i want to do that i want to have a create a single bracket duplicate our existing bracket give it a very specific name we're going to call it bracket connector 2 on this one and oh sorry these ones have different names back connector 1 and black connector 2 sorry so this one we're going to call bracket connector 2 and what i want to do is i want to then call it out put a connection format and i want to make it very specifically notice this one has a different connection right our beginning is based on our bracket text column number column count minus one what do i mean by that if this is three we know this is three four three i want to connect it to where i want to connect it to two so it's our column count 3 minus 2 minus 1 is 2. so i want to connect it to the one above this is shape 2 this is shape 1 this is shape 2 and this is shape 3. so if i want to connect from 3 to 2 all i need to do now notice the different positions now our starting position is three what do you remember this is one this is two and this is three so our starting position is three our ending position is one so a little bit different here on the bracket not this one here ending position is one let's move that here oh not there so here so our ending position is one which is that first one so we're doing something a little bit different our starting position is three here our ending position is one we're connecting it to column count minus one to column count remember so we're only on three column count only four specifically when our column numbers were having specific code for our last one right here so that's all we're doing it's just connecting in column four only okay great so we've done that for our winning connector and then also what i want to do is i want to place that trophy icon and then basically going to take that trophy icon shapes that one doesn't need to be okay we're just going to make it visible or invisible so we're going to give it a specific left position we're going to give it a specific top position we want to make sure it is on top right i don't want it under the line or i don't want this i want it over the line so when autumn let's reset that so we want it on top of the line so we're going to do the z order the z order will ensure that this trophy is above the line so we can do that here z order bring it to front this ensures let's put that ensure trophy is over connector line okay good and then also we want to make sure it's visible because remember up here we've hidden it up here just to make sure we've hidden it here but here it's going to be visible again so i want to make sure that we make it visible again okay so once it's visible now we've done it so we've done two things we've added the left side brackets we've added the winning bracket and the trophy icon now we're ready to add the right side brackets so the right side brackets is a little bit different if the column number is greater than four greater than four that would mean column number let's close this out column number five six or seven anything greater than 4 we're going to do a little bit different slightly different right so to do that first of all i want to know the previous column equals the column number plus column we want to set the previous column just as we did before the previous column count is going to be equal to the application roundup so this time i'm determining the previous column count and i want to do that we're going to use the round up i want to know how many were in the previous column so we're going to use column count divided by two this time we're dividing it by two and it's going to set we're gonna make sure that we're rounding up that's gonna also right so i want to know if the previous column four on column two it's gonna be four and i wanna know that previous column very very important because i need to connect if i'm on here column four i need to make sure that the previous columns column two so we're dividing and making sure that it's rounding up making sure that we have a full number on that okay so that's why we use roundup so we can set if the column number is 5 then the previous column number equals 2. right just to make sure here we're calling on 5 call number 5 i want to make sure we're setting the previous column because i want to set these connectors to this one these two get set to this one right here these two get set to this one so we're just making sure that we're gonna do that okay so with the shapes we're gonna duplicate that connector here and the bracket connector one the first one we're giving it a specific name because it's our first bracket and i want to connect it to the column number and the column count so this is the same here again also starting at position two and going to four again so when we get to here i want to connect this again from 4 all the way to 2 but we need to call out that shape if i know that this is column number 5 bracket number 1 i want to connect it to 2 2 in other words connected to number 2 here in this case is 2 so we're going to do that again just like here so we're going to do the column count here that's the original we're connecting it to our original shape here our original connection right here and we're connecting it to this shape here so our second connection here is based on the previous column count our previous column this is where it comes in handy right how do i know that because i need to get from 1 i need to get to 2 2 in this case 2 or from 2 to 2. so this is going to help us out same thing here from 1 going to 1 or from 1 to 2. right so it's a little bit different right from two so we're getting that whole number we're creating this from column to the second one here just creating this also from four position four to two great so now that's all that we do that for every single one on the right side so these are all for right side brackets now what about the top position now what i want to do is remember as we loop through these we need to adjust the top position right the top position is going to move down or the top position is going to move up subtraction so we either get adding to the top position as we move down or as we get to the right side we're going to be moving up decreasing that top position so we can do if the column number equals 1 and the column count equals 2 or mod so i want to know is it different so i want to add that top position what is the top position as we move down here i need to know what is the top position if it's a new how do i know if it's going to be an existing match up right notice that it's existing matchup or a brand new matchup the top position is going to have to be ch as i add this i need to know the difference how do i know if the top position notice this is 1. if this is an odd number 1 i notice that we're going to get a smaller top position however if the if the column number is three if it's an odd one then i know it's a lower top position so it's an even number let's say bracket number two every even number is going to have a smaller top position because the evens here are the existing matches the odds three is going to start a brand new match so we need to determine whether it is odd or even so if the column number is one making sure that it is one right and the column count mod 2 equals zero meaning it's an even number if it's an even number or right the column number seven right i'm really focused on these two right here i'm really focused on the first column and the last column because both of those these first columns column 1 and column 7 have very distinct spacing right very specific spacing so we want to ensure that the spacing is always equal between those two columns okay so moving on now what we want to do is we want to continue on so adding that top position so if call number one if it's an even right mod 2 then i want to make sure it's we have a top position even it's a new match up brand new match up between those two the top position is going to be equal to the top position plus the new match space remember we've got a variable that's distinct for new matches and we have one that's existing so this is for the new match spacing it's a larger space between the two teams because it's a brand new match what if it's a current matchup well if it's a current matchup and it's in column number seven and we're on we're going to reset the top meaning what we want to do is when i get to the all the way down first thing if i get back up to all the way to the top one and number seven i want to make sure that we reset it with the exact same top position that we had with the original one so to do that we just reset it back so the column number seven and the column count meaning it's the first shape on the first column reset the match space so we need to reset it to the original spacing so the top position is going to be able to the top position i8 plus one that is the top position that we reset i just updated here that's the original top position that we had the original that initial so we're just resetting it but only if it's on the last column column number seven right and i also want to set the existing match space to also want to reset it to brk 15 so the high plus 15 we're resetting that existing match space back to the original spacing that it was however remember that original spacing here existing match space here is brk height plus 15. so all we're doing is we're resetting it back to the original one because we want both column one and seven to be exactly a match all right so that we're just resetting this otherwise we're going to set the top position equal to the top position plus the existing match space for the current match so this is for the current match up space here the top position right this is for the new one so new match ups and for current matchups right smaller spacing for the current match up okay but what about for the winning speeds i want to do something very very specific i want to set the if the column number is four and the column count equals two what does that happen column count is four and two this for this specific shape right here column count before space shape number 2 in there i want to make sure that we're setting the top position very very unique very top position based on a very specific location okay so that's it so we're just setting it up that's all we're doing then we're just looping through every single row so this way this next column count remember we're looping from one to eight from one to four from one to two and so on and so forth then back up so we're looping through that so now we're outside of it now we're into a new column when we change the columns right this is the this is the specific items inside a column this is for the new columns when we change the column when you do something very distinct i want if i want to do something if we're on the left side then i want to set the column limits remember we need to change the number of columns columns are going to go from 8 to 4 to 2 or it's going to go from 2 to 4 to 8. based on whether we're on the left side or we're on the right side so that's what we need to make that distinction i want to reset that column limit if we're on the left side the column limit is going to be reduced so it's going to be divided by 2 from 8 to 4 to 2. we're dividing by 2. if we're on the right side we're increasing so with the column it's going to double it's going to go from 2 to 4 to 8. so here we're resetting the column limit and then we're also resetting we need to update the left position remember we're moving from left to right so all we need to do is reset that left position based on the current left position based on the spacing of that new remember that spacing is set all the way up here we're setting that spacing right here right here that right spacing here based on the width of the shape plus 20 so then we're simply adding to that so we can move it to the left here right up here right here we're adding to that left spacing that's it that's all we have to do and then we loop through the columns then all we need to do is i want to make one sure then i want to load the next macro the next macro load existing is going to be loading all those shapes but i only want to load those shapes if we have an associated to a row if it's a new to a ride i want to do anything i want to make sure that we have specific data if we do have a 2 if b4 doesn't equal empty then i want to load any data that's associated with it which is the second macro notice there's no data associated with this but if we do have data i want to load all of that team all the winning information the teams the flags and everything i want to load that in the existing shape it is that that we're going macro that we're going to cover right now and that's called load existing so it's that macro that we're going to run assuming that we have an actual saved tournament okay great so now we're going to do that so now again i've got to load some information i want to this time what we're going to be doing is we're going to be basically converting the data inside this all this data into actual information so all this data is going to come in and we know what what is the winning team and we notice that so i'm going to bring it up but it's going to be based on a specific id right i want to only load associated items or associated data from a specific tournament tournament id1 so to do that we're going to run an advanced filter and i'm going to have those results appear so i want that criteria of our advanced filter based on that specific tournament id located in b2 and i want those results to come here so i want to know all of that information based on that it's going to come down here all that information is going to be only for that term and id 1. then what i want to do is i want to get that picture i want to know the picture that's associated with a specific team i want to know the picture that's associated with team one and i want to know the picture associated with team one that information is here it's located in this database and the teams and players so i need to extract that i haven't named ranges here so if we go into team here we have name range one is called team name and we have another one called team picture so it's based on here this dynamic named range so if i know the team name if i know what team name is coming in the results i can then extract the picture and then i can bring down those formulas so i can extract those pictures i need those pictures to be displayed and i can do that with formulas so if i create a formula here indexing that team picture based on a match whatever's in u3 and based on that team name it's going to extract that picture and we can do that for both team one and team two if i then take this formula and i bring it down on all the results we can then have it it's much better to do that during the macro as opposed to having a lot of formulas that you're not using so what we can do is we can simply delete all these formulas and recreate them as we need them if we keep them up here a single formula and then just copy down the formulas so that's what we're going to do once we get all the information i'm going to then loop through those all the way from 3 all the way to the last row i'm going to determine the match id if i know the match id then i know the shape that's associated with them i can then put all that information inside the shape so that's just what we're going to do inside the macro so we can do that we need to determine the picture folder as a string i need to know differentiate between two pictures picture one name and picture one path because we have two teams and picture two name and picture two path right because i've got two different pictures associated with that i also wanna know the match id remember i wanna put that in a string this is our match id i wanna put that into a string and i also wanna know who the winner is what team is the winner some variables i want to know what column number we need to loop again loop through those columns and determine loop through and i also want to know the team one and team two and i also want to know the team one id and the team 2 id those are going to be string variables since we're running an advanced filter we're going to determine the last row of the data and the last results row we're going to loop through all those results so we're going to need to keep track of as we go through those results right as we go through from 3 all the way to 17 i need to keep track of which row we're on that's going to be considered the result row and i also want to know what row the tournament rows on okay and we're going to focus pretty primarily with our tournament sheet here this is our tournament sheep here we're going to start out with that then we're going to move to our database and run our advanced filter but the first thing what we want to do is we need to make sure that b4 is not empty right if we don't have a tournament row that's associated there's nothing we can do so we need to make sure that we're loading that information from the tournament so to do that we just need to make sure that we have it so we've done that if b4 is empty please select tournament to load okay i want to set the picture folder based on a variable and that's based on the named range that we created make sure that these are not the same picture folder here and pick folder to make sure these are different i run into problems if i create a named range that's exactly the same as the name a variable excuse me if i create a variable that's exactly the same as the name range i run into problems so i want to take that this is that named range that is in our setup screen here i want to take that picture folder here and i want to add a backslash onto it notice there's no backslash onto that so we need to do that within the variable so we're doing it right here adding that backsplash then also i want another tournament row that's located in b4 i want to load that information i want to bring all that tournament information inside that i want to bring in the inside k4 the tournament name i want to know the start date i want to know the end date and i want to know the quantity and q4 so i want to bring all that in that name that team quantity the start date and bring it all in from there's no data mapping because there's only four different fields here so we're going to bring all that tournament information into our tournament that's pretty much it for the tournament sheet for now we're going to focus on the database that's where all of our match data is located so we're going to focus on this and we're going to run an advanced filter determining the last row of this one in this case the last row here is going to be 31 30. so we're going to run that advanced filter and have our criteria from p2 to p3 and then getting those results in all the way the results we want the results to come in from q all the way to aa so we're going to do that now the last row is going to be based on the last row we want to clear any prior results before we do that so what i'm going to do is i'm going to clear this all the way from q3 including the formulas all the way to ac and down i just want to clear all that data out because we're going to bring it in i don't want any whole data from q3 all the way ac down clearing any prior results the last row if the last row is less than three we're going to exit the sub we're going to run our advanced filter from a3 all the way through almost row that criteria is going to be p2 through p3 just as i mentioned before that criteria is right here it's based on a link here inside that tour id right here p2 to p3 i want to bring all that information from q all the way over to aa so we can do that with that advanced filter those results q2 through a we want unique results to be true then we want to determine the last results row that's going to be based on column r which is our match id if it's less than 3 then we know we have no data if it's less than 3 we're going to exit the sub nothing to do all right now that we have that what i want to do is i want to bring down our formulas right i want to take the formulas located in ab1 through ac1 and bring them all the way down so we can do that with a single line of code here ab3 through ac formula the last results row equals ab1 through ac formula so bring down call this bring let's go bring down team picture formulas so all right once we are that's going to bring it down now we're ready to run it now we're ready to get in our data so we're going to run that loop the result row is going to be from 3 to the last results row i want to put all this into variables right let's bring this down here so we can see both the same time and take a look at our results here okay so what i want to do is i want to bring in our match id is going to be located in column r we want to bring in our column number i want to know what the column number is how do we know in other words i want to take this this is column number one these are column number two three so i want to extract that column number from the match id so we can use the left to do that so we're going to use the left of the match id what i want to do is i want to find the first instance of that underscore if i want to find the last i'm going to use in string reverse but to find the first instance we're going to use in string of that match id and then i'm going to subtract 1. i want to subtract 1 because i don't want to include this underscore so if i do that that's going to extract that column number from that string very important if i need to know i need to know what that call number i need to know what remember these are two teams if i take a look at this variable this is team one this is team two right this is team three and this is team four on column number one if we look back on our tournament here and we take a look at this shape here we see that this is column one and this is team three this is column one and this is team two so we have to separate all that information to know that so we know exactly where to place it so we want column one team three and team four so basically this is team three this is team four this is team one this is team two based on column number one so we need to extract all those details so to get team id one team id one that's in the middle right that's in the right in the middle so to get that we're going to use the mid and we're going to use the mid function and the math we're going to use the match id first place i want to know the first position is going to be based on that original that original underscore plus 1 right plus 1 meaning i don't want this first one plus 1 i want to start it out right here our starting point is right after that underscore so our ending point is right before the next underscore so how are we going to find that well we're going to find that using the in string reverse in string reverse that's going to give us our second underscore in string reverse or in this case our last underscore which is our second our last i want to find that position so how do we do that we can use in string reverse to find that so we're going to use in string first we're going to run that's going to get us our last region again i don't want to include that underscore in the results so we're going to subtract 1. so if we take this point this midpoint here and then we subtract out all of the in string that last one it's going to extract out our id so we're going to use this formula and basically this is going to simply extract in this case it's going to extract the 3. that's what i want to extract so that what about our team two that's a little bit easier all i need to use is the right function of that we don't know if it's going to be it could be two integers or it could be one so we need to use the right function in this case team two id we're going to use the write function based on the match we're going to use the length of the entire and we're going to subtract out the latter position of the last in string reverse underscore match id okay we're going to extract that second id so now we've got all three components of it we've got the column we've got team id 1 and team id2 so we know that now i also want to put team 1 and team 2 into variables team 1 of course is going to be located in column u team 2 is going to be located in column x so i want to extract those in two variables i also want to know the winner that's coming from column a a picture name that's going to be an a b for for team number one and a c for team number two and i want the picture i want to create a two picture pass one for the first picture and one for the second picture so picture one path is based on the picture folder and picture one name picture two path is based on picture folder and picture so this is gonna get us the two palettes for both pictures so now we can focus on the tournament a little bit so the tournament now what i want to do is i want to i've got all of our variables i've got all the information that i need in order to place this team name here and place the picture where i want it okay so how are we going to do that well we're going to focus on the tournament with the shapes brk text the bracket text remember i'm focused on this individual shape here brk text underscore i need to know the column number and i need to know the team team one column number and team one or actually call number and i know to know that individual team right so we have it team two team three team four and so on and down so now that we have that we can call it out so bracket the column number and team one id team one id is that one two or three right team one so we're going to give it a specific tax i'm gonna give it that team one text so what that's going to do is going to take this particular shape here bracket column number one team one and it's going to give it that specific text this formula won't matter anymore because we've given it this text right so it won't matter anymore so once we do that we can give it that text so all i do is load that it's going to load it up here so that formula is only used temporarily if there's nothing there if we've assigned it a specific text that formula won't matter anymore because we've given it a very specific text that text is going to be the name of that team team one we're going to do the same thing for team two except this one's going to take on team two id for the first one team two idea this particular instance team two is gonna be team two that shape that brk one 2 is going to get team 2's name okay so now we've given it the name but we haven't given it the picture yet the picture is going to be placed directly in that shape now look take a look at this shape here brk pick it's got just the different text but it's got the same column and it's got the same team id number so we can do that picture name one assuming it doesn't equal empty and we want to make sure that the directory path is correct we want to make sure it has a correct path then what we want to do is then shapes brk picture column number team id i want to give it a fill i want to fill it with a user picture fill that circle with a user picture what picture is going to be based on this path you can do the same thing for both picture number one and picture number two that fills those in with the picture okay great so now i've got that but i haven't decided what i want to put this colored border green or red based on the winning team so now what we have to do is do just that so now if the winner equals empty then go to no winner right if there's no winner we don't need to color the borders i don't need to call these borders if there's no winners we need to make sure so if the winner goes blank then go to no winner it's going to skip all of this and go directly down here if we do have a winner then we can then color associated so team one if team one is the winner if team one equals the winner then what i want to do is i want to color this green and this red team two is the winner like in this case i want to call the first one red and the second one green based on the winner so we can do that if team one is the winner i'm going to take that shape that text shape team one i'm going to give it a little specific color that color is going to be this green remember this is an rgb red green blue so that's going to take all the green it's going to give it that full color green that full color green and zero red and zero blue that is going to give it that line color going to give it that line for team number one because the number one is user what about team id 2 we're going to give it red border it's going to be all red 0 green and 0 blue rgb red green blue so we're going to give it that all right that's if team one is the winner else if team two what if the team two is a winner and now we're just going to do the opposite team one is going to take on that red border and team two is going to take on that green border now we've colored it there so now what about the winner now very very specifically if the column number equals four and the winner is not empty this is very specific only for this i want to make sure that we've colored the final winner final winner here i want to make sure that we're good once we get to that column we're going to do something a little bit different if the column requires four and the winner does not equal empty then what i want to do is i want to call her shape number three this is shape number three this is one this is two and this is three so three is the one we're going to be calling out four right column four and three that way i wanna do something very specific that one we need to do here in this case gonna put the winning name this one's gonna take the winning name right we have two teams that play but this one's gonna take the winning name so this shape is gonna take the winning name and also i wanna call it this great and i also wanna make it bold right this one notice that this one has a bold font the others don't right so the other this one's got a bold font i also want to make the text font making that bold and also giving it that green border here that's only for the winner and i also want to give it the picture i want to know who the picture is and i want to put their flag there as well so how are we going to get that if team 1 is the winner then what we're going to do is we're going to assign give it that team 1 picture if team 2 is the winner we're going to give it that picture path team 2. so this assigns the picture to that winning shape here right down here that's a winning shape great so that's all we have to do that actually that macro assigns all the data and all the pictures to all of the columns relatively easy as long as we understand our shape names and we understand our columns and which shape we're on so now that's the hardest part that's going to get a little easier i know it can be confusing but please download this for free using the links in the description and go over the code all right so now what if i want to save and update the tournament right this is the button that's attached here if i want to save and update tournament i want to save all that tournament information if i want to edit the tournament it's going to open up line 4. so how do we do that well the first thing what i want to do is i want to determine is it a new tournament or is it an existing tournament if it's a new tournament we click this button and we see that b4 is empty there's no row that's associated with the tournament if it's an existing tournament right b4s can pick out a value so we need to determine if b4 is new or not but first of all i want to make sure that we actually have a name right if this gets deleted we cannot save that tournament it's going to let us know please make sure to add a tournament name before saving right so we need to make sure we're going to do that so if k4 is empty let the user know to give it a tournament name before saving here's where we're focused on b4 if b4 is empty then there's a new tournament alison is existing so something we want to do if it's a new tournament i want to sign it that new id that new terminal id is going to come from b6 i'm going to take whatever's in b6 i'm going to place it directly inside b2 and i also want to place it on the first available row inside our tournaments all right so to do that we would just do a few things the tournament row we're going to set it base the first available row here in the tournament database and also what i want to do b2 is going to take on that next tournament id and i also want to place that term and id and column a that's if it's a new tournament so these three things or if it's a new tournament if it is an existing tournament all we need to do is extract the row from whatever's in b4 all the other lines of code relatively simple all we're going to do is simply apply not using data mapping in this case just a few lines of code b column b is going to take on our tournament name column c is going to take on our tournament quantity start date is going to be based on m4 and the end date's going to be based on o4 okay so we're just adding the data just adding these simply adding these fields here name date end date and quantity into our database relatively simple so once we have that that's it then all i want to do is i want to run a macro that hides the term and we'll get into these as we look through but all this is going to simply do is just is going to hide this row here and it's going to hide these shapes right here this this shape here all it's going to do so when i save it it's just going to do just that hide that row because you may not want to see that and same thing here if i save the details it's going to hide it okay so we've got a few other macros to go on this so when i do a new tournament i have some things i want to do if i click new tournament i want to show that row and i also want to show these shapes here so to do that so first i want to clear the contents i want to clear any contents associated including b2 including whatever selected uh tournament id and b2 i want to make sure that's cleared out i also want to clear the name the date and the associated fields i'm going to keep this here because we're only doing 16 teams and this training all right so we're going to clear this and also want to remove the existing tournament shapes right if i've loaded it let's load let's let it turn it if i've got some tournament i'll also want to clear out any shapes that are associated when i click new tournament i want to clear all those shapes out so to do that we can just run a loop just as we did before for each shape and shapes if it contains the name bracket any shape that contains that we're going to delete it and also what i want to do is i want to hide the trophy picture right so we're just going to hide that using this that hiding that trophy icon right we're not deleting it we're just hiding because we only need one we're going to set q4 uh to 16 we're going to default to 16 teams we're going to make sure that that if it's currently hidden only if it's currently hidden row 4 then i want to run a macro that's going to show it and i'll go over that macro in just a bit but i want to take care of all the macros on this module first then i want to select k4 k4 will select that tournament name that's all we have to do for new tournaments just like that so what is next so next what if i want to delete a tournament if i delete a tournament then i want to basically make sure that it's an existing tournament so let's say we decide we've got global finals i want to delete this tournament and i'm going to use this button to do just that it's going to delete that but first i need to do make sure a few things are you sure you want to delete this tournament yes or no give the user an option out so when i click delete the tournament here it's going to give us that option so that's it and if they say no we're going to exit the sub out all right so first thing what i want to do is has this tournament been saved or not before if b4 is empty the term has not been saved there's nothing to do in this database if it has not been saved so if we do want to delete it all we need to do is just clear the cells out much easier so then we're going to basically skip to not save we're going to skip all the way down here and go to not saved but if it has been saved i want to set the turn row in a variable here and i want to set i also want to delete the entire row from the turn of b that's going to take this row here let's say 7 and it's going to delete that entire row but then i also need to make sure that i delete any teams that are associated with this so we don't have any i'm going to save one before i do it we don't have any teams but i want to make sure that we delete those so for example this one if i decide that we're going to add some teams here so let's add this and we'll add this here let's go down here and we're going to add a winner onto this one and we're going to save it okay so what that's going to do is going to add some rows to our database so now we have two here now let's center this so i can see them properly all right so we have now two rows associated with tournament id i need to make sure that these rows are deleted but when i bring them in i also need to know that's why these rows are so important i need to know what row is going to be there let's pin this here for a second and then what i want to do is i want to associate if i'm going to delete all i need to know only those rows associated with this so we're going to use are going to run in criteria and we're going to actually i'm going to do it here i want to know all of those rows associated with this this row bring this row and i want to know which row so i can delete row 31 and delete row 32 but if i delete row 31 first it's going to change this row so the best way to do that is to reverse it i want to go to the highest rows first and low so if i delete row 32 first it won't change row 31 right if i were to delete this row all of a sudden 31 become 32 be very confusing right but if i delete row 32 and 31 if i go in reverse order it'll be much easier so that's what we're going to do first we're going to clear any prior results on our advanced filter so we're going to run an advanced filter because i only want to know the rows associated with this case terminal id so i'm going to clear out any prior results so clearing out all the results here first so we can do that right here clearing out all those results determining the last row then we're going to run an advanced filter getting all that information in just as we did before then i want to do something very very special this is very important for delete i'm going to run a sort and i want to run it based on descending so i want to know my highest rows first based on q3 to my lowest rows so i want to run that so we're going to run it so our highest row 32 and then 31. then what we can do is loop through this so that's what we're going to do we're going to clear with the sort of that sheet we're going to clear any sorts that exist we're going to add a key based on q3 we're going to sort based on descending very important then we're going to use we're going to set we're going to then basically create that range based on the term remember we need to call out that sheet again because we're inside with sort so make sure you call out the sheet when you're sorting again sorting that based on that range q3 all the way to a8 okay then we're ready to loop through then i'm going to loop through from three although last and deleting calling out the row and then deleting the individual row so four results row equals three to the last results row if q equals empty then go to next result right we need to make sure that we're going to have a row in order to now we're going to set the matrix to whatever's in queue that's setting the matro then we're ready to delete it we're going to delete that entire row delete so if i run this macro here and i click click yes but i put a stop in it you see up into this point now row 32 here is first in the row 31 a second so as we loop through those we'll be able to delete result row three the last row and then all we're going to be doing is delete negative loop through then all we're going to be doing is just running a macro to add that new tournament so as we continue on with this macro the reason this by came up is because we're trying to select something but we're not on that sheet but that's okay so notice that k for select we can't select a specific range because we're not on that sheet but if i switched here normally you would not be deleting anything unless you're on this sheet and now we continue on with the macro it's not going to leave it of course once i'm on this sheet i can select k3 so keep that in mind sometimes what i'll do is i'll do if active sheet equals right to make sure that that bug so we're only going to select this if our current active sheet is tournament or whatever that's a way to avoid that bug okay great so we've done that we just deleted it so now we take a look inside our tournament there's no longer tournament we look in our tournament and we see that there is no longer anything associated with five okay so we've deleted that tournament all right great so we've covered that delete tournament that's good we can a little bit that is it for our tournament macros so let's take a look inside the match macros here right there's certain things that we need to do with a match we're going to select the match we've got to do something we've got to save the match right new matches and we've got to forward so now we're going to go through the macros on that okay we're going to turn our attention to our match macros here we've got a few variables the match id as you're familiar with that the column number team id things you've been calling it team one id team two id the column count as we need to know and i also need to know the winning match id right that's important and the winner is a string also the match row the match column the winning call and the winning row winning up okay so the first one i'm going to focus on is a macro that this is the macro that's already been assigned to the sample right there's a macro notice it's going to create a bug that's okay and so there's a macro that's associated with this each individual shape on this group already has a macro assigned to it so if i right click and click assign macro sorry it is that match select so that way when i duplicate this shape automatically there's macros associated with that each shape already has a macro because i've assigned it to this group so when you assign a macro and then you duplicate it that macro gets duplicated along with the shape so they're already assigned macros and it is that macro called select and the reason here i want to know is what if i try to run this automatically it's going to be a debug because application caller we're using this is the name of the shape that called it if you try to run this macro from here it's going to create a bug because it's only it's based on the name of the shape that has selected and what is the name of the shape now keep in mind when you select it a macro does not get assigned to a group so when i see that assign macro notice nothing shows up right it does just click one that's not the macro name the macro name is actually the assigned to the individual shapes of the group so when i click on the individual shape of a group and i right click that and click a slide macro we then see the macro that's associated with that it is the same macro that's on this bracket text and the same macro that's on the picture here so if i unselect the text here and only the picture then right click it you see it as the same macro that's been assigned to both of those when i assign a macro to the group that macro actually gets assigned to the individual shapes of the group and not the group itself so that macros we're going to focus on here so what i want to do is i want to put the text brkpic11 or brk text11 i want to put it into a variable i'm going to call it that match id right so that match id is going to be inside that but of course i want to extract the team id right from that so the best way to do that is to get that information right i want to extract i want to know what team id what the column number and i want to do it so what is the team id the team id is going to be the right i want to know what is that team id can extract that here so what is the team id the team id in this case is going to be let's just go here this text box here is going to be on extracted 1-1 right i want to know that one i also want to know the column number so the team id is going to use the right and basically we're going to extract that here what is that is it that 1 or is that that 2 or what is that team id so we're going to use the right just as we did before and we're going to use the match id which is the application column the text we're going to know i want to know the length the entire length of it minus the in string reverse meaning it is the last underscore that is found i want to know what is to the right of that last underscore in this case it's a one as you can see so it's going to put that one it's going to put it directly into i also want to know the column number the column number how are we going to get the column number well what we can do is we can use the replace so how are we going to do that well i'm going to use the the column number i want to remove all everything associated with that i'm going to do that in two different steps so the first thing what i'm going to do is i'm going to take the match id and i'm going to replace the left of whatever is the match id i'm going to take the first seven characters and i'm going to remove them first seven characters that's the first step what is the first seven characters it is all this it's that brk txt underscore now keep in mind that the picture if they select the picture it is going to be exactly also the first seven characters we're going to be removing brk pick underscore we're going to be removing those so the first thing i want to do what that's going to do is going to leave me with one underscore one so that's the first step we're going to extract that we're going to put that into a variable called column number okay that's a string variable although keep that in mind that's column number is a string variable in this case right because i need to work with it i still want the column number but it's a string variable i need to work with it so i'm going to be taking using the replace i'm going to be taking the first seven characters of that string and i'm going to be replacing with nothing this is going to extract the last three digits or four or whatever then what i want to do is from those three i've done it in two steps here i want to extract the middle point so the left so basically the left one i'm going to look for so basically this is going to give me something like one underscore one right so now that i have this what do i want to do i want to find the underscore and i want to determine whatever's on the left of this i want to extract out of it so to do that we're going to use the left command we're going to look for the first underscore and i want to extract everything to let that i don't want to include this so we're going to subtract 1 and that's going to whenever it's 1 or 10 or 99 or 100 that whatever numbers that first number is going to extract so that's going to leave us with just that number and it's going so that's what i'm going to put that into a variable called column number so we're going to update that so basically we're going to extract column number i need to know what called the column number is also we've already got the team id we know this is the team id that's the last number here as in one if the team i now need to know the difference is it going to be a 1 or is it going to be 2. i don't i need to know if it's the first one or the second one so the first one is going to be odd the second one is going to be even so to do that if the team id here's the team id is it 1 2 3 or 4 if the mod of 2 equals 0 meaning this isn't even let's put this as even number and this is going to be the odd number okay odd number so if it's an even number what i'm going to do is i'm going to assign this the team id right if it's even how do i know this if it's even i if it's even this would be even right let's say this one right here let's pull this one in right here this is even right so i know that this is team two because it's an even number i know that this is team two and i know that team one is this one here right i know it's team one so how do we differentiate between those so team one id is gonna be because it's even right so that means the team id is let's say it's two it's four it's six or it's eight right because we know it's an even number so team one id would be what one less than that our team id is four that means our team one id is three and our team two id is four right so because it's even however what if it's odd if they've selected the odd one right then i know that our team id is for t1 id is three and our team two id would be team id plus one so basically this is how what i wanna do is i wanna associate if they select one i need to know what what's the other team if they select this one i need to know both which is team one team two i need to with that why is that important because i need to create this right i need to know that this is team seven look this is if i select this i need to know one seven and eight i need to know that i need to know if it's seven and eight this is column one this is team id seven and team id8 right i need to have this is it column one five and six so i need to get that i need to extract that team id very important one five and six this would be two three and four this would be two one and two column number one two so i'm i'm really trying to get that match idea i wanna get that that idea is very very important so i need to know when they select something how do i how do i build this id how do i build it if i know the column and i know the team one number and i know the team number then i can build it so now we've done that just that we've built that up so we can determine the team id so we know team one id we know team two id we also know the column number now we can build it up so the match id which is what i want is the column number plus the underscore plus the team one id plus the underscore plus the team two we've built this match id because it is this match id that we're saving here it is this match id that we need to know we need to save it we need to put it in and we need to determine remember we use that sum product here to determine where what row is located on based on that information based on the column based on team one based on team two so we're building this little string here and that's going to help us out so we're going to take that and we're going to place it directly in b7 once they've selected notice when i selected it b7 takes on that match id so as we select it regardless so that means if i select this one this team or this team notice is the same match id see that that's way both three both for so that's why we separate team one and team two because regardless of what i'm selecting here it is the same one the same thing here if i select the team one or the team two it is exactly the same match id because each game here each match has the same id and so we've done just that here place it in b7 i'm going to run the calculator because i want to make sure that we're actually calculating i really need to extract this row and make sure if it doesn't exist well it might not exist why might it not exist if i have a if i have something and this team hasn't been saved notice this is a zero why is that zero because this team doesn't exist it's not found inside here it doesn't exist here that's okay too we just need to add it once it's saved we need to know if it doesn't exist when we save it make sure to save it to a new row and that way it's not found but that's why this formula is so important because i need to know has this match id been found or not and so we do just that in here so calculating those formulas is very important okay so now what i want to do is i want to show the details also when i select something i want to show the details let's go back to when we've got all the data in here so if i select something i want to put in i want to show all those details in row 6. i want to know the location the time so i want to make sure that this is visible remember once i save this our macro which we'll get into our macro is going to hide row 6. so if i select it i always certainly want to make sure that those details are visible so if row 6 is not visible i want to unhide this row and we're going to do a macro to do that so if six and six the entire row is hidden is true then we're going to run a macro which we'll get into a little bit that's simply going to unhide that row and it's also going to display this button here so we need to have a macro to do that okay so also what i want to do is i want to clear any existing match details and now what i want to do is i want to determine has this match been saved right if we know that if team this one right here if b8 contains a row and not zero we know that it's been safe right so if b8 contains a row then what we can do is we can load all of the associated data from that whether it's the team the information the score and everything i can load that in so we can do that with this line of code if here b8 value does not equal empty or ba does not equal zero then we can load the saved match details and that's what we're going to do first of all we're going to set the matro based on b8 i need to put that into a variable and we're good this time we are going to use data mapping just as we did when we save it we are going to then load that in for the match column equals 3 to 11 and that means starting with column 3 we already have the tour id we already have the match id we don't need to load those in but i do need to load in everything else from three all the way to this one we don't need to load the row and for all the way to call 11 right here so i'm going to bring that in and all we're going to be doing is whatever is in the associated row here we're going to place it in n6 whatever is in here we're going to place it in b9 so that's just going to bring all that information bring it into these cells here and also our points located in b9 and b10 so we have that here so that's going to load in all of our match data then what i want to do is i want to check i want some default values right notice that if it hasn't been saved right if it's if it's empty i do want to put in some default values so what do i mean by that let's let's put another one in let's i think this one here's got some data here so if there's no data in here right notice there's no score here right these teams don't have a score i want to put in some default data if it's blank so that the user knows what to put in here so if this if the score is blank then like if these items are blank here the scores are blank then put in some default text and we do that here if n60 was empty then put in team 1 if 06 is angle so it just allows us to put in some default variables so the user knows what to put in signed bad so if we have a team we can do that so let's say we add a winning team on to this one here let's add a winner onto this and so okay so let's save that okay so now when we select this team here we see that there's only one team we see that there's no team two we have a team one but there's no team two so i want to put in some default so that the user knows to select the team so there's nothing here right so we want to put in that default so that's going to put in team two but only if p6 is empty okay great so that's just puts in the default values when they're blank else what if it's a new match right if it's a new match for certainly i do want to put in regardless if it's a brand new match putting in everything else here remember here if it's an existing match load save match details else it is a new match we're going to automatically load those default right if it's a new match in this case this one here is a brand new match it hasn't been saved before how do we know that we know that because b8 is 0. so then we're going to put in this default text here these four items default text because it's a brand new match and we do that right here and then also i want to do is if i if the row six entirety is true then match show i want to show run macro to show the match right show that just in case we want to run it okay what if we want to save it now right we've loaded it now i've loaded all the information now what i want to do is i want to run a macro that's tied to this button i want to save those details so how do we do that we can do that with this macro here called save turn the first thing what i want to do is i want to make sure that b4 contains a value right if we're trying to save a match but we have not saved the tournament right we can't do it we must assign a tournament id for every match right so we need to make sure that the tournament has been saved if it has not been saved we need to let the user know if b4 is empty please save the tournament before adding and then we're going to use the match name right details this is a dynamic based on the setup screen match name is right here so we can say please save match or game or or set or whatever it is here it's going to be based on that match name there and that's going to be dynamic so the user knows what also if b7 we need to set a match name before saving right if b7 is empty we need to make sure we select a mass name right b7 we need to have an id associated with it even if they're empty we always have an id associated with so we want to make sure if for any reason this is blank we need to have an id even the blank ones all have an id so we need to make sure that b7 contains a value so if b7 is empty it should never be but if it is please select a match before saving okay assuming that we have all the requirements set up now we're ready to load we need to know is it a new match or is an existing match once i save that this i just tied the macro this to refresh just for the fun of it so when i click here it just refreshes it that's kind of a refresh it was easier for me okay so basically what i know is if this is 0 it's i need to add a new row to our database here but if it already has a row sign associated then we can just save it to the existing row so b8 is going to let us know if b8 is empty or b8 equals 0 then it's a new match we're going to set that match row to the first available match plus one that's our first available row once i have that row then what i want to do is i want to also associate the row number inside l so we're going to simply do this why we're doing only this because we're going to add every row just to make sure and i'll show you what what's a minute every day so basically all we're going to be doing is adding that row at this point in the macro at this point right here all we're going to be doing is taking that new one adding that brand new row right here writing row here everything else is going to get added with data mapping all the way from 1 to 11 in this case and i'll show you that in just a moment else it's an existing match we're going to whatever is located in b8 is the match row so we can associate that now what i need to do is i need to determine the winner this is very important if here if i have a team and i decide a winner that we're going to save this this is very important let's say i've put a team here right and i put another team here and i also set a winner between these two teams where is that winner going to go right that winner here is the winner is going to go right here in a brand new row so not only do i need to assign a new row for the two teams that i'm seeing i also need to assign a winner to the next match right that next match which doesn't exist right so i need to determine what is the next match right so it's a little bit complicated right you've got two teams that are associated is going to be brand new inside this new row so we've selected remember this here look is one five six column one teams five and team six right here this one right here but i also need to assign the winner to column two and then number three right column two and team number three so the winner goes to this position here and the two teams go to this position but only if there's a winner that's been set so if this winner had been set i need to do two things right so if the winner if the winner equals blank then we're simply going to clear contents we're not going to award any points or loss right so we don't need to but now what i want to do oh so we've got to focus on points before we focus on that okay so let's do the points first points how do we know which points decide if the winner has been associated right i need to know what points points are set up here the winner gets the winning points here and the loser gets two points here so we need to associate that if there's a winner selected so if the winner doesn't equals empty we're simply going to clear out b9 b9 and b10 are associated with our points here b9 and b10 if there's no winner we can clear those out if there is a winner then i want to assign the points to b9 and b10 so else there's a winner let's put this winner winner winner chicken dinner okay so and then we also have no winner so no winner or winner so now we'll read if there's a winner i want to sign the associated points award the winning points to team one and losing points to team two if n6 equals the winner right n6 is our team one here's n6 if n6 equals the winner that is our team one so for example in here australia is our winner here this is team one so we want to make sure that team one gets ten points and team two gets two points so we do that inside the code b9 equals the winning points b10 equals the losing points else award the winning points to team two b9 gets the losing points and b10 gets you know why are we assigning to b9 and b10 because it is these two fields that are mapped to our data here b9 here are win team one points and b10 here are two points so this is how we bring our winning points into the database here all right so once we have that mapped out then we can do is bring in all the data for the match column equals 1 to 11. i want to bring all of the data but i want to wait make sure that if it's that i don't want to add in the default data if it's score i don't want to put the text score you know or if it says team one or team two i don't want to bring data into that i only want to bring actual data so i want to make sure if the match column equals five and the data the value equals team one then what i wanna do is say go to the next column right we don't wanna we don't wanna add in these default details we only want an actual data so we're just checking for the defaults otherwise we're going to add in all update all of the data that's associated as we go through each column that's it okay so now remember i was discussing the winner before right i need to know which team if we're on here this is where i was gonna go with it here okay let's just go back to where we are and say okay so we now what we need to do when we're saving it we've already saved all the data i've saved all the data here but what i need to do is if it's a winner i need to determine the winner right so let's say there's two teams here i need to know the winner i need to make sure that winner advances right so here the winner must advance to the next one so we need to add that winner on so if the winner does not equal empty then we're going to do the following the match id is based on b7 so we're just going to put that inside i want to put that inside a variable taking our match id whatever's in b7 placing that here in b7 here okay but what i need to do is i need to determine the winning match id for example this one the winning match id is going to be here the winning is going to go bracket 2 1. so i need to put that winner right here so the match 80 is going to be at 7. now the column number what's the column number here is going to be left based on the match id and the match we're going to extract that column number from the ids using that left just like we did before that column number is going to be 1 2 or 3 or 4 okay so we're extracting that column number from that id once i have that id if the column number equals 4 then we need to do something a little bit different we're going to go to the final match okay so if it's not then we continue on the column count is equal to now what is the column count basically it's like the team number i need to know what it is so to extract that we're again we're extracting the column count from that column counts like the team id match id we're going to use the length in string reverse match so basically what i want to do is if i take a look at here i see that i want to extract this 2 this 2. i want to know what is the last one right this 2 this is 3 this is 4. so i want to extract that number i want to look for the last underscore and i want to extract that based on that so i want to do that we're going to use the right feature function for that and we're simply going to extract that out right here pulling that count that last item there that's going to start using in string reverse the length of it we wrote this just a moment ago minus the reverse it's going to just basically extract that column extract the last let's put in the last number okay so we're extracting the last number now what i need to know if the column count is two or six so basically here's what i need to know if it is two this is 2 4 or 6 right if it's 2 and if it's 6 it's going to go to this top position if it's 3 or 4 it's going to go to this bottom position right so notice the top position albania here or the bottom position australia or the bottom position here so i need to know where to put it on same thing here here is the top position this one is the bottom position right if i select here you see this is two if i select here you see it's 4 2 that if it's 2 it's going to be the top position if it's 4 it's going to be the bottom position same thing here if it's 2 it's going to go to the top if it's 4 it's going to go to the bottom here if this is 6 it's going to go to the top and if it's 8 it's going to go to the bottom so i know i want to separate the top the winner is on the top or the winner is on the bottom 2 2 here six here is going to be top okay so using that if here the column number equals four or we got okay if the column count equals two or column count equals six then the team position equals one or the team position equals two team position meaning rate the top this is one this is two this is one this is two one two so i need to differentiate because i need to know where to place that winner are we replacing on the top or we're placing on the bottom right this will be the bottom this will be the top okay so we're just putting that into one or two inside the team position okay so now if the column number is less than four it means it's going to be the left side brackets we need to do something a little bit different than that if it's a column count is less than five column count is less than five then all we're gonna do is we're gonna set that winning match id i need to create a winning match i need what is that winning match id okay let's say i have a winner here albania and armenia that winning match id is going to be two one two right can i select here two one two so when i if i select one one two i need to extract that winning match id i know that the column if i'm on the right side that column is going to be plus one so i know any winner is going to move from column number one to column number two so we're going to get that we're going to add that in the column number plus one we know it's going to be an increase in column so if if the wing matches 1-2 right if the winning match id equals column number one plus two one dash two otherwise one three four so basically we're just determining where it's going to go on three or four or one or two it's going to update the winner based on that so if you're still falling what if it's on the right side of the brackets if it's on the right side of the brackets we're going to subtract one right because we're moving to the left right here right if we're on column number seven and i want to know the winning where's the winning the winner is going to be in column number six or the winner of column number six is going to move to column number five so we're subtracting a column here we're adding a column that's why we want to differentiate the left side from the right side because the winner reduces the columns whereas the winner here on the left side increases the columns from one column one two three or four otherwise from column seven six or five so we need to reduce it that's why we're subtracting the column number here and then we're simply adding at either one two or it's going to go to three four right so that's gonna just set that so basically we're just getting that winning match id i wanna put that into a variable i want to take once i have that winning match id i need to take that winning match idiot i'm going to place it directly here inside b11 why is that important because i need to know is there a row associated with that winning match id we're going to use that same sum product here but this time we're going to use the term id and this time we use a winning match id so i want to know if there's a winning match id what row is associated with this winning match id because i need to update the data inside that winning match id so we're going to do that here b11 equals the winning match id we're going to set that winning match id so basically all i do if i'm selecting here i need to know the winning match id which is this one here right so and that's that's it so once i have that i can extract the row if there is a row then we can use that if there's no row associated it hasn't been say we need to create a new row so we're going to first calculate it here we're going to set the wind rows to be basically on whatever's in b12 b12 if it's zero we need to do something so we need to add a new one so if the wind row is zero then match not found to sign a new row so if we're going to assign a new row we get the first available row inside the tournament now what i need to do is i need to take whatever is that terminal id i need to place it in column a i need to know the winning match id and b and the row l so these three things for a brand new one right that would mean in a the tournament id and b the match id and an l the row number so all those things get added in then what do we do else that's it that's all we need to do actually for our new one so we've assigned the windrow so once we've done that i can then take this now here's where it gets important remember we wanted to know what is the position is the position number one or is it position number two where is that why is that important because when i place that winning team i need to know where we're going to place that winning team in position number one or we're going to place that positioning team that winning team in number two so one goes here two goes here that's it that's how we determine the position so if the team positioning was one then e column e team one will take on that winner else h will take on that winner so that's how we place that winning team in the right row that's all we need to do then all we're going to do is run a macro called tournament load existing we're going to reload that tournament so let's take a look in here okay let's do a brand new team so notice there's no database associated with this and obviously this is going to be cleared out so let's say we add a team here let's go ahead and add something different morocco and say morocco is playing here the united states and we choose the winner as the u.s okay so now notice we notice there's nothing associated so this is going to add two brand new rows so take a look inside here i'm adding it to one five six column one row team five and team six so we're gonna add that that's gonna put that it's gonna put that winning team in position number one right here so when i save that here save the data here save it it's going to do just that it's going to put morocco in the united states here the winning is going to go here now let's take a look at how that appeared here so we have two new rows morocco and the united states the winner takes on the top position one which is going to go here brand each got created so that way one five six got created two three four got created here one column one five and six five and six also 2 3 and 4. so 2 column 2 3 and 4. so that's how we did so two new rows all right and then of course it gets reloaded bringing all that data here and we do that with that macro and then we're just going to hide the mat so we're just going to run the macro to hide it okay very very cool we got through that macro that is all we have to do for the match macros we are almost done we have a few macros on this sheet so we've gone over the math markers we've gone over the tournament markers now we just have a few macros on the sheet so let's go over that we have a few we also have a little leaderboard which i'm going to show you which is a really cool macro we'll be getting to that and then so the few simple macros here we'll go through them really quickly the leaderboards show when i run a macro all i need to do is show these columns and i want to show this button here and i want to hide the this button that would be show so this button gets hidden so that's just all we do here we're going to the show we're going to unhide those columns d through h hidden equals false we're going to show the all tournament options i want to show this option here there's a there's an option here that i want to show it's called all tournament options i want to show that and i also want to show this one here the hide standings button okay so i want to show all that so we're going to do all that through here we're going to refresh run a macro refresh leaderboard and we're going to select k2 hiding the leaderboard is going to be completely the opposite we're hiding those columns where we are unhiding those two shapes and we're going to show the show standing button and we're going to select k2 that's it for the hiding another macro leaderboard refresh now how do we keep it this is also the same macro that's hided here so when i select it i want to refresh this macro i want to load in all those data i want to create all the data it's going to be based on information all the database now whether it's based on all tournaments or just a single tournament i want to load in all of that data so how are we going to do that well of course it's going to be based on all the data that's contained inside here right we've got our tournament database all of our points here all of our winners are here everything's here but what we want to do is i want to create a separate criteria and separate results we already went over this criteria but i want to create something separate so the first thing i need to know is it based on a single tournament or is it based on all tournaments so we're going to have that here our cry leaderboard criteria here this is going to be based on a specific value if the tournament b5 equals true right meaning all tournaments b15 excuse me if tournament b15 equals true what does that mean that means this here if all tournaments if this equals true that means we're going to show all terms in that case i just want to create some criteria here that's going to show meaning not blank tournament id is not blank so we're going to do that otherwise i want to show a specific tournament id otherwise i want to show what's in b2 so that means when i select this not all tournaments just that single selected tournament we want to change this we want to show three so that only a single tournament shows up and we're only adding criteria for that so then what i want to do is i wanted to determine all of the teams that are associated with either this specific tournament or all tournaments and i want all the team ones to show up here and i want all the team twos to show up here then what i want to do is i want to take all the team ones and i want to place them here and i want to take all the team twos and i want to place them here then what i want to do is i want to take all of these and i want to create a list of unique teams based on this notice there's many repeats but i only want unique teams and i'll place these unique teams here then i have a ranking this is numbers that's always going to stay here that won't change just numbers then i so then i have rings then what i want to do is i want to determine create a formula i want to know how many of day one how many uh games that they want or matches they want i want to know how many they lost and i'll put that with formula i want to know the total points and i want to know the picture that picture is going to become in handy in the standings and we can do that with a formula just like we did before but that formula is also going to be based on whether it is all matches or whether it is only the existing match that's going to be based on basically a g depends on this whatever this is here okay so we know we're going to use countif the first one we want to know is i want to know how many there won so again we have some named ranges that are going to help us with this and we go in here we have some matches i want to know the team wins all the team wins that's a dynamic name range based on the winning teams we also have the team one here we also have the team one points here we have the team two here and we have the team two points here so that's going to really help us determine and we're going to be able to actually use sumif and countif to help us with that so let's take a look at some of these formulas here if a g3 equals empty or nothing right then what we're going to do is we're going to count if i want to count if all of the team wins based on am3 which is our first row once we bring down that formula to be all for the associated row okay keep in mind that ag3 is absolute in this case that won't change as we bring it down okay otherwise right we're going to i just want to know all the wins all the wins regardless otherwise what if ag3 is not empty if it contains a number i wanted based on that id then i only want to count those based on a specific tournament for them tournament id is equal to ag3 right only based on the specific tournament only want to count those right so we're basically counting it on whether it is all the tournaments or whether it is only an existing term and id so that's it so that's all we knew what about the number that's been lost well the number's been lost again we're going to determine whether it's all tournaments or an existing tournament if it is all tournaments we are going to determine all the team ones we want to know all the team ones how many plus all the team twos right so i want to count all of the games that they played in all of them regardless if it's a team match based on the m3 then i want to subtract out all the ones that they won right how many did they win so how many do they know when they won whatever's in a and three is how many they won so basically all the games that they played minus all the games so in this case they've played three games they've won two and they've lost one so we know that this here is equal to three dave lost one and they won one so based on if they've won it okay but what about this this is based only on a specific tournament right so in this case i want to know all the games that they've played only for this specific tournament so we're going to use countifs because we all want to not only based on a specific tournament but based on all the team ones that they won all how many times did they play as team one how many times did they play as team 2 and how many times subtract by how many total wins that they have that's going to determine the number of loss based on a specific tournament id now i want to know all the points right so what a bit again all the points it's also going to be based on whether it is for a specific tournament or for all tournaments again also based on ag3 we're going to use some if it's for all tournaments i'm using sum if i want to know all the team one points based on a specific team using sum if plus all the team two points remember so it's basically all the team one points all the team two points assuming that it is for that specific team regardless of whether that team is for team one or team two i just want to add up all the points irregardless of any tournament however if it is for a specific tournament then we want to use some ifs because it is based on a specific tournament based on the tournament id being an ag3 then again i also want to add up all of the points but it's only based on a tournament if it's team one and i want to add up all the points if they're team two based on that team based on that tournament id so we're adding up we're to get to all the points now again i want to extract their picture i want it just as we did before we're going to run a match based on the team name that's located here i want to run a match and i want to use indexing that team picture and i want to extract that that team picture is going to be helpful because i want to add that team picture inside those standings right brand new inside that leaderboard i want to add that picture here okay great so now that i have extracted the data what we're going to do is we're going to run an advanced filter and i'll walk you through the macro that's going to go up to this point we're running an advanced filter we have our possible criteria here we've got our results here where they're going to take those results we're going to add them together into a single column i'm going to run a brand new advanced filter but no criteria no criteria here because i only want just the unique teams without the criteria our criteria has already been set here so our teams here already have set a criteria here we're going to get those unique so let's bring the macro up until that point and then we'll work out from there so we're going to do leaderboard refresh so we want to determine the last row as long last results wrote last results row 1 and last results row 2. we need both of those because i need to know last results row one last results are two because i want to bring them all into a single column so we need two variables for that and then also i want the picture folder file and path just as we've done before in the picture shape this one we are going to turn off application screen updating to false just make sure we turn it on before the macro ends focus on the tournament i want to remove any standings and any pictures that are associated once i refresh it i want to make sure that all these pictures get refreshed and i want to make sure all the data now these pictures contain the word standing each one standing one standing two standing three right so i want to remove any picture that says has the word standing we're going to do that just with this looping through all the shapes and the sheet anything with standing we're going to delete i also want to clear the contents of all the data clearing out all this data right just clearing that out like that okay we have conditional formatting that's going to help us with that as we add i'll go over the conditional format as we add information conditional formatting will help us as we click here it is this macro that is tied the macro that we're going over that is tied to this option here right so if i right click here sign macro we see that is the macro that we're working on right now the leaderboard so once we clear all the data that's going to be it for our tournament for just a bit now we're going to focus directly on our tournament match that's the criteria that i was going over with you i want to clear any prior results inside this clearing out all the prior results starting with am all the way over to aq just clear i'm going to keep these rankings here i want to keep that here that's not going to change so i'm going to clear out all that data here clearing out and i also want to clear out any teams associated here all the way from ah to aj so we're going to clear all that out clearing an appraisal i want to determine the last row of data and if it's less than three exits so we're going to run our advanced we're going to run our criteria now our criteria is going to be based right here on ag2 through ag3 right here and our results are going to go in a h2 and ai2 so that way i have all the team one and all the team two that are associated with this tournament or if there's no tournament right if i've selected all tournaments here then it's gonna we're gonna have a much much larger list right because it's gonna be all tournaments but we'll keep it for a selected tournament the smaller data set is a little bit easier to work with okay so we've got our data there and so now what we've have our we have all of our results coming in here so now what i want to do is i want to get the last results row for both columns one based on column a h the other based on column a i that's going to give us our last row but then what i need to do is i need to combine the data of both those into a single column so they can run an advanced filter and have those results i only want the unique results so to do that we're going to get if the last result was less than three we're going to go to maybe there's no team one we're going to skip here but as long as team one has data what i'm going to do is i want to take those results from ah and the last result so i want to bring them directly into aj3 basically all we're doing at this point is taking this and bringing it right in here so we're going to bring it right there okay great now we can focus on team two if the last results row of team this should be team two last results were of team two is less than three then go to no team two assuming that we do have results for team two what i wanna do is i wanna bring it over here and now again i wanna but this time what we're gonna do is we're gonna bring team two over but i'm gonna start it out one row below wherever team one ended so to do that we're going to say the last results wrote plus one plus one that's here the last results row is here plus one is here this all the way to the last results row aj and the last results are a 1 plus the last results row 2 minus 2. why is that the last results row in this case is 7 right if i subtract 2 i know we're going to say it from 3 to 7 so i know the number because this one starts on 3 that's why we're subtracting 2. i want to bring this data over and i'm going to place it directly right in here once i have that through this here now what i want to do is i want to run an advanced filter i want to run advanced filter based on all these results but this advanced filter as i mentioned previously has no criteria however when working with advanced filters we want to make sure that we delete any prior criteria when working with advanced filters with no criteria we want to delete any existing criteria notice we do have a criteria that criteria is stamped in a named range so if we look in formulas name range we see that there should be a credit i guess we don't have one but if i run let's take a look i'm going to get a criteria here simply by running this tournament will help us get a criteria now what we're going to do is we're going to go back in here formulas name matters we're going to see that we have a criteria notice this criteria oops notice this criteria is here right i don't want that criteria i want to delete that criteria that criteria gets automatically created when you run an advanced filter so we want to delete that so to do that we're just going to do the following lines of code and we're going to delete the criteria if for some reason that criteria doesn't exist it would create an error so we want to wrap that in on-air zoom next and on-air go to zero so now what we want to do is get those unique teams just as i've mentioned so we're going to determine the last row and have those unique teams appear directly in am make sure the headers here are both the same so to do that we're going to run our advanced filter our advanced filter is going to be aj2 through aj in the last results row this this is the same row as we placed right here so that's just our last results row we're going to run advanced filter remember no criteria there's no criteria we're going to copy to am2 this is going to give us our unique list of teams that unique list of teams is very important once i determine the last results row i can then bring down all of those formulas associated in fact notice that there's zeros here that means i probably need to bring clear out those all the way through apq i don't like to have those formulas there and so make sure that this should see as soon as we're clearing out data right i really wanted to am all the way through aq i want to clear out those formulas i don't want them here unless we're using them right so i want to make sure we're clearing out all the prior results so now if i select this and unselect it here again going back in here we've cleared out all i like that better so i want to clear out all the data i'm keeping the standings here clearing out all the data okay that looks better now what i also want to do is i want to bring it once i have it i want to determine again the last row but this time based on am i want to determine the brand new last row this is 8. once i know the last row i'm going to take these formulas and bring them all down then i have all my data they're associated so to do that the last results wrote if it's less than three we're going to exit the sub there's no data right so when we have data i'm going to bring in those formulas taking the formulas from all the way from a n1 through aq and bringing them down directly from am through aq it's going to bring all that one loss total points and the picture all the way down to the last row now i've got my data all the way here now i can take all this data from al through ap i'm going to bring it directly inside our terminal bringing it directly inside d9 all the way through h and down bringing it exactly in so that's what we do in the next line of code okay but actually before i do that i want to sort right we want to make sure we start i want the highest points first so we can run a sort so inside our terminal i want to know all the points who's got the most points i want to put them at the top so it's going to be based on a sort so it's going to be based on ap3 so we're going to run a sort we're going to clear out those sort fields we're going to base it based on ap3 which is the number of points that's our key there we're going to base it descending right we want the highest points first so it's going to be descending then we're going to set our range again making sure we call out the sheet for each of these because we're inside the sort and we're going to run our sort based on am through aq am through aq not including the rankings because those will get added later so am3 aq now once we have that sort now we're ready to bring it inside that net tournament remember it's going to include those rankings i want those rankings here so we're going to start al all the way in the last row through aq not not aq ap just the total points not the picture so aq is going to bring that in so d h through h in the last results row plus 6 all the way from a l through a p in the last result that's going to bring in all of our leaderboard data now all we need to do is add the team and the pictures and the players right so now all we need to do is loop through this data and determine those pictures and create a small picture all we need to do again is duplicate this item right here sample add a picture and then duplicate it here so that's what we're going to do wrong going to create that picture folder as we did before we're going to set that results row set a loop from the 3 to the last results row i'm going to set that picture file based on what is located in aq that's our picture file in aq and i want to create that full file picture path here if it's empty or if the directory is incorrect as long as it's not empty here we go as long as it's not empty the picture path and as long as that directory is also not equal empty then we can add the picture so first we're going to duplicate that small sample shape that sample shape standard picture duplicating that giving it a specific name and giving it a number the result row minus 2. that's going to that way it's going to go 1 2 3. it's going to give it a unique number for each one not too important on that then what we can do is we can work with it so with that specific shape i want to do a few things i want to place it directly on the left and the results row plus 6 why the result wrote plus six notice this result rows on three notice our first result rows on rows nine so to compensate that difference we need to add six so i want to place it directly inside column median there all right so that's what we're doing here placing it on e in the left position top position here now what i want to do is i want to fill that picture with the user picture on the file path if there's an error we'll just go on and resume next maybe there's an issue with the picture that's it that's all we have to do to fill and place that picture and then application screen updating true okay very good so now also we have the tour show right touring show is just easy right all we want to do is this is this particular macro remember we were going to hide the tour edit tour save tour right when i save that tournament i want it hidden so all we're going to be doing is hiding row 4 for that tournament but i only want it shown if it is currently hidden right we don't want to run we don't want it if it is currently visible there's nothing to do right so when we're showing it we want to check is row 4 visible or not if hidden equals false then exit the sub it's already displayed if it's not then we're going to take that tour group which is those two shapes right here and we want to edit it is those two shapes here tour group i want to display that i want to unhide it i can display and i also want rows three and four hidden equal false i want to display it and k for select when we're hiding it we're doing the opposite we're going to hide that jury grip and we're going to hide those rows same thing for match show all we're doing is we're going to display that button and then we're going to display we're going to unhide rows 5 and 6 match hide doing just the opposite we're running that macro we're going to hide that button save match remember this is we're focused on this row here row six what do we want to hide or show it when we save it is that macro that hides it when we select it is the macro that shows it so it's just these two macros right here relatively simple okay cool so last thing what i want to show you is some conditional formatting now we've added some conditional formatting really a few rules based on ones on alternate row and one's based on the number of points so when we go into the home and conditional format and we manage rules we see we have three different rules one is based on alternate rows right so here we want two conditions one we want to make sure that d9 or any row that's associated down below notice not absolute does not equal empty and the mod row equals one this is odd row so odd rows i want to color this light green and then for the even rows i'm going to do just the opposite even rows equals zero this is for even rows and i just want to give it that dotted line around it and the white fill for that also i want to give it a color scale i want to create a color scale and i want to apply that color scale to column h and that color scale is going to be based on the lowest value is going to be white and the highest value is going to be green that way they're automatically colored based on that wow what an incredible training you have seen me be able to create these uh amazing tournament generator based on tournaments selected we already know how to assign names and pictures and teams how to advance teams onto that how to create a dynamic tournament based on the number of columns how to also assign winners and losers to that how to assign points to that how to save and update them how to create pictures within those shapes and space them accordingly how to create a new creating leaderboard and show or hide that leaderboard and do a whole lot more with an amazing conditional formatting thank you very much for sticking with us on this incredible training we will see you next week for a brand new training all right thanks again [Music]

Channel: Excel For Freelancers

Views: 33,011

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, Tournament Generator, Tournament Software, Free Tournament Software, Free Tournament Generator, Game Player, Fantasy Football Software, Fantasy Football Tournament, Generator Football Brackets, Team Matchup Software, Team Software, Player Software, Tournament Excel, Excel Tournament

Id: PObhYPzFsnE

Length: 149min 17sec (8957 seconds)

Published: Tue Dec 14 2021