Conditional Format Dilemma

[dparteka]

This has now given me a headache, can someone please tell me what I'm doing wrong here. In H42 I have a conditional format with this formula in it...

OR(ISNUMBER(SEARCH(F42="Profile",F42="Angle Deg",F42="Angu",F42="Runout",F42="Concent",F42="Cylind",F42="Flat",F42="Parall",F42="Perpen",F42="Posi",F42="Round",F42="Straigh",F42="Surf",F42="Symm")))

The formula is accepted without errors but is not changing the format. The text that is being searched for is just a portion of the actual text string in F42, I had to do that to shorten the formula to stay within the maximum character limitations.

 

[PaulF]

Put your formula any cell (Not CF area), does it return a TRUE or a FALSE ??

 

[dparteka]

Hi Paul and thanks for the quick reponse... putting the formula in any cell returns a message that says the formula contains an error.

 

[PaulF]

Upload the file please...

 

[wudixin96]

"SEARCH" function only has three parameters

 

[wudixin96]

=ISNUMBER(SEARCH(","&F42&",",",Profile,Angle Deg,Angu,Runout,Concent,Cylind,Flat,Parall,Perpen,Posi,Round,Straigh,Surf,Symm,"))

 

[dparteka]

Paul... file is upload, looks like the SEARCH function might be the problem

 

[NARAYANK991]

Hi ,

See if this works :

=OR(ISNUMBER(SEARCH({"Profile","Angle Deg","Angu","Runout","Concent","Cylind","Flat","Parall","Perpen","Posi","Round","Straigh","Surf","Symm"},F42)))

Narayan

 

[dparteka]

Narayan... returns the message "You may not use union, intersection or array constants for conditional formatting criteria"

 

[dparteka]

Wudixin96... it's not working here

 

[dparteka]

Narayan... if I remove the equal sign "=" I do not get the error but it does not change the formatting

 

[PaulF]

=SUM(COUNTIF($F42,"*" & $O$42:$AB$42 & "*")) = 1

 

[NARAYANK991]

Hi ,

Sorry , I should have known.

What you can do is define a named range called , say SearchCriteria , and in its RefersTo box , enter the following :

={"Profile","Angle Deg","Angu","Runout","Concent","Cylind","Flat","Parall","Perpen","Posi","Round","Straigh","Surf","Symm"}

Now , for the CF , use the formula :

=OR(ISNUMBER(SEARCH(SearchCriteria,F42)))

Narayan

 

[Nebu]

Hi:

Please find the attached using helper columns.

Thanks

 

[dparteka]

PaulF... that's impressive, problem solved, my headache is finally going away... thank-you so much

 

[PaulF]

PaulF... that's impressive, problem solved, my headache is finally going away... thank-you so much

Thank you Sir

Respectfully,
PaulF

 

[dparteka]

Narayan... thank you, works great, I greatly appreciate your help

 

[dparteka]

Nebu... thank you, I appreciate the help