Forum Discussion
If then not working
I have a spreadsheet that includes a simple If then formulae to help tally the NFL Pick Em Total.
If the Player's team matches the "Winners" row, then the formulae should be true and a 1 should be entered in the corresponding cell. For example, D15 reads "=IF(D5=D$14,1,0)". Therefore, D15 should return as 1. That is not happening. And there are several cells that are not working properly.
Please help!
That's what data validation has been invented for. Depending on what you want to achieve, there are different data validation methods to go about this.
You could use a list with all the three letter codes for the teams, plus the "NO PICKS" option, or you could dynamically calculate the valid entries with a custom formula like this:
=OR(D5=LEFT(D$4,3),D5=RIGHT(D$4,3),D5="NO PICKS")
Apply that formula to the cells D5 to Q11. It will allow either the first three characters from row 5 in the current column, or the last three characters from that cell, or "No Picks".
A list dropdown might be easier to set up and more robust, though.
6 Replies
Hi MrBGL
whenever something like this happens, try to get to the root of the problem. Excel does not recognize that the two cells are the same, but you think they are the same. So, who is right?
Check the contents of the two cells and you will find that one cell has a trailing space. "HOU" is not the same as "HOU ". So Excel is right after all. (That's a relief).
IngeborgHawighorst Wow! Such a simple solution. Not sure how I missed that. Thank you so much.
Any ideas How I could program the sheet to give the user a predefined choice so that the data enterer cannot make that mistake?Thanks again
That's what data validation has been invented for. Depending on what you want to achieve, there are different data validation methods to go about this.
You could use a list with all the three letter codes for the teams, plus the "NO PICKS" option, or you could dynamically calculate the valid entries with a custom formula like this:
=OR(D5=LEFT(D$4,3),D5=RIGHT(D$4,3),D5="NO PICKS")
Apply that formula to the cells D5 to Q11. It will allow either the first three characters from row 5 in the current column, or the last three characters from that cell, or "No Picks".
A list dropdown might be easier to set up and more robust, though.
