Formula/function that checks for a set of values.
 

I am not sure if there is a function for this, but I am setting up a
schedual using Excel. I want to create a formula/function that looks
at a range and checks each column to confirm that there is at least one
value of “E” “N” and “D” preset. There are other values that dont
matter, and it is ok if there is more than one of the required values,
there just needs to be at lease one of each (“E” “N” and “D”). If one
of the columns is missing one of the values I’d like it to mark that
column in some way that makes it stand out (color?).

I have an example .xls file of the schedual at
http://home.pacbell.net/memnok/Example.xls

Thanks in advance for anyone who can help.

Clint


--
Memnok

Formula/function that checks for a set of values.
 

Hi

Select your columns, say A through to G, then go to Format/Conditional
Formatting. Select Formula Is and try this:
=OR(COUNTIF(A$2:A$100,"E")=0,COUNTIF(A$2:A$100,"D" )=0,COUNTIF(A$2:A$100,"N")=0)

Hope this helps.
Andy.

"Memnok" wrote in message
...

I am not sure if there is a function for this, but I am setting up a
schedual using Excel. I want to create a formula/function that looks
at a range and checks each column to confirm that there is at least one
value of “E” “N” and “D” preset. There are other values that dont
matter, and it is ok if there is more than one of the required values,
there just needs to be at lease one of each (“E” “N” and “D”). If one
of the columns is missing one of the values I’d like it to mark that
column in some way that makes it stand out (color?).

I have an example .xls file of the schedual at
http://home.pacbell.net/memnok/Example.xls

Thanks in advance for anyone who can help.

Clint


--
Memnok

Formula/function that checks for a set of values.
 

Hi

Select your columns, say A through to G, then go to Format/Conditional
Formatting. Select Formula Is and try this:
=OR(COUNTIF(A$2:A$100,"E")=0,COUNTIF(A$2:A$100,"D" )=0,COUNTIF(A$2:A$100,"N")=0)

Hope this helps.
Andy.[/color]

Andy, thanks, I think we are on the right track! I was not sure it
this was possible. I went into the schedule and highlighted the range
that I wanted the formula to check and applied it in the Conditional
Formatting screen. I had the format fill with yellow. The only
problem is that it highlights the column next to (to the right of) a
column without a value of “E” “N” and “D” preset.

I uploaded my experimental schedule to
http://home.pacbell.net/memnok/Schedule.xls

Maybe you could take a look? The range selected for this formula to
check is B4 to AF21.

Thanks,
Clint


--
Memnok

Formula/function that checks for a set of values.
 

Hi

I have misunderstood. I've had a look at your spreadsheet. So what you want
to do is highlight the whole column if there is no E, no D and no N in the
column? If any column has E, D or N it stays as it is?
Select the whole area (from B4 to AF21) and go to Format/Conditional
Formatting. First of all, delete the existing formats using the Delete key.
Then set Formula Is and try this:
=AND(B$4:B$21<"E",B$4:B$21<"D",B$4:B$21<"N")

Hope this is what you want!
Andy.

"Memnok" wrote in message
...

Hi

Select your columns, say A through to G, then go to Format/Conditional
Formatting. Select Formula Is and try this:
=OR(COUNTIF(A$2:A$100,"E")=0,COUNTIF(A$2:A$100,"D" )=0,COUNTIF(A$2:A$100,"N")=0)

Hope this helps.
Andy.

Andy, thanks, I think we are on the right track! I was not sure it
this was possible. I went into the schedule and highlighted the range
that I wanted the formula to check and applied it in the Conditional
Formatting screen. I had the format fill with yellow. The only
problem is that it highlights the column next to (to the right of) a
column without a value of “E” “N” and “D” preset.

I uploaded my experimental schedule to
http://home.pacbell.net/memnok/Schedule.xls

Maybe you could take a look? The range selected for this formula to
check is B4 to AF21.

Thanks,
Clint


--
Memnok[/color]

Formula/function that checks for a set of values.
 

I have misunderstood. I've had a look at your spreadsheet. So what you
want to do is highlight the whole column if there is no E, no D and no
N in the column? If any column has E, D or N it stays as it is?

Yes, that is exactly what I am looking for. I security check to be
sure I have at lease one person schedualed to work on Days, Evenings
and Nights. If there is more than one person that's ok.


Select the whole area (from B4 to AF21) and go to Format/Conditional
Formatting. First of all, delete the existing formats using the Delete
key. Then set Formula Is and try this:
=AND(B$4:B$21"E",B$4:B$21"D",B$4:B$21"N")

Andy, that says there is an error in the formula.

Clint S.


--
Memnok

Formula/function that checks for a set of values.
 

Hi

That's because I left the signs out of it!!

Try this:
=AND(B$4:B$21<"E",B$4:B$21<"D",B$4:B$21<"N")

Sorry!
Andy.



"Memnok" wrote in message
...

I have misunderstood. I've had a look at your spreadsheet. So what you
want to do is highlight the whole column if there is no E, no D and no
N in the column? If any column has E, D or N it stays as it is?

Yes, that is exactly what I am looking for. I security check to be
sure I have at lease one person schedualed to work on Days, Evenings
and Nights. If there is more than one person that's ok.


Select the whole area (from B4 to AF21) and go to Format/Conditional
Formatting. First of all, delete the existing formats using the Delete
key. Then set Formula Is and try this:
=AND(B$4:B$21"E",B$4:B$21"D",B$4:B$21"N")

Andy, that says there is an error in the formula.

Clint S.


--
Memnok

Formula/function that checks for a set of values.
 

Hi

That's because I left the signs out of it!!

Try this:
=AND(B$4:B$21"E",B$4:B$21"D",B$4:B$21"N")

Sorry!
Andy.


Isn't that the same? Ha-ha! :) I copied and pasted them side my side
and they are identical.

You are a trooper, thanks for your continued effort!


--
Memnok

Formula/function that checks for a set of values.
 

Hi

The formula I reposted (and I've got in my thread and sent items also) is
=AND(B$4:B$21<"E",B$4:B$21<"D",B$4:B$21<"N")
which is different but I don't know why that didn't appear to you!!

Try it.

Andy.

"Memnok" wrote in message
...

Hi

That's because I left the signs out of it!!

Try this:
=AND(B$4:B$21"E",B$4:B$21"D",B$4:B$21"N")

Sorry!
Andy.


Isn't that the same? Ha-ha! :) I copied and pasted them side my side
and they are identical.

You are a trooper, thanks for your continued effort!


--
Memnok

Formula/function that checks for a set of values.
 

Hi

The formula I reposted (and I've got in my thread and sent items also)
is
=AND(B$4:B$21"E",B$4:B$21"D",B$4:B$21"N")
which is different but I don't know why that didn't appear to you!!

Try it.

Andy.

Andy, something is amiss because the last three strings have been
identical. I'm not sure why. My guess is the bulletin board interface
is messing with the code in some way? If it would not be a bother,
could you enter the code into the .xls file at '
http://home.pacbell.net/memnok/Schedule.xls '
(http://home.pacbell.net/memnok/Schedule.xls) and email it to me? My
email is memnok AT pacbell DOT net.

You should be getting paid for your hard work!


--
Memnok