Formula/function that checks for a set of values.

M

Memnok

I am not sure if there is a function for this, but I am setting up
schedual using Excel. I want to create a formula/function that look
at a range and checks each column to confirm that there is at least on
value of “E” “N” and “D” preset. There are other values that don
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 on
of the columns is missing one of the values I’d like it to mark tha
column in some way that makes it stand out (color?).

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

Thanks in advance for anyone who can help.

Clin
 
G

Guest

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.
 
M

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.
Click to expand...
[/QUOTE]

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

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

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

Thanks,
Clin
 
G

Guest

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.


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
[/QUOTE]
 
M

Memnok

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

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

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

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

Clint S
 
G

Guest

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.
 
M

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.
Click to expand...


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!
 
G

Guest

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.
 
M

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.
Click to expand...

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!
 
Top