Conditional Formatting on cells that contain formula results

[Hoody_89]

Hi All,

I've had a look around this forum and was unable to find an answer to my question. Essentially I have a table that depending on the value of a list item, shows 3 different sets of data in the same cells.

I want to apply conditional formatting to results on just one of the list item results cells. I have had success getting the red, amber and green styles applied for a single cell on the value, but I am having trouble scaling this to cover the entire table.

The formula in each cell is as per below, I know it isn't as optimised as it could be however it does get the job done.

=IF($I$2="On Off Delta",TEXT(MAXIFS(Output!$E:$E,Output!$M:$M,$A$7,Output!$C:$C,C6)-MINIFS(Output!$E:$E,Output!$M:$M,$A$7,Output!$C:$C,C6),"h"),IF('Trips And Distance'!$I$2="Trip Distance Total",SUMIFS(Output!$H:$H,Output!$M:$M,'Trips And Distance'!$A$7,Output!$C:$C,'Trips And Distance'!C6),COUNTIFS(Output!$M:$M,'Trips And Distance'!$A$7,Output!$C:$C,'Trips And Distance'!C6,Output!$D:$D,"Ignition On")))

The list values are "Trip Distance Total, Ignition Started, On Off Delta". The results of On Off Delta are an shown as an hour count using =TEXT(cell,"h"), typically between 1 and 15.

The conditional formatting style I have been using as per below (I2 is the list value cell)
Red -

=IF($I$2="On Off Delta",$I$16>=5)

- I16 is a random cell in the table that has a value lower than 5

Amber -

=IF($I$2="On Off Delta",$I$32>5,$I$32<7)

Green -

=IF($I$2="On Off Delta",$I$36<=7)

The condition formula above applies only to the cell that is referred to that the end of the code (the one it is checking the value against). I am struggling to think of a way to make this scale-able so that I do not need to go through and manually add 3 conditional formatting rules on each cell of the table.

Hopefully this explanation of this issue is thorough enough, please let me know if you need anything further! Here is a snap showing the formula and cell values to give an idea.

Cheers,
Mat

 

[RoryA]

If you're testing I16's value to format I16, you should remove the $ signs:

=IF($I$2="On Off Delta",I16>=5)

You can then just apply that formula to the table/column as appropriate, making sure you have I16 selected when you do so.

 

[Hoody_89]

If you're testing I16's value to format I16, you should remove the $ signs:

=IF($I$2="On Off Delta",I16>=5)

You can then just apply that formula to the table/column as appropriate, making sure you have I16 selected when you do so.

Thanks Rory, I did try that however it seemed to be locking the absolute reference to the cell. Do you think that if I just create the conditional format on one cell and then edit the range to be the entire table it will work? Previously when I tried it was just applying whatever format would go to the original cell to all cells in the table.

Cheers,
Mat

 

[RoryA]

If you're applying it to the whole table, then select the whole table before adding the CF and make sure that the cell reference at the end of the formula refers to whatever cell is currently active and that it does not include any $ signs.

 

[Hoody_89]

If you're applying it to the whole table, then select the whole table before adding the CF and make sure that the cell reference at the end of the formula refers to whatever cell is currently active and that it does not include any $ signs.

Okay, that might be my downfall. Thanks for that I will try it out and report back

 

[Hoody_89]

Hi Rory,

I tried it as you mentioned and had the same issue that I reported earlier. It applies the formatting to ALL cells based on the results of the cell it is checking against. I want it to ***** each cell independently. My CF range is the range of data in the table, and the formula i tried is below (no absolute reference on the cell with data, just the list item cell)

=IF($I$2="On Off Delta",I7>=5)

Images below to illustrate what I am seeing.

Cheers,
Mat

 

[DanteAmor]

Updated Formula:

=[COLOR=#0000ff]AND[/COLOR]($I$2="On Off Delta",[COLOR=#0000ff]$[/COLOR]I7>=5)

 

[Hoody_89]

Updated Formula:

=[COLOR=#0000ff]AND[/COLOR]($I$2="On Off Delta",[COLOR=#0000ff]$[/COLOR]I7>=5)

Thanks Dante, I just applied this and it made the CF apply to the entire table range which is good, however it hasn't worked as expected. The entire table is now red!

It seems to base the check on whether or not to apply to CF on the cell initially referred to in the AND, it does not auto-fill the column / row as required as it moves around the table.

 

[DanteAmor]

You put the formula as it is. You checked the signs $. They should be as I put it.
Apply to: =$C$7:$O$39

Check my file:

https://www.dropbox.com/s/1ke431ghaug0h7k/CF delta.xlsx?dl=0

 

[DanteAmor]

I already saw what the problem is. In column I you have texts that look like numbers. Then use the following formula:

=AND($I$2="On Off Delta",Value($I7)>=5)