Forum Discussion
Conditional Formatting Question
I would like to find an easier way to "gray out" names in spreadsheets.
I have a spreadsheet with columns of names, in a variety of different order on separate sheets based on different skills.
I want to be able to "gray out" the names on all sheets as the name "gets called", but that was too time-consuming to go to each sheet and do conditional formatting.
So, my solution was to copy all the sheets into one Master Sheet so that I could highlight the whole sheet, go to 'Conditional Formatting' > Highlight Cells rules > Text That Contains > Type in the name to highlight(gray out). Even this takes forever when doing it one by one, especially if I put them in 10 at a time.
Is there a way to build a Master Column....so that the names typed in that column will be grayed out everywhere it appears in the sheet/spreadsheet?
21 Replies
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.
What is the formal logic behind to compare the names. For example,
Martinez, J.D.
J.D. Martinez
J.D. Martinez BOS OF
are above 3 different persons or the same one?
The naming conventions are different based on different sources of the Rankings. The individual is the same for all three.
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_CTo use the formula, from the Conditional Formatting window click "New Rule" > "Use a formula to determine which cells to format".
As you mentioned, you want all the different sheets combined together and then a master column created like in my mini example. Easiest way to combine all sheets is with Power Query. With this approach you could even keep them in separate sheets and press Refresh to combine them into one.
Hi Ellis_C,
you can use the formula below in a Conditional Formatting rule. Please find example and file attached showing the solution.
=NOT(ISERROR(VLOOKUP(A2,$C$2:$C$20,1,0)))
The answer to your question is ......
Let me ask you a question first: Is it possible for us to engage in a little spreadsheet re-design?
From the way you describe your current situation, you have different sheets based on what kinds of skills various individuals possess. So John Doe has SkillA, SkillD, SkillF and appears on each of those sheets, whereas Jane Smith, with SkillB and SkillE is on different sheets. And it sounds like you need to be able to produce reports that list those still "un-called" who are on any given Skill List.
From a data integrity point of view, if my surmising is correct, you are exposing yourself to potential problems by doing it this way. Just to take a trivial example, if Jane Smith and John Doe were to get married, and do so in the old-fashioned way where she becomes Jane Doe, are you forced to go through all the sheets where her name appears and update that single change multiple times? Or maybe they are more hip and both take the name Doe-Smith? Then you change both of their names?
What I'm getting at is this: good database design avoids redundancies, for the sake of improving data integrity. So I'm wondering if you can create a single Master Database, where each name appears only once, and then the various skills SkillA-SkillZ, instead of being accommodated by separate sheets, are accommodated by individual columns on that master database.
It still would be possible to list possessors of SkillM who haven't been called (and not show all the other irrelevant Skill Lists).... Excel has ways to accommodate your output needs without designing them into multiple lists.
So, back to your question: "Yes"
What you ask--a master column which could cause names to be grayed out--could be done.
But I truly think (granted, without seeing your current workbook) that you'd be better served by re-thinking the way you store and retrieve your data in the first place.
Is it possible to upload a sample of what you currently have, after first removing identifiable personal information? I (or somebody else) could show you then more specifically how you could accomplish your goal, and perhaps give specific suggestions on a better way to accomplish that goal.
