Forum Discussion
Conditional formatting
I am working on a planning sheet in Excel with each column representing a month and each row representing a development task. I used grouping to map several rows to the same topic (chapter, if you will) and thus created more layers.
Per row I marked the months in which our company needs to work on the task. I would like to have the rows depicting the chapter/topic to be coloured based on whether or not one or more of the underlying cells are coloured.
I attached an example. Row 4 is a chapter. Row 5 is a chapter within the chapter of row 4. Row 5 needs to be coloured if one or more cells in rows 6-10 are coloured. Row 4 needs to be coloured if one or more cells in row 5-38 are coloured. Row 20 needs to be coloured when one or more cells in row 21-24 are coloured, etc.
Help is greatly appreciated.
Peter
PeterSchmeits so I started down 1 path and got it to work but then thought of another option that is cleaner and easier. In BOTH cases I add an "x" or "y" to the cells that you manually highlighted (which you should remove that manual color). I used x and y because you had 2 colors and in theory could have multiple conditional formats to color differently accordingly but that gets tricky when you have both in the same chapter/subchapter. The 2 versions then:
a) split the chapter, subchapter, ... names into their own columns and then I created a formula
b) I added a column that 'redefined' the chapter, subchapter, etc... into a standard table of contents style: 1.1.a.1, 1.1.a.2, etc... It doesn't matter letters, numbers, roman numerals, etc... as long as all the corresponding sub-sections start with the parent id. (NOTE: you have 1 issue because you have chapter 5 with subsection 1-11 and then define a chapter 5.1 with additional subsections. So maybe I should have use 5.0.1 for the first group?)
See attached files and the conditional formatting inside each.
- You need to approach this problem from a different angle. You can not use color as a basis for determining if it meets a conditional formatting formula. What you need to do instead is create a formula that will incorporate the sub-chapter criteria. Maybe if you supplied a sample sheet (no confidential/personal info) we could help figure out what that might look like.
Thank you for your reaction. I submit a sample sheet containing mock information. I started using colours for visualisation, but if text (for instance an X) is a better way to indicate that in that month we will work on that item, I am fine with that.
Thanks again. Hope you can do some magic
PeterSchmeits so I started down 1 path and got it to work but then thought of another option that is cleaner and easier. In BOTH cases I add an "x" or "y" to the cells that you manually highlighted (which you should remove that manual color). I used x and y because you had 2 colors and in theory could have multiple conditional formats to color differently accordingly but that gets tricky when you have both in the same chapter/subchapter. The 2 versions then:
a) split the chapter, subchapter, ... names into their own columns and then I created a formula
b) I added a column that 'redefined' the chapter, subchapter, etc... into a standard table of contents style: 1.1.a.1, 1.1.a.2, etc... It doesn't matter letters, numbers, roman numerals, etc... as long as all the corresponding sub-sections start with the parent id. (NOTE: you have 1 issue because you have chapter 5 with subsection 1-11 and then define a chapter 5.1 with additional subsections. So maybe I should have use 5.0.1 for the first group?)
See attached files and the conditional formatting inside each.
