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.
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.