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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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

> 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

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" <[email protected]> wrote in message
news:[email protected]...
>
>> 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]

> 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

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" <[email protected]> wrote in message
news:[email protected]...
>
>> 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

> 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

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" <[email protected]> wrote in message
news:[email protected]...
>
>> 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

> 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