Forum Discussion
Conditional formatting with multiple criteria and dropdown list
I am trying to get a conditional formatting formula to work in the attached scenario. The CF is not showing at all on any column.
The 1st column has a data validation dropdown list. I have tried using an offset column with a number to represent the words in the CF formula but the result is no different.
The cells I need coloured have another formula in the actual cells to display a character under certain conditions so I can't reference the cell contents directly in the CF formula.
| Category | Start | Finish | 1-Jan-21 | 7-Jan-21 | 21-Jan-21 | 31-Jan-21 |
| In Progress | 2-Jan-21 | 28-Jan-21 | green | green | green | green |
| Complete | 1-Jan-21 | 15-Jan-21 | red | red | red | x |
| In Progress | 22-Jan-21 | 30-Jun-21 | x | x | green | green |
Current CF query I have is =and($A3="In Progress",$D2>=$B3,$E2<=$C3) with format cell to colour green
And in a separate CF, =and($A3="Complete",$D2>=$B3,$E2<=$C3) with format cell to colour red
Extrapolating this out, I am wanting it to come out as I have coloured it but it is not formatting the cells at all.
The logic I am trying to achieve here is: If the dates between (and including) the start and finish dates fall within the dates between (and including) one column and the adjacent one, then if the 1st column is "In Progress" it will colour it green, if the 1st column is "Complete" it will colour it red, and if the 1st column is empty then it will not colour it.
The formula above works ok within the spreadsheet (as an if statement) but not in conditional formatting. I am using Excel 2016. The attached is a simplification of the worksheet.
Any help with this would be much appreciated!
- =AND($A2="In Progress",OR(AND(C$1>=$B2,C$1<=$C2),AND(D$1>=$B2,D$1<=$C2),AND(E$1>=$B2,E$1<=$C2)))
Apply this rule for green and in the manager for cf in the field "applies to" enter: =$D$2:$G$4
=AND($A2="Complete",OR(AND(C$1>=$B2,C$1<=$C2),AND(D$1>=$B2,D$1<=$C2),AND(E$1>=$B2,E$1<=$C2)))
Apply this rule for red and in the manager for cf in the field "applies to" enter: =$D$2:$G$4
3 Replies
- =AND($A2="In Progress",OR(AND(C$1>=$B2,C$1<=$C2),AND(D$1>=$B2,D$1<=$C2),AND(E$1>=$B2,E$1<=$C2)))
Apply this rule for green and in the manager for cf in the field "applies to" enter: =$D$2:$G$4
=AND($A2="Complete",OR(AND(C$1>=$B2,C$1<=$C2),AND(D$1>=$B2,D$1<=$C2),AND(E$1>=$B2,E$1<=$C2)))
Apply this rule for red and in the manager for cf in the field "applies to" enter: =$D$2:$G$4- Thanks. While the cell refs in this solution don't match the sheet, the use of and(or)and has put me on the right track and I have it working now.
See the attached version. We need an extra date before the first one, and after the last one.
