Forum Discussion
Conditional Formatting Question
Ellis_COk, this sounds like my first suggestion, but in reverse. So there is a pool of players to pick from and as you choose them, they grey out. So going back to my first reply, you would grey out the names on the right when used in column A (or multiple columns in your scenario).
Yes, the rule would get entered from Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
The naming convention you mentioned creates a problem. You can do partial matches in formulas such as VLOOKUP and COUNTIF but can you guarantee there are not more than one J. Martinez? And how would you distinguish which is which.
You need unique reliable names.
Wonderful,
I think we are getting close.
So, I would use the rule:
=NOT(ISERROR(VLOOKUP(A2,$C$2:$C$20,1,0)))
to accomplish this?
Yes, the naming conventions has been a slight issue. For 75% of the players, I usually type their first or last name that is unique. For many of the others, there are multiple players that would be highlighted if I just typed 'Martinez' or 'J.D.'. In those cases, I have to type in 'Martinez, J.D.' and 'J.D. Martinez' to get both naming conventions. As for unique players, there are only two or three scenarios where players have the exact same first and last name. In those cases, I just highlight it manually in the positional column.
With that said, will the above rule work if I type partial names (first or last)?
Ellis_CTo use partial matches just edit the formula to;
=COUNTIF($A$2:$C$3,"*"&$F2&"*")>=1
Then if someone types David P or Ms Gita into a cell in columns A:C it will still be recognised. This does run a danger obviously, so using unique names is still recommended.
For some reason, nothing is happening when I apply the rule and type names in various formats (full name, first last.
Am I supposed to put the 'Players chosen' list on the right side and then it highlights left?
I truly appreciate your assistance in helping with all this.
Ellis_CAs you can see in the image I had it so that the names are greyed out in the master column (column F) when selected in the big range you have on the left.
This can be altered if you want the master column on the left and definitely altered because your range of names is bigger. But that is the formula I used + the wildcards if you want them - but I can't guarantee that will work because names are unreliable.
Ellis_CPlease find a file attached with solution. The formula below was used which you can check out in Manage Rules
=COUNTIF($A$2:$C$3,$F2)>=1
This does not account for partial matches. I heavily encourage that the names are made unique to avoid any confusion and problems.