Forum Discussion
Color Tracker
You could help us help you by giving some more basic information on how, exactly, to navigate your workbook. OR, maybe better, create a simpler example that could be used to show you how to have what you want to do.
One thing I want to do is that for each thing that pops up as either red or orange (in some of the sheets, not all have due dates), there is a dedicated sheet to tell me what exactly is each person deficient in.
If you want to work with your original Tracker V3, that's OK, but answer a couple of questions so that we don't need to begin from ground zero.
- Is one of those the "dedicated sheet" that you refer to? Which one? Or are you asking us to create that sheet and demonstrate how to populate it?
- How are we to identify the various deficiencies? What piece of data (or pieces of data) would be the criteria for having them show up on that dedicated sheet? It would be cleaner (more direct) if we are able to write formulas that look for data itself, not colors. [In general, spreadsheets are more reliable and cleaner in design if the use of color is kept to a minimum and ONLY used at the final output stage, not as an intermediate flag or highlight.]
Those are for starters. Anything else that you can say that would help navigate the various sheets would be helpful, but at a minimum, please answer the two questions above.
2. Various deficiencies will be annotated in conditional formatting, which starts typically X days out from when it's due again.
That answers my first question. Let me try again with my second.
When I asked "How are we to identify the various deficiencies? What piece of data (or pieces of data) would be the criteria for having them show up on that dedicated sheet?" I was not asking how we'll highlight them once placed on the RED&ORANGE Tracker sheet; I'm asking what are the criteria that cause them to be placed there in the first place?
If we're going to write a formula that picks items off those other sheets, in other words, what specifically leads to one item being picked as deficient and another passed over? I realize, you're familiar with your data and probably consider this "obvious," and it may well be in retrospect, but if you're asking those of us not familiar with your situation to help you with creating a formula to extract things, you can help us help you be articulating those criteria.
If, for example, there's a need to recognize "X days out from when it's due again" what specifically is that value X? And if it differs by person or by project or by some other variable, such that we need to construct a table of what "X" means for project A vs project B, then spell those out.
- How the tracker works is whenever you input a date of the last time you had an appointment, the adjacent cell will take the date and automatically add time to annotate how long it is till the next date is due. (Some are annual, and some are quarterly.) I have conditional formatting rules that, based on how far the next date is from today is, to give me a particular color.
OK, I finally see the difficulty here. What you've done is put the rules that determine deficiency into the Conditional Formatting rules in the subordinate sheets. That means they're invisible to whatever routine you'll be using to extract the rows to the Tracker sheet.
What I'd recommend is that you move those rules to what we might call a helper column in the subordinate sheets, and have that helper column, based on the formulas, the IF condition, enter a value or a word and that word then be used in a FILTER function to extract to the Tracker sheet the ones that meet your criteria. For example:
=IFS(
H3<TODAY()+30),"Red",
AND(H3>=TODAY()+30, H3<TODAY()+60),"Orange",
AND(H3>=TODAY()+60, H3<=TODAY()+90),"Yellow"
H3>TODAY()+90,"Green"
)
As noted, once that formula is in place, the words--in general you should always leave the conditional formatting for the final output--can be used as the basis for filtering the items you want displayed in the Tracker sheet.
Frankly too, given the number of courses, I think you'd be better served by having a single column in the subordinate tabs, a single column in which all courses appear, with the corresponding dates adjacent to that column. That way, for example, Sgt Oleskevich's deficiencies would appear vertically, one after the other, (and they could be sorted by priority), rather than horizontally.
Now, as to the FILTER function, you'll need an up-to-date version of Excel -- you don't say what version you're working with. Here are two links that explain the FILTER function. One is from a good reference site. The other a YouTube video produced by Microsoft.