M
mooresk257
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!
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!