Conditional formatting using VLOOKUP

[barongreenback]

Hi all

Using Excel 2007 and trying to see if I can do the following.

In sheet 1 I have a list of postcodes and in sheet 2 I have a list of postcodes with a name marked against each one. I'm trying to change the colour of the postcode in sheet 1 based on who the postcode is assigned to e.g. if Bill has the North London postcode, I want the background to be blue.

The formula I'm using in the conditional formatting box is this:

VLOOKUP(A1,Sheet2!A1:B127,2,FALSE)="Bill"

Excel then converts this into ="VLOOKUP(A1,Sheet2!1:B127,2,FALSE)=""Bill"""

Any ideas where I'm going wrong?

Many thanks.

[martindwilson]

1. you cant use references to other work sheets in cf like that
2.you are putting it in wrong any way
you need to put the = in
=VLOOKUP(A1,Sheet2!A1:B127,2,FALSE)="Bill"
so for 1.
to reference other sheets you need to make it a named formula
tools/insert/name/define pick a name say baron (in 2007 its formulas tab/defined names)
put that in the names in workbook field
in the "refers to" put
=VLOOKUP(Sheet1!$A$1,Sheet2!$A$1:$B$127,2,FALSE)="Bill"
click ok
now in your cf use
=baron

[barongreenback]

Martin - thanks for your help. Just to clarify, A1 is the cell I want to look up but this will need to fill down (you've specified this as $A$!1) - can I have the cell reference change so I can use the function throughout the worksheet?

[martindwilson]

change the names formula to=VLOOKUP(Sheet1!A1,Sheet2!$A$1:$B$127,2,FALSE)="Bill"