Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 23rd 08, 03:17 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 2
Default Conditional Formatting - Multiple Conditions with OR()

I have been trying to make a conditional format criteria using the "Formula
is", but has not been working quite right.

The formula I have is this:
=NOT(ISERR(OR(SEARCH($A3,"Personnel"),SEARCH($A3," Ships"))))
and it would effect the cells:
=$AA$3:$AA$6027

What I am trying to do have any cell that is in AA to become Black if the
cell in A does not contain "Ships" or "Personnel". The problem that is
occurring is that with the above Formula none of the cells become black, if I
take away the NOT() in the formula, ALL cells become black. If I take ISERR()
instead of NOT() all cells are not black.

Another similarly problem I have with trying to use with the OR() and trying
to get cells to turn black or not is:
=ISERR(OR(NOT(SEARCH($A3,"Personnel"),SEARCH($A3," Ships"),SEARCH($A3,"Dilemma"),SEARCH($A3,"Event")) ,SEARCH($I3,"1st"))))
and would effect the cells:
$W$3:$W$6027

In this one, any cell in I with "1st" in it would make W black, or if it
does not contain "Ships", "Personnel", "Event", "Dilemma" it would turn
black. However this one returns various errors. Either stating equivalent to
"Too Complicated" or "Check your formula for errors".

If anyone could help me with trying to get these formulas to work out, I
would appreciate it.

Also something that confounds me at the moment while using the NOT()
statement in a formula is that it is stated that NOT(FALSE)=TRUE,
NOT(TRUE)=FALSE.
Hence, if say a cell in I# = 2nd and I have a Conditional Formula of
=SEARCH($I#,"2nd") to turn J# black, it should equal TRUE. But apparently it
is received as FALSE? And I have to add NOT() to make the condition TRUE?

  #2   Report Post  
Old February 23rd 08, 03:43 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Conditional Formatting - Multiple Conditions with OR()

"EEB3" wrote:
.. What I am trying to do have any cell that is in AA to become Black
if the cell in A does not contain "Ships" or "Personnel".


One way to get it going is to create a defined range for the 2 words,
then use it in a CF formula via SUMPRODUCT, like this:

Click Insert Name Define
Put under "Names in workbook:": MyList
Put in the "Refers to:" box: ={"Ships";"Personnel"}
Click OK

Then select AA3:AA6027 (AA3 active)
apply CF using Formula is:
=SUMPRODUCT(--ISNUMBER(SEARCH(MyList,A3)))=0
Format Fill color black OK out

Adapt/extend to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #3   Report Post  
Old February 23rd 08, 03:49 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Conditional Formatting - Multiple Conditions with OR()

any cell that is in AA to become Black if the cell
in A does not contain "Ships" or "Personnel".


Is "Ships" or "Personnel" part of a larger string?

What if the A3 is empty? What do you want to become Black, the text or the
fill color?

Try something like this:

=NOT(OR(A3="personnel",A3="ships"))


--
Biff
Microsoft Excel MVP


"EEB3" wrote in message
...
I have been trying to make a conditional format criteria using the "Formula
is", but has not been working quite right.

The formula I have is this:
=NOT(ISERR(OR(SEARCH($A3,"Personnel"),SEARCH($A3," Ships"))))
and it would effect the cells:
=$AA$3:$AA$6027

What I am trying to do have any cell that is in AA to become Black if the
cell in A does not contain "Ships" or "Personnel". The problem that is
occurring is that with the above Formula none of the cells become black,
if I
take away the NOT() in the formula, ALL cells become black. If I take
ISERR()
instead of NOT() all cells are not black.

Another similarly problem I have with trying to use with the OR() and
trying
to get cells to turn black or not is:
=ISERR(OR(NOT(SEARCH($A3,"Personnel"),SEARCH($A3," Ships"),SEARCH($A3,"Dilemma"),SEARCH($A3,"Event")) ,SEARCH($I3,"1st"))))
and would effect the cells:
$W$3:$W$6027

