Forum Discussion
Conditional Formatting Question
I dont feel as though I explained it well enough. Without coffee, "skills" might not have been the best reference point.
Here is a screenshot of the spreadsheet I'm referring to.
As you can see, there are columns illustrating various Rankings of the same individuals. Also, the same individuals are broken down by positions and in some cases, they are categories by analytical date (K/BB%).
So, I wanted to "gray out"/conditional format highlight the names that have already been chosen, so that I know who is still available. I can check availability across the various rankings, positions, or even statistics.
One-by-one just seems to be the long way to do it and thought there would be a shorter method.
Obviously, I'm open to all recommendations as well.
Thank you for the feedback thus far.
Ellis_CI'm not fully understanding, but you could use the following formula in a CF rule.
=COUNTIF($A$2:$D$4,A2)>1
This one will grey out/Conditional Format a name if and when it is used more than once.
This formula is assuming the range you're checking is A2:D4 and that the first name occurs in A2.
The basic premise is that a group of my friends and I are "drafting" from this group of individuals. So, the spreadsheet starts completely clean.
As a player is taken, I (cross them off the list/gray them out/highlight them) so that I know what group of individuals are left to choose from.
The process of Highlighting is time-consuming, even though it is about five clicks of the mouse (plus typing the name).
Pardon my ignorance, but where do I apply the rule that you referenced.
Do I ...Highlight the whole sheet....highlight cell rules...more rules....use a formula to choose what cells to format.....?
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)?