ISFORMULA not working with Conditional Format

[dparteka]

I feel like a deer staring at headlights, I think I need a different set of eyes to look at this, what am I not seeing here? I'm using =ISFORMULA(D5) in a conditional format which should result in the font in the entire row to go red as long as D5 has a formula and then go Auto(black) once that formula is gone. Thanks for looking and any help will be greatly appreciated.

 

[David Zissman]

It works for me once you select the status in the column C. When I select OPEN, C and D turn red, when I delete the formula in D, C turns black. What exactly isn't working for you?

 

[GraH - Guido]

Like this, respecting the order of the rules and flagging "Stop if True"?


Now, as I see it, the if statement stays in D5, no matter if C5 is closed or not. So why don't you change the conditional formatting to 2 opposite rules like
=$C5="CLOSE" (grey)
=$C5<>="CLOSE" (red)
Just a thought...

 

[Peter Bartholomew]

For me changing
=ISFORMULA(D5)
to
=ISFORMULA($D5)
worked.

Then I clicked 'Close'. I wonder whether I sent an email ?

 

[dparteka]

David… what you describe is exactly what I’m shooting for. With the formula in column-D, when I select OPEN I get black font… should be red like you are seeing but it’s not.

GraH… altering the condition as you have it changed nothing for me, still no red. If I use the opposing formulas I get red but once the formula in column-D is removed the font doesn’t change to Auto(black). I tried adding an additional condition with your opposing formulas using =NOT(ISFORMULA(D5)), that also does not work.

Peter… adding the $ sign did not work here for me.

To all… thanks for your help, I don’t get it, I’ve tried Office 2010 and Office 365… =ISFORMULA(D5) should work just like it does for David, why not for me?

 

[Peter Bartholomew]

dparteka

I have repeated the process and it still works for me.
I think the point is that your conditional formatting formula is defined as if it were a formula in the top left cell C5. If the condition is relative then this means that every cell looks one to the right to determine whether it should fire the rule or not. If you want the format to be based upon D5 across the entire row then the rule must be based upon $D5.

Because your second condition of 'CLOSED' also has a formula in D5, it think it may be more reliable if it is promoted to the first test and the 'Stop if True' box is checked, as shown by Guido

 

[GraH - Guido]

once the formula in column-D is removed the font doesn’t change to Auto(black).

How are you removing the formula and why do you do that since you have an IF statement?
I have done this in 2010, 216, 365 versions. All work with the same rules.
As the proof of the pudding is in the eating....

 

[Peter Bartholomew]

I believe Guido has the definitive solution to this problem.

 

[dparteka]

VBA code converts the cell from formula to value. This is done to lock in the date... keeping the formula in tack will result in it continually updating to NOW(). Something I just discovered, if I open GraH-Guido's workbook the conditional formatting as he has it does work on my home Office 365 PC but not my work OfficeProPlus 2010 PC. Searching the available functions on the 2010 PC shows that ISFORMULA is not available (see the attached image), that must be the root of the problem which means I'll have to play around a bit and come up with another way.

 

[Peter Bartholomew]

That would make a difference !
Do you need to discriminate between the formula and the value that replaces it? If not, ISNUMBER might do; conversely you could repeat the test against NOW to see whether the clock has stopped. Once the task is closed the other CF could be set to take precedence.

 

[GraH - Guido]

Sorry, my bad I kind of forget I've just moved to Office 365 at work. No longer using 2010 at all.