Excel: Data Validation
Over the last couple of weeks at school we have been learning a lot about Excel. I am pretty good at formulas and different uses of excel. My NFL spread sheet is just one example of how to use simple formulas, but I have never used some of the more advanced formulas like “Data Validation”. Data Validation has several really excellent uses and I’ll show just the list function.
Note: See below to download the update file.
When I learn a new function or idea, I like to make some use out of it to help remember. Since I had the NFL excel spread sheet going and I decided this would a really good way to work with the data validation function.
Here were my issues.
- I wanted to do was make it easier to select teams.
- I wanted to make it easier to show select the winner of each game.
- I wanted to automate the scoring.
So here is how I accomplished this and excuse the “windowclipping.com” watermark; as I trying out the program to see if I like it.
- Highlight the cell you want to have the selectable list on.
- Go to the Data tab and click on Data Validation button.
- Select “list” from Allow list.
- In the “Source” area type or use CTRL and select the area you want the user to select from. I put in a column with the work “Select” list so I can have a word for the user to read and get a hint to select something.
- Go to the Input Message tab and if you want put a note to the end-user that pops up. I find it a bit distracting, so I am not using it.
- Go to the Error Message tab and enter an error message. Why, because somebody will do something strange and you might as well tell them they did something strange….
Now simply repeat for the winner column….
Now we need this count the winner and automatically count if the user picked the correct winner. So enter the following formula “=IF($G2=H2,1,”-”)”. If you have multiple players you would need to make sure the cell references are correct.
We also need to count how many games were won, so enter “=COUNTIF(I2:I17,1)” in the cell you want to have the totaling of wins in. Make sure the cell reference “I2:I17” is correct for you spread sheet.
Ok that is it… You can download the spread sheet and see what I have done. It is protected, but there is no password.
Download the free [Download not found]
Please note, I did not make this to promote gambling. My intention is for families and friends to have fun and enjoy the sport of Football.
Related posts:
- 2009 NFL Excel Spreadsheet (Free)
- Update to the Free 2009 NFL Spread Sheet Calculator
- 2010 NFL Spread Sheet: In Beta
- Programmatically Copy Excel Range with VB.NET
- Scale