In offices throughout the US, people are starting to turn in their NCAA bracket sheets to the office commissioner. Whether you are asking accountants to fill out an NCAA bracket or sales reps to fill out a sales forecast, it makes sense to collect the data in an Excel workbook. In Episode 464, we take a look at a macro that can be used to gather entries from the individual worksheets and combine them into a master worksheet.
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Want to invite you to enter our NCAA Bracket.
Go to MrExcel.com/NCAA.html Download the excel spreadsheet.
Enter your pics. Mail back again.
It's absolutely free. You have a great chance to win some excel books.
Now we've been talking this week about the basically the hassle of entering everyone's pics in the NCAA sheet and we talked about how I had formulas to help choose the higher seed.
That's what most people choose and then yesterday we talked about formulas to check to make sure, I hadn't keating data in wrong and really for the first dozen or so years we were doing the bracket.
we basically keat all that data in but then we finally said, well you know it would be really easy if rather than people faxing their entries in to us, if we could give them an excel workbook and have them fill out the entries themselves with the guys of course saying that it would be easier for you to choose excel rather than having to get the USA today and that whole thing.
Then the great benefit to that is once people send the bracket back in, we then can electronically harvest their answers.
So if you remember from last Friday, We have this bracket where basically we have the drop downs.
People just have to go through and choose which team they would like and then the next drop-down is smart enough to offer the winners from round one.
They fill in their "Name" over here, filling the "Tie Breaker" and Their "Email".
They would email this back to me then and what I do is basically once it arrives in the email, I open the workbook and rather than having to copy and paste, we wrote a quick little macro and I tie it to Ctrl G for gather. Gather all the entries from here.
The macro basically goes through and it says, we're going to take this person and add them to the next available line in the worksheet.
So I'll hit Ctrl G and just that quickly and it confirms that they chose all correct picks, and we'll take a look and we'll see that, there is sure enough in row 15, the new person's entry.
One thing that we've always done in our competitions, we have two players that are computer-generated.
The first player is called "seed" and that person basically just chooses the higher seed all the way through.
If you're scoring exactly the same as seed we know that you really use no imagination whatsoever.
The other player is a player called "Random".
Now most of the time "Random" does horribly because he knows nothing about basketball.
It just is choosing randomly, but there was a situation last year, where the results were so crazy that random is actually in the top five or six for most of the tournament.
One other feature about our pool is that it's so easy once the games start to actually figure out the results.
because excel is doing all the formulas.
We can then spend some time to take a look at people's pics and and write some interesting commentary.
and we always tend to make fun of anyone who's doing worse than random.
Although again as I said last year that was more than ninety percent of the field.
So today's Wednesday.
You have less than a day to the game start tomorrow at noon.
Go out to MrExcel.com/NCCA.html Download the Bracket, fill it in send it in.
You have a chance to win some great books and a reason to pay attention to the NCAA men's college basketball finals for the next three weeks.
Thanks for stopping by. We'll see you tomorrow for another netcast from MrExcel.
Want to invite you to enter our NCAA Bracket.
Go to MrExcel.com/NCAA.html Download the excel spreadsheet.
Enter your pics. Mail back again.
It's absolutely free. You have a great chance to win some excel books.
Now we've been talking this week about the basically the hassle of entering everyone's pics in the NCAA sheet and we talked about how I had formulas to help choose the higher seed.
That's what most people choose and then yesterday we talked about formulas to check to make sure, I hadn't keating data in wrong and really for the first dozen or so years we were doing the bracket.
we basically keat all that data in but then we finally said, well you know it would be really easy if rather than people faxing their entries in to us, if we could give them an excel workbook and have them fill out the entries themselves with the guys of course saying that it would be easier for you to choose excel rather than having to get the USA today and that whole thing.
Then the great benefit to that is once people send the bracket back in, we then can electronically harvest their answers.
So if you remember from last Friday, We have this bracket where basically we have the drop downs.
People just have to go through and choose which team they would like and then the next drop-down is smart enough to offer the winners from round one.
They fill in their "Name" over here, filling the "Tie Breaker" and Their "Email".
They would email this back to me then and what I do is basically once it arrives in the email, I open the workbook and rather than having to copy and paste, we wrote a quick little macro and I tie it to Ctrl G for gather. Gather all the entries from here.
The macro basically goes through and it says, we're going to take this person and add them to the next available line in the worksheet.
So I'll hit Ctrl G and just that quickly and it confirms that they chose all correct picks, and we'll take a look and we'll see that, there is sure enough in row 15, the new person's entry.
One thing that we've always done in our competitions, we have two players that are computer-generated.
The first player is called "seed" and that person basically just chooses the higher seed all the way through.
If you're scoring exactly the same as seed we know that you really use no imagination whatsoever.
The other player is a player called "Random".
Now most of the time "Random" does horribly because he knows nothing about basketball.
It just is choosing randomly, but there was a situation last year, where the results were so crazy that random is actually in the top five or six for most of the tournament.
One other feature about our pool is that it's so easy once the games start to actually figure out the results.
because excel is doing all the formulas.
We can then spend some time to take a look at people's pics and and write some interesting commentary.
and we always tend to make fun of anyone who's doing worse than random.
Although again as I said last year that was more than ninety percent of the field.
So today's Wednesday.
You have less than a day to the game start tomorrow at noon.
Go out to MrExcel.com/NCCA.html Download the Bracket, fill it in send it in.
You have a chance to win some great books and a reason to pay attention to the NCAA men's college basketball finals for the next three weeks.
Thanks for stopping by. We'll see you tomorrow for another netcast from MrExcel.
