Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Memnok
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Memnok
 
Posts: n/a
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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]


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Memnok
 
Posts: n/a
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Memnok
 
Posts: n/a
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Memnok
 
Posts: n/a
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
hiding zero values in charts fascal Charts and Charting in Excel 4 December 19th 05 03:17 PM
Keeping a cell value constant trhoughout a list of values borikua05 Excel Worksheet Functions 2 December 3rd 05 04:03 PM
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row Sam via OfficeKB.com Excel Worksheet Functions 6 November 29th 05 04:27 PM
I Need a formula to evaluate a cell with + or - values Bob in Oklahoma Excel Worksheet Functions 6 October 31st 05 03:41 PM
#N/A Values : Returned by Formulas vs Entered Manually monir Charts and Charting in Excel 8 July 7th 05 01:16 AM


All times are GMT +1. The time now is 11:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"