In this one, any cell in I with "1st" in it would make W black, or if it
does not contain "Ships", "Personnel", "Event", "Dilemma" it would turn
black. However this one returns various errors. Either stating equivalent
to
"Too Complicated" or "Check your formula for errors".

If anyone could help me with trying to get these formulas to work out, I
would appreciate it.

Also something that confounds me at the moment while using the NOT()
statement in a formula is that it is stated that NOT(FALSE)=TRUE,
NOT(TRUE)=FALSE.
Hence, if say a cell in I# = 2nd and I have a Conditional Formula of
=SEARCH($I#,"2nd") to turn J# black, it should equal TRUE. But apparently
it
is received as FALSE? And I have to add NOT() to make the condition TRUE?



  #4   Report Post  
Old February 23rd 08, 04:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 2
Default Conditional Formatting - Multiple Conditions with OR()

Thanks, this helps a lot, Things work now, (as well as cleans up a lot of the
messy formulas I had there.)

And no, "Ships" or "Personnel" would be the only text within that cell
(along with other single worded cells)

And for items that were listed within column A, if it was blank, it would
have no "row" of information after it.

I was having the background of the cell being simply filled black, since
other columns wouldn't require certain cells to be filled based on what
column A had.


"T. Valko" wrote:

any cell that is in AA to become Black if the cell
in A does not contain "Ships" or "Personnel".


Is "Ships" or "Personnel" part of a larger string?

What if the A3 is empty? What do you want to become Black, the text or the
fill color?

Try something like this:

=NOT(OR(A3="personnel",A3="ships"))


--
Biff
Microsoft Excel MVP


"EEB3" wrote in message
...
I have been trying to make a conditional format criteria using the "Formula
is", but has not been working quite right.

The formula I have is this:
=NOT(ISERR(OR(SEARCH($A3,"Personnel"),SEARCH($A3," Ships"))))
and it would effect the cells:
=$AA$3:$AA$6027

  #5   Report Post  
Old February 23rd 08, 06:07 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Conditional Formatting - Multiple Conditions with OR()

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"EEB3" wrote in message
...
Thanks, this helps a lot, Things work now, (as well as cleans up a lot of
the
messy formulas I had there.)

And no, "Ships" or "Personnel" would be the only text within that cell
(along with other single worded cells)

And for items that were listed within column A, if it was blank, it would
have no "row" of information after it.

I was having the background of the cell being simply filled black, since
other columns wouldn't require certain cells to be filled based on what
column A had.


"T. Valko" wrote:

any cell that is in AA to become Black if the cell
in A does not contain "Ships" or "Personnel".


Is "Ships" or "Personnel" part of a larger string?

What if the A3 is empty? What do you want to become Black, the text or
the
fill color?

Try something like this:

=NOT(OR(A3="personnel",A3="ships"))


--
Biff
Microsoft Excel MVP


"EEB3" wrote in message
...
I have been trying to make a conditional format criteria using the
"Formula
is", but has not been working quite right.

The formula I have is this:
=NOT(ISERR(OR(SEARCH($A3,"Personnel"),SEARCH($A3," Ships"))))
and it would effect the cells:
=$AA$3:$AA$6027





  #6   Report Post  
Old February 24th 08, 12:10 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Conditional Formatting - Multiple Conditions with OR()

Hold the preceding suggestion if you are looking at the more complex CF
scenario where the 2 words could be part of a text-string in the cells. I had
presumed that was the case, from your original posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---



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
conditional formatting for multiple sets of conditions steve Excel Discussion (Misc queries) 9 August 15th 07 07:00 PM
Conditional Formatting - 2 conditions Otto Moehrbach Excel Discussion (Misc queries) 3 March 27th 07 08:07 PM
Multiple conditions for conditional formatting plf100 Excel Worksheet Functions 4 September 27th 06 11:00 AM
Multiple conditions in Conditional Formatting guilbj2 Excel Discussion (Misc queries) 0 June 28th 06 09:09 PM
Banding with Conditional Formatting with Multiple Conditions Geremia Doan Excel Worksheet Functions 7 February 2nd 05 03:14 PM


All times are GMT +1. The time now is 02:51 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017