1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ISFORMULA not working with Conditional Format

Discussion in 'Ask an Excel Question' started by dparteka, Mar 12, 2019.

  1. dparteka

    dparteka Member

    Messages:
    195
    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.

    Attached Files:

  2. David Zissman

    David Zissman New Member

    Messages:
    8
    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?
    dparteka likes this.
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    984
    Like this, respecting the order of the rules and flagging "Stop if True"?
    upload_2019-3-13_8-17-58.png

    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...
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

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

    Then I clicked 'Close'. I wonder whether I sent an email :eek:?
  5. dparteka

    dparteka Member

    Messages:
    195
    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?
  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    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
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    984
    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....
    upload_2019-3-13_19-42-27.png

    Attached Files:

    Thomas Kuriakose and dparteka like this.
  8. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    I believe Guido has the definitive solution to this problem.
  9. dparteka

    dparteka Member

    Messages:
    195
    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.

    Attached Files:

  10. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    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.
    dparteka likes this.
  11. GraH - Guido

    GraH - Guido Well-Known Member

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

Share This Page