Forum Discussion
If then not working
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.
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.
IngeborgHawighorst THAT WAS AWESOME AGAIN!!!
I used the drop down list.
Last question, how could can I color code the cells with the iff then statements. I.e., If 1 Green, If 0 Red or no fill?
