Mar 08 2022 02:59 PM
Mar 08 2022 02:59 PM
I am trying to build a baseline people development tool. The intent is to have people do a self-assessment by choosing a drop-down menu where the answer is Y, N, or O. Y meaning that skill is done, N meaning not done and O meaning it works in progress. I need a separate tab with counts the Y and the N and O to flag green, red or orange with a percentage. Don't know where to start. The spreadsheets are built but I am missing how to create this link.
Mar 08 2022 03:21 PM
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
I then calculate the percent that applies to each response.
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.
Mar 08 2022 10:00 PM
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.
Mar 09 2022 05:57 AM
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.
Mar 09 2022 07:04 AM
Mar 09 2022 07:35 AM - edited Mar 09 2022 07:37 AM
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:
Mar 10 2022 07:47 AM
Mar 10 2022 07:51 AM