Conditional formatting with two formulas (If and)

[Peter Davison]

Hi,

I have a spreadsheet Sheet 1 (My main sheet)
It pulls formulas from 4 other different sheets Lets say Sheets 2 to 5) based on a validation list in Sheet1 cell A1 (The list is held in Sheet1 NN1 to NN4)
When sheet 2 is chosen on the list it compares cells in Sheet 1 to Sheet 2 and has a conditional format set to show differences. This is fine and works ok.
What I want to do is when I choose Sheet 3 from the validation list my conditional formats don't work because they relate to Sheet 2
Is there a way I can use "if" with "and" in the conditional format (Use a formula to determine which cells to format) so whichever sheet I was choosing on my list the conditional formats looked at that sheet

I was thinking the formula would be something like this
=if(and(A1 = NN1,Sheet1!C6<>Sheet3!C6))
this is where I then set the colour the cell would be

I just can't think how to do this?
Any help would be appreciated.

 

[Toadstool]

Hi Peter Davison,

You could use an IF statement but Conditional Formatting is applied where the condition resolves to TRUE and isn't when it resolves to FALSE, so you could just use:

=AND(A1=NN1,Sheet1!C6<>Sheet3!C6)

 

[Peter Davison]

Thank you I will give it a go tomorrow.
Appreciate all your help.