You are invited on Monday to download MrExcels NCAA bracket and enter his free NCAA pool. The winner will get their choice of books from the MrExcel store. In todays Episode 461, we will take a look at the Data Validation commands used to make the NCAA bracket selection worksheet work.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welome back to the MrExel netcast.
I'm Bill Jelen.
You're a basketball fan or even if you're not a basketball fan.
In the United States the next three weeks will be kind of crazy.
Its March Madness the NCAA basketball pool where 64 different men's college teams enter the bracket to become the national champion.
Now about 15 years ago.
I had a manager who said to me, Hey guess what? You're in charge of running the basketball pool for the office.
Well. This is of course a big hassle.
I was young analyst at that time, and I basically started to keep track of all this in Lotus 1-2-3.
Few years ago someone came to me and said hey, I need to keep track of an NCAA basketball pool and we pulled the old system out and dusted it off, brought it into Excel.
I want to invite you to enter my basketball pool.
It's completely free and the winner gets their choice of books from the MrExcel store.
With my basketball pool, you're going to go to MrExcel.com/NCAA.html and download this bracket.
I want to talk about some of the excel behind this bracket.
Now actually this is Friday.
I don't know what the selections are.
These are last year's selections.
On monday morning, the real bracket will be available with the selections for 2007.
Basically, we have all the teams along the left-hand side here and to enter your selections you actually come over here column B and open the drop-down and choose one of the two teams Now last year it was Duke the number one see it's versus Southern and most people would choose Duke.
But then George Washington versus UNC Wilmington. You have to pick those.
Now what's really cool is after you've chosen those two selections and go on to the next spot to put the winner for the second round.
The drop-down is smart enough to know that you chose Duke and George Washington in the first round .
Let's take a look at how we do this.
The data validation is set up. Using data validation we allow a list and normally the list has to be right on this worksheet.
But you'll see here that I've referred to something called list 01. if we go to insert name define and take a look at list 01. It's actually on a sheet called sheet 2 A2 to A3.
Well that's a hidden sheet. You have to go to "Format" "Sheet" "Unhide" to take a look at sheet 2.
and you'll see that at some point we went through the tedious process of actually putting all the lists here and they're live formulas that update.
So as you choose something from round 1, the list on the right hand side for round 2 pull those choices and that becomes the validation list.
Now most of the time data validation is not allowed to work, if the list is on another sheet but if you're using workbook level range names validation is allowed to work.
So, I want to invite you to play.
Even if you know nothing about basketball please feel free to download the bracket and enter.
Some years the most bizarre things happen and the people that know nothing about basketball have the best chance of winning.
NCAA.html@MrExcel will kind of track the results over the next three weeks, as well as talking about some of the spreadsheets that we have on the backend to automatically generate the reports after each round.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
You're a basketball fan or even if you're not a basketball fan.
In the United States the next three weeks will be kind of crazy.
Its March Madness the NCAA basketball pool where 64 different men's college teams enter the bracket to become the national champion.
Now about 15 years ago.
I had a manager who said to me, Hey guess what? You're in charge of running the basketball pool for the office.
Well. This is of course a big hassle.
I was young analyst at that time, and I basically started to keep track of all this in Lotus 1-2-3.
Few years ago someone came to me and said hey, I need to keep track of an NCAA basketball pool and we pulled the old system out and dusted it off, brought it into Excel.
I want to invite you to enter my basketball pool.
It's completely free and the winner gets their choice of books from the MrExcel store.
With my basketball pool, you're going to go to MrExcel.com/NCAA.html and download this bracket.
I want to talk about some of the excel behind this bracket.
Now actually this is Friday.
I don't know what the selections are.
These are last year's selections.
On monday morning, the real bracket will be available with the selections for 2007.
Basically, we have all the teams along the left-hand side here and to enter your selections you actually come over here column B and open the drop-down and choose one of the two teams Now last year it was Duke the number one see it's versus Southern and most people would choose Duke.
But then George Washington versus UNC Wilmington. You have to pick those.
Now what's really cool is after you've chosen those two selections and go on to the next spot to put the winner for the second round.
The drop-down is smart enough to know that you chose Duke and George Washington in the first round .
Let's take a look at how we do this.
The data validation is set up. Using data validation we allow a list and normally the list has to be right on this worksheet.
But you'll see here that I've referred to something called list 01. if we go to insert name define and take a look at list 01. It's actually on a sheet called sheet 2 A2 to A3.
Well that's a hidden sheet. You have to go to "Format" "Sheet" "Unhide" to take a look at sheet 2.
and you'll see that at some point we went through the tedious process of actually putting all the lists here and they're live formulas that update.
So as you choose something from round 1, the list on the right hand side for round 2 pull those choices and that becomes the validation list.
Now most of the time data validation is not allowed to work, if the list is on another sheet but if you're using workbook level range names validation is allowed to work.
So, I want to invite you to play.
Even if you know nothing about basketball please feel free to download the bracket and enter.
Some years the most bizarre things happen and the people that know nothing about basketball have the best chance of winning.
NCAA.html@MrExcel will kind of track the results over the next three weeks, as well as talking about some of the spreadsheets that we have on the backend to automatically generate the reports after each round.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
