Hi lovely people! Hope everyone is doing well today.
I have a list of Project Names in A2:A100. In D2:BC100 there is a value of either Y or N. Some of the N cells are highlighted red and some of the Y cells are highlight yellow based on conditional formatting, but for this purpose, I only care about cells that are both N and red.
I want to create a summary of this worksheet that is easier to read. Essentially, I want to list only the projects that have at least one red N on their row, and then only list the value from the column header (row 1) for where there's a red N.
Here is a mock up of how it currently looks:
This is what I'm trying to achieve:
Is this possible, or is the conditional formatting a roadblock (vs normal highlighting?)
Using Office 365 ProPlus, and happy to use VBA as a solution if needed.
Thank you!
I have a list of Project Names in A2:A100. In D2:BC100 there is a value of either Y or N. Some of the N cells are highlighted red and some of the Y cells are highlight yellow based on conditional formatting, but for this purpose, I only care about cells that are both N and red.
I want to create a summary of this worksheet that is easier to read. Essentially, I want to list only the projects that have at least one red N on their row, and then only list the value from the column header (row 1) for where there's a red N.
Here is a mock up of how it currently looks:
Example.xlsx | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | |||
1 | Project | Country | City | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | ||
2 | Project 1 | N | N | N | N | N | N | N | N | Y | Y | Y | Y | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | Y | N | N | Y | Y | ||||
3 | Project 2 | N | N | N | N | Y | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | Y | Y | ||||
4 | Project 3 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | ||||
5 | Project 4 | N | N | N | N | N | N | N | N | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | ||||
6 | Project 5 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | ||||
Mock-Up |
This is what I'm trying to achieve:
Example.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Project 1 | 18 | 27 | 29 | 33 | ||
2 | Project 2 | 6 | 24 | ||||
3 | Project 3 | 18 | |||||
4 | Project 4 | 12 | 52 | ||||
Sheet5 |
Is this possible, or is the conditional formatting a roadblock (vs normal highlighting?)
Using Office 365 ProPlus, and happy to use VBA as a solution if needed.
Thank you!
Last edited: