How to conditional format a cell where no further action is required.

Deepa SP

Hi, I am trying to build a heat map of different teams required for various projects to workout a heat map. The first column is name of the deliverable and the next 10 columns are completed by PMs. These 10 columns are various technical teams and PMs choose from the drop down to indicate which months they need. The colour columns are then availability RAG for each of these teams for each of the three months. Basically, I want to black out the cells where no action is required. Currently if a team is "not required" for a deliverable, then I have set up conditional formatting to block that cell as black. As a next step , I would like to automate and block team cells if they are not required for a specific month. Example : Row 1 : Systems team is required only for August, which means July and September can be blocked out. this will roll up into a report for the systems team to work only on the specific areas they need to plan their resources for.

What I am trying to avoid is to conditional format for each of the month across each of the teams (10 * 3 = 30 col). Is there a way we can use formulas to use this. BTW, all the coloured columns have RYG dropdowns.

Genevieve P.

Hi @Deepa SP

It looks like you're working with @Paul Newcome on a formula solution for the symbols instead of using Conditional Formatting on this post, here. Reports can use Symbols as criteria, so this would be a better option than using Conditional Formatting.

Paul's formula should work, as long as "July" in your drop-down is exactly spelled this way... you'll just want to make sure your "grey" says "gray" with an A for the symbol column to recognize it.

=IF(AND([Systems - Required (next 3 months)]@row <> "", [Systems - Required (next 3 months)]@row <> "July"), "Gray")