Forum Discussion
Creating an baseline Assessment
So I've done a partial solution, a separate tab with a count of how many have selected Y, N or O.
The formula for that is
=COUNTIF(Roster!$B$4:$B$20,Tally!C3)
I then calculate the percent that applies to each response.
=D3/SUM($D$3:$D$5)
The actual spreadsheet is attached, but here's an image of that portion that contains those summaries.
What is not clear from your first description is how you want those colors to be applied and where. Maybe you could spell that out a bit more. If it's just Y=Green, N=Red, O=Orange, then all you need to do is set the background colors. But if you're asking for smarter "Conditional Formatting," then you need to spell out a bit more fully what condition calls for what color, and where.
Thanks for the interest in this. What I need to do is make the red, green and amba come up on the summary tab on the corresponding skill. If the person says Y against as a skill they are attributed 80% and that is a green. If they say N they are given 25% which is a red and when they say O which is for work in progress they are attributed 50% corresponding colour for that cell in the summary turns to amba.
OK, we need to back up here. With these percentage figures coming into the picture you are adding to the situation you first described. So could I ask you to start from scratch and explain what you're doing more fully. It's sounding now like you may have a person self-assessing on multiple skills (which wasn't explicit in your starting post).... So in that case, you're looking for a summary of multiple skills for each individual person, rather than the summary I gave you which is more of a summary of a single skill but multiple persons.
Could you post an image of your existing spreadsheet, the one you have developed. Or describe it more fully. Or post it in Google Sheets or OneDrive and give us the link to access it.
- I sent you a sample using your Training Tally example as I could find where to attach it here. Please check your junk mail.
Here's what I think you're looking for. Now I see that it's one person, multiple competencies, multiple levels. (But I don't see any reference anywhere to those percentages.)
To see what's involved look at the Conditional Formatting dialog box, accessed two ways. Either from the Home tool bar, or the Format menu as shown below.
Select Conditional Formatting to see this: