You need to enter T. Valko 's formula under conditional format for the cell
you want to test, it should works. Your original question refers to
conditional formating unless it is something else you want.


JH

"mooresk257" wrote:

That gives a #VALUE error if the value in A2 does not match a value in the
list.

I found this works for any value in the list "Department":

=(NOT(ISNA(MATCH(A2,Department,0))))

but I was hoping for something simpler.

"T. Valko" wrote:

Try it like this:

=OR(A2=Department)

--
Biff
Microsoft Excel MVP


"mooresk257" wrote in message
...
I have a named list "Department" that is made variable by using this
formula
in the "Refers to" box:

=OFFSET(Sheet1!$B$2,0,0,(COUNTA(Sheet1!$B$2:$B$16) ),1)

Say the list contains the following:

Dept 1
Dept 2
Dept 3

I want to create a logical test to determine if a value in a cell matches
one or more of the values in that list to create a conditional format.
This
is easy enough to do by using an "OR" function to test the cell contents,
for
example, to test if A2 = "Dept 2" or "Dept 3",

=IF(OR(A2="Dept 2",A2="Dept 3"),TRUE,FALSE)

But what I am having trouble with is finding a way to test if "Dept 2" or
"Dept 3" are contained in list "Department".

I'd like to use some sort of lookup test using the list name "Department"
rather than a range of cells.

Any suggestions on how to do this?

Thanks!