Conditional Formatting a cell from a "true/false" VLOOKUP

Works for me..

Do this..

- Select cell you intend to conditionally format
- Conditionally format using new rule -- formula to determine which cells to format --
Use this formula
=VLOOKUP(C5,'Sunday Duty times'!$A$4:$N$99,3,FALSE)<1600

- Format --Red

Hmm? earlier, "im fairly sure" this formula was excepted but didn't do anything.
But now, im getting "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria"

This VLOOKUP is a reference to another sheet, in the SAME workbook?

just tryed without the"=" and this statment is accepted, but don't work. must be what I did earlier.

any ideas?

Try this...

A2 = 0752-1543

As the conditional formatting formula use:

=--RIGHT(A2,4)<1600

I'm assuming that all the times are entered as a string of 4 digits.

A2 = 0752-1543

RIGHT(A2,4)

Returns N number of characters from right to left of the referenced cell. In this case N=4 so we want to return the 4 right-most characters from the value in A2:

RIGHT(A2,4) = "1543"

RIGHT(...) is a TEXT function so the value it returns is TEXT. In this case it returns the TEXT string "1543" which is not the same as the numeric value 1543.

Since you want to do a logical comparison of this value to the numeric value 1600 we have to convert the TEXT string "1543" to the numeric value 1543.

One way to do that is to use the double unary minus --.

The inner - converts the TEXT number to a negative numeric number:

-"1543" = -1543

The outer - converts the negative numeric number to a positive numeric number:

--1543 = 1543

--RIGHT(A2,4) = 1543

=1543<1600 = TRUE

You're welcome!