The formula for C1<# is the VLOOKUP function that is inside the STYLE function in the Conditional Formatting dialog...
VLOOKUP($Sheet1.C1;$Sheet1.$A$1:$A$7;2)
Important thing to notice is the absence of the 4th argument in this VLOOKUP formula, ie the formula is
not: VLOOKUP($Sheet1.C1;$Sheet1.$A$1:$A$7;2;
0)
Without that zero, VLOOKUP does not look for a perfect match between the C1 value and the values in Sheet1.A1:A7.
Instead it looks for the greatest value that is less than or equal to C1's value.
It is essential that the values in Sheet1.A1:A7 be in ascending order for VLOOKUP to return a meaningful result when used this way.
Now with Sheet1.A1:A7 = {0;1;2;3;4;5;6} and C1=5.5, the VLOOKUP matches 5.5 with 5 because there is no 5.5 but 5 is the largest column A value that does not exceed 5.5.
With Sheet1.B1:B7 = {"Default";"Green";"Red";"Blue";"Magenta";"Yellow";"Cyan"}, the VLOOKUP goes on to return the value corresponding to the 5, ie "Yellow".
So, the style applied to C1 is then "Yellow".
Can I have data on sheet one with a conditional formatting that tests a cell and pulls the formatting data from sheet 2?
The lookup table can be on any sheet.
It can even be a part of the VLOOKUP formula...
VLOOKUP($Sheet1.C1;{0;"Default"|1;"Green"|2;"Red"|3;"Blue"|4;"Magenta"|5;"Yellow"|6;"Cyan"};2)
What conditional rule are you trying to apply to your cell(s)?
Ken Johnson