I think I'd lose something in understanding what's supposed to happen.

(Sometimes I do things that make it easier for my sake--not necessarily for
excel's sake.)

"T. Valko" wrote:

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

As a conditional formatting formula you even just use:

=MATCH(A2,Department,0)

When MATCH evaluates to a number the format will be applied. When MATCH
evaluates to #N/A the format will not be applied.

--
Biff
Microsoft Excel MVP

"Dave Peterson" wrote in message
...
You could use =isnumber() instead of =not(isna(...))


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!






--

Dave Peterson

--

Dave Peterson