Conditional Formatting Formula

[Tennisguuy]

I wanted to use conditional formatting to turn a cell red if a certain condition was true. The formula I wanted to use was a double lookup so I test the formula in an actual cell to see if it was working the way I wanted. Below is the formula

IF(OR(B57="OR",B57="TX"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE)) the formula worked and return a value of 35 which is correct.

The problem is when I tried to modify the formula to be based on a condition of the result I am getting a formula error. I tried different variations but I wanted formula to be true if the result was greater than 40. I tried the following in a cell to see if it would work

IF(OR(B57="OR",B57="TX"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE))>0.4. I know this is not correct because it says TRUE in the cell but it should be false because the value is 35.

I also tried this IF(AND(OR(B57="OR",B57="tx"),(VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE)))>0.4 but that didn't work either

 

[Fluff]

Re: Conditional Formatting Formula Help

If this
IF(OR(B57="OR",B57="TX"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE))
Returns 35 you get
35>0.4
which is true

 

[Tennisguuy]

Re: Conditional Formatting Formula Help

Thanks your correct that's what happens when you look at something too long a simple slip of the digit of .40 vs 40 can make all of the difference.

As I mentioned I tried testing the formula in a cell. That formula work when I changed it to 40. However when I put this formula into condition formatting I am getting an error that says There's a problem with this formula

=OR(B57="OR",B57="tx"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE)>40

 

[Fluff]

Re: Conditional Formatting Formula Help

You need to keep it in an IF like you posted originally

 

[Tennisguuy]

Re: Conditional Formatting Formula Help

Thanks very much that work but I didn't think you could use an IF statement in conditional formatting.

 

[Fluff]

Re: Conditional Formatting Formula Help

You can as long as it's returning either true or false, which your's does as you then compare the result of the IF with the >40

 

[Tennisguuy]

Re: Conditional Formatting Formula Help

ok thanks one last question. I have this formula in a template so it's blank until data is entered. When I put the formula into the conditional formatting I highlighted from B57:B157 if the statement is true it would make the cell fill red but when the template is blank and there are no states yet entered the entire column from B57 to B157 fills red bt when I enter a state then the red goes away until the condition is true. Is there a way to make the fill not red when no states are entered.

 

[Tennisguuy]

I forgot to mention one other thing even when I enter a state if its not a state that is in the formula it stays red.

This is the formula I have I have more states listed from what I originally posted because I didn't want to put them all in until I got it to work

=IF(OR($B$57="AZ",$B$57="CA",$B$57="CO",$B$57="FL",$B$57="ID",$B$57="MT",$B$57="NM",$B$57="NV",$B$57="OK",$B$57="OR",$B$57="TX",$B$57="UT",$B$57="WA",$B$57="WY"),VLOOKUP(VLOOKUP($B57,SOV!$BN$6:$BO$205,2,FALSE),SOV!$BO$6:$BQ$205,3,FALSE))>40

 

[Fluff]

You can wrap that formula in an And like

=AND($B57<>"",IF(OR($B$57="AZ",$B$57="CA",$B$57="CO",$B$57="FL",$B$57="ID",$B$57="MT",$B$57="NM",$B$57="NV",$B$57="OK",$B$57="OR",$B$57="TX",$B$57="UT",$B$57="WA",$B$57="WY"),VLOOKUP(VLOOKUP($B57,SOV!$BN$6:$BO$205,2,FALSE),SOV!$BO$6:$BQ$205,3,FALSE))>40)

which will deal with blanks.
However in your OR you have locked $B$57, but in the lookup you have left the row free to move $B57. Is it meant to be like that.

 

[Tennisguuy]

Thanks I had tried the same last night with the AND(B57<>"" in the formula but I had the last parenthesis in the wrong place. I didn't initially have the B57 as all absolutes with the OR statements but when I didn't the fill wasn't working the way I wanted so I tried making them all absolute. Everything is working now. I removed the absolutes. Thanks for all of your help. I really really appreciate it.