Conditional Formatting with IF Statement

[DailyCaffeine]

Hello, I have a conditional format that only sometimes work.

I'm trying to make is to that whenever $J:$J says "Overdue", cells on a different sheet changes colours, otherwise it leaves the cells blank.

My two conditional formatting rules for them are
='Sample Sheet'!$J:$J="Overdue" turns it red
='Sample Sheet'!$J:$J<>"Overdue" leave it white


My problem is that it only sometimes work whenever I test it on the current excel I have but works if I make a new one. Is it because I have a lot of sheets on the one I'm working on or am I doing something wrong?

Thank you in advance

 

[jasonb75]

For starters, you don't need to use a rule to 'leave it white'. If the rule that changes it to red is false then the condition is not met so it will stay white (or more accurately it will have no fill). A white cell conceals the grid lines for the cell, when the cell has no fill the lines are still visible.

It is unclear from your post if you want to fill a single cell (G4?) if "overdue" is found anywhere in column J, if it should be (for example) J5 turns G4 red, J6 turns J5 red, etc. or something else.
Regardless of what is needed, your existing formula is wrong, the fact that it works 'sometimes' is simply luck based on the relative position of the cell saying "overdue" and the one with the rule applied. In order to suggest one that will work we will need clarification of exactly what is required.

 

[gordsky]

more info about the task required

 

[DailyCaffeine]

For starters, you don't need to use a rule to 'leave it white'. If the rule that changes it to red is false then the condition is not met so it will stay white (or more accurately it will have no fill). A white cell conceals the grid lines for the cell, when the cell has no fill the lines are still visible.

It is unclear from your post if you want to fill a single cell (G4?) if "overdue" is found anywhere in column J, if it should be (for example) J5 turns G4 red, J6 turns J5 red, etc. or something else.
Regardless of what is needed, your existing formula is wrong, the fact that it works 'sometimes' is simply luck based on the relative position of the cell saying "overdue" and the one with the rule applied. In order to suggest one that will work we will need clarification of exactly what is required.

I would like it so that if any cell in the column J has the word "overdue" in "Sample Sheet 1", it'll change the colour for cell G4 in "Summary" sheet to a red.
And if any cell in column J has overdue in "Sample Sheet 2", G5 in "Summary" changes. So on and so forth.

This is my first time with conditional formatting so I'm still working the ins and outs of how things exactly work and all help are greatly appreciated !

 

[jasonb75]

For that you need to use countif to check for the presence "overdue". Use the formula below as the rule to turn the required cell red.

=COUNTIF('Sample sheet 1'!$J:$J,"Overdue")

This will produce a count of how many "overdue" are found in that specific column, which is something that conditional formatting is able to use, although guidance would have you think that you need to use IF or similar to return TRUE or FALSE, this is not actually the case. TRUE, or any numeric value other than zero will make the condition true and the formatting will work. FALSE, text, errors, or a numeric 0 will make the condition false and the formatting will not be applied.

If your sheets are sequentially numbered (Sample sheet 1, Sample sheet 2, etc) then you could possibly use a modified version of the formula to sequence the conditions for them. IF in reality that is not the case then you would need to edit each one manually.

 

[DailyCaffeine]

For that you need to use countif to check for the presence "overdue". Use the formula below as the rule to turn the required cell red.

=COUNTIF('Sample sheet 1'!$J:$J,"Overdue")

This will produce a count of how many "overdue" are found in that specific column, which is something that conditional formatting is able to use, although guidance would have you think that you need to use IF or similar to return TRUE or FALSE, this is not actually the case. TRUE, or any numeric value other than zero will make the condition true and the formatting will work. FALSE, text, errors, or a numeric 0 will make the condition false and the formatting will not be applied.

If your sheets are sequentially numbered (Sample sheet 1, Sample sheet 2, etc) then you could possibly use a modified version of the formula to sequence the conditions for them. IF in reality that is not the case then you would need to edit each one manually.

Thank you for letting me know, this is exactly what I wanted and it is now working perfectly!

My sheets have different names on them so that'll need some editing but I am very thankful for your help ? !!