Conditional Formatting a single cell when rows and columns switched?

KarenTF

I've setup a sheet to easily visualize different metrics for each month for different departments. Each metric has a different score that needs to be met, otherwise if it falls short of the target, then I'd like for it to change to red. The problem is, that different metrics have different scoring requirements, therefore, trying to use the conditional formatting, I'm limited to what can be formatted in the month columns. I've attached a snip of my sheet for clarification:

So you see I have what the planned target score for the different metrics and I'll have the actual score automatically populate using formulas referencing another sheet. I'd like to set the formatting to turn red when the score isn't met, but you see, there are different targets! Is there anyway to achieve this without having to switch the view, as this is the common display my team is used to and familiar with?

JamesB

@KarenTF Since you are using a formula to create the actual score, you could include the test for the scoring within the formula. Then if the scoring is not met, you could add a period or something into the formula result, then use this character to trigger your conditional format.

KarenTF

I don't believe this would work if I'm understanding what you are suggesting. The formulas used to pull in these scores are either averages of the scores input over time, or a summation of total items of specific inputs. This is why this is difficult to make the conditional formatting to understand what I'm trying to do. For instance in January, how can I set 3 different values to turn red at 3 different triggers within the January column? Because under 90 would be red, under 55 would be red, and under 13 would be red.

JamesB

@KarenTF If Are you saying you want all the items to turn red if the scoring is not met or just the one that is not met? Example, Monthly training goals would be black because 90% is met, but Leadership team would be red because it is less than 55? Or are you wanting Monthly Training, Leadership and Safety to be red, because 1 of the 3 are not met?

Can you paste the formula you are using to pull the data in from the other sheet?

KarenTF

The numbers you see in the picture above are the target scores for each metric (the Planned value); these are preset cells that are locked and merely there for quick comparison. Under those values (where it says Actual) will pull in averages using AVG(COLLECT so it can just look for the values for that metric in that month; I'm also using SUMIF function for other metrics.

JamesB

@KarenTF I would think you could use an if statement around your AVG(collect formula. I hope the structure below makes sense.

=if("YOUR AVG FORMULA"<[JAN]ROW#,"YOUR AVG FORMULA"+".","YOUR AVG FORMULA")

Using this mindset, if your Average being pulled in, is less than the percentage above, then put in the AVG result and add a period to the end. If it is not, then just put in the AVG result.

Then in your conditional formatting, you could look for the period to be present in the cell and have it color red.