Conditional format formula problem

[Romileyrunner1]

Hi guys,
want to run something like this for a conditional format:


=IF(vlookup($B70,Rec!$N$10:$Q$89,4,false)="A")

Obviously it`s vlooking up the value of "A" up another worksheet.
This comes up with an error: highlighting the "A" part.
Is there a syntax error (probably).
Or is there a problem in running a condition from another worksheet?

Thanks.
RR1

 

[Jacob Skaria]

You cannot directly refer another sheet. Instead create a named range for
Rec!$N$10:$Q$89
(menu Insert>Name>Define) Refers to Rec!$N$10:$Q$89 name as myRange.

Copy paste the below formula in CF condition1

=VLOOKUP($B70,myRange,4,0)="A"

If this post helps click Yes

 

[Fred Smith]

You don't use If in a conditional format. You want an equation that
evaluates to True or False, as in:
=a16="A"

You can use a vlookup formula, as in:
=vlookup($b70,$n$10:$q$89,4,false)="A"

Unfortunately, you can't reference another worksheet in a conditional
format. If you need to do this, then do the Vlookup in another cell (say
B10), then use:
=$B$10="A"

However, what's the formula in the cell that you're applying conditional
formatting to? If it's the Vlookup formula itself, then simply use:
="A"

Regards,
Fred.

 

[T. Valko]

Well, you have a syntax error *plus* you're referencing another sheet.

The correct syntax would be:

=VLOOKUP($B70,Rec!$N$10:$Q$89,4,FALSE)="A"

However, even though you now have a syntax that will work Excel will
complain about referencing another sheet. You can get around that by giving
your lookup table a defined name.

Insert>Name>Define
Name: LookupTable
Refers to: =Rec!$N$10:$Q$89
OK

Then use this as the CF formula:

=VLOOKUP($B70,LookupTable,4,FALSE)="A"