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.
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?
Actually, Scratch my last question. How could I format so that it lights up as such, i.e. If the text in the two cells match, then the result is the matched cell turns green? I know the conditional color formating will do that. But how do you scale that formula to multiple cells?
MrBGL You need to use a conditional formatting rule that uses a formula. Then create a formula that compares the current cell with the cell that has the value. One rule can be set up to cover all result cells, but you need to take care with the $ signs in the cell references.
An example of a rule would be
=A5=$A$1
If that rule is applied to cells A5 to A20, each row (current cell) will be compared to A1. By using the $signs, you lock in the address for A1, but the address for the current row is relative.