conditional format help

Topic

New Reply

Formula is:
=IF(B2=””,””,IF(ISERROR(DATEDIF(B2,C2,”d”)),”no end
date”,DATEDIF(B2,C2,”d”)))

I want to Conditional format so when:
“D” has a # return it is yellow
DATEDIF(B2,C2,”d”
“D” returns “” it is white
IF(B6=””,””
“D” returns “no end date” it is green
IF(ISERROR(DATEDIF(B2,C2,”d”)),”no end date”

My CF is:
Cell value >0 = yellow
Cell value =”” = no color
Cell value =”string” = green

Results = All cells are yellow.

CF works correctly if I rearrange the order to:

Cell value =”” = no color
Cell value =”string” = green
Cell value >0 = yellow

I assume that the order determines priority, but why would the >0
affect, or interfere with, the strings/null? Is a string or null
string 0? Does it have any numeric value?

How can I change “Cell value >0” so I can place it first and have
this work. Stated another way, what can I use so it would not
interfere with the other two?

Question #2 – Column E
I have a formula which returns a string “no $C$5” if B5 has a date
but C5 is empty. It changes as the rows change, that is the next row
would say “no $C$6″ if B6 has a date but C6 does not.

=IF(B43=””,””,IF(ISERROR(DATEDIF(B43,B44,”d”)),”no”&” “&CELL(“address”,B44),DATEDIF(B43,B44,”d”)))

How can I CF this return without having to do each row separately
using the specific string return [no $C$5, no $C$6, etc] or without
using “>than $C$1” ?
I want to know other ways to do it maybe using a variable or other means to change the number(x) [$C$x] with corresponding change of the row.

See attached sample.

Reply | Quote

Replies

Will this work for Column D? It uses a CF test of isnumber for first condition.
If yes, just use the Format Painter for any additional rows of data in column D.

Reply | Quote

This might work for Col D and Col E

Let me know

PS: Copy and Paste should work in both columns.

Reply | Quote

tfspry,
Thanks.
First suggestion works great, second one works but will color all cells with no number. I want only the cell with the string return colored.
Can a variable or a wildcard be used in CF? Can it be done in this case?

Reply | Quote

Hi skipro- Will the attached do what is needed?

HTH

Reply | Quote

Yes, thanks.
I am still looking for a more direct solution if anyone knows how. I am curious if a variable, wildcard or other method can be used in CF.

Reply | Quote

I am still looking for a more direct solution if anyone knows how.

I don’t understand what you mean by “more direct solution”. The solutions provided seemed direct to me. What do you consider not direct about them?

I am curious if a variable, wildcard or other method can be used in CF.

Variables can be used, wildcards can be used, and there are many other methods. What you asked for did not require them. Do you have a simple example that you think requires a wildcard or a variable?

Steve

Reply | Quote

SDCKPR,
Thanks for responding.
Answer to your 2nd question:
Although my sample does not “require” the use of variables, wildcards or a different approach, I would be interested to see how they can be used for future reference. Can you, or someone else, show me how a variable, a wildcard and other approaches would work either in my sample, in general or lead me to references.

Answer to your 1st question:
I am only referring to Column “E” in my sample.
If, as you state, variables, etc can be used, I assume this would be more direct, efficient and customizable.

I see the offered solutions as functional for this sample but indirect and less efficient. A more direct solution would state:

1) If numbers exist, do “A”
2) If any variable of string $C$x[x as number] do “B”

and do so with the least rules, be specific to this string needs and be available as a standard which would be more universal and useable if the need is reasonably modified.
Rule 1= {Isnumber} seems to be an effective approach.
Rule 3= {Cell Value Is Not equal to 0} works but is an indirect approach to the
string issue, an elimination approach and general.
Rule 2= {Formula is =Bx=””} again works, but is only needed to override Rule 3 because of Rule 3’s lack of specificity.

To me, this is less direct or definitive and less efficient.
I recognize I did not state this originally and my choice of words describing
my goals was not accurately descriptive. I am not suggesting the replies are
inadequate for my sample, but rather I am seeking another and, as I interpret it, [here I go again], more direct approach.

I am seeking
A) Using only 2 rules which would be simpler and saving the 3rd, for future
use, avoiding Excel’s limitation.
B) [referenceing “2)” above]
Only the specified string [$C$x] or its cousins[modified] would {do “B”}.
The solutions offered are not string [or it’s variations] specific. If future
needs required another string to also be present, the solution would not
perform as needed.

That is what I meant as more direct or definitive. I am seeking an approach I
can use for future unknown but similar needs which through the use of
variables, wildcards or other methods/functions would expand the possibilities while being as direct/efficient as possible.

I use this forum not only to solve a specific issue, but to also learn. That
is why I asked the questions in the original post and why I am pursuing this
further and asked the question in the April 2 reply.
I hope I have clearly stated my objectives.

Reply | Quote

………..
I see the offered solutions as functional for this sample but indirect and less efficient. A more direct solution would state:

1) If numbers exist, do “A”
2) If any variable of string $C$x[x as number] do “B”
……….

I am seeking
A) Using only 2 rules which would be simpler and saving the 3rd, for future
use, avoiding Excel’s limitation.

The attached will work with just 2 CF’s.

Reply | Quote

There are several optional Conditional Formats that can be used.

Using file in Post #9 for example: Condition #2 could be …
Formula is =left(E6,1)>=”a”

Just need some examples of variations you would like to use.

Reply | Quote

Tfspry,
Thanks. Both work fine and taught me another approach to use. I appreciate the 2 variations as it shows me how the concept can be manipulated.
The first formula is more specific than the second one and has less chance of overlap with other string returns, which is my goal. I modified it to be even more specific and essentially eliminate the chance of overlap with another string return with:
=LEFT(E5,6)=”no $C$”
This is a direct approach I have been seeking. Although it does not actually use a variable or wildcard, it does conceptually. To me, its specificity makes it more elequent.

Can characters be skipped?
Ex: Can you use character 3-5, skipping characters 1&2?
Can you suggest any other approaches? Even suggestions to other functions would be helpful.

Reply | Quote

Can characters be skipped?
Ex: Can you use character 3-5, skipping characters 1&2?
Can you suggest any other approaches? Even suggestions to other functions would be helpful.

To get characters 3-5, you could use =MID(E5,3,3)

Here is a Microsoft site for some Excel 2003 Functions…..
List of Excel functions (by category)

Among others, take a look at the Text functions.

Reply | Quote

Tfspry,
Thanks.

Reply | Quote

Tfspry,
Thanks.

skipro – Your welcome. Glad we could help.

There are so many possible functions/combinations that can be used in Conditional Format. Feel free to ask for more solutions for your ideas.

Here is another Conditional Format (For condition #2) to test for characters in position 4-6 ($C$ ) in Col E……
=MID(E5,4,3)=”$C$”

Reply | Quote

Another way to use wildcards with cond formatting is to use countif, for example:
=COUNTIF(e5,”=*He*lo*”)>0

will be true for cells with “He” in it followed by “lo” elsewhere in the cell…

Steve

Reply | Quote

Sdckapr,
Thank you for showing an example using a wildcard.
Why the equal sign [=] in “=*He*lo*” . Seems to work without it.

Tfspry,
Thanks for the =MID example.

All of these will be helpful in general, not just CF.

Reply | Quote

Why the equal sign [=] in “=*He*lo*”

I suppose habit and I didn’t think about not using it…

In other examples you can also use >, < etc

Steve

Reply | Quote

sdckapr,

Thanks.

Reply | Quote