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!

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!

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!

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!

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

Yes, if entered as a formula on the worksheet without array entering it.

I thought you wanted a conditional formatting formula:

to create a conditional format.


--
Biff
Microsoft Excel MVP


"mooresk257" wrote in message
...
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!

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

When I used it in a conditional format, there was a formula error, so I put
it in a cell to test - and did not Ctrl/shift/enter to make it an array
formula when I tested it.

Oops.

I'll see if I can sort this out tonight and post back later.

Thanks!

"T. Valko" wrote:

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

Yes, if entered as a formula on the worksheet without array entering it.

I thought you wanted a conditional formatting formula:

to create a conditional format.


--
Biff
Microsoft Excel MVP


"mooresk257" wrote in message
...
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!

=(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

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

This works fine, thanks.

The only problem with =MATCH(A2,Department,0) is that it returns #REF if
there is no data in a list, and #NA if there are no matching values. This
means that the =MATCH(A2,Department,0) formula cannot be used with an "AND"
or "OR" to test other cell conditions with or against a value in the list.

For example, say I want to apply a conditional format to a cell either if
there is a matching value from the list in one cell, or the contents of a
different cell is "Yes". If the list is empty or does not have a matching
value, and I try to use

=OR(B2="Yes",MATCH(A2,Department,0))

the result of the formula does not equate to true or false, so no format is
applied.


"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

I would add the =isnumber() check.

=OR(B2="Yes",isnumber(MATCH(A2,Department,0)))

(untested)


mooresk257 wrote:

This works fine, thanks.

The only problem with =MATCH(A2,Department,0) is that it returns #REF if
there is no data in a list, and #NA if there are no matching values. This
means that the =MATCH(A2,Department,0) formula cannot be used with an "AND"
or "OR" to test other cell conditions with or against a value in the list.

For example, say I want to apply a conditional format to a cell either if
there is a matching value from the list in one cell, or the contents of a
different cell is "Yes". If the list is empty or does not have a matching
value, and I try to use

=OR(B2="Yes",MATCH(A2,Department,0))

the result of the formula does not equate to true or false, so no format is
applied.

"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