クラシックファイルタイプは、2021年1月をもって使用不可となりました。クラシックファイルを移行するか、PDFをダウンロードすることができます。 詳細を見る

Conditional Formatting - Due Dates

0

コメント

6件のコメント

  • Matthew H Fink

    I think you just need to reset the range where your conditional formatting applies to be the whole column. This is the box directly above the conditions where it currently says "M3", or where it says "M1" in your previous example of Upcoming / Past-Due formatting. 

    To apply to the whole column you can change the range to "M3:M", which applies it to all cells below M3. Or you can apply to a specific range using "M3:M#" where # is the row number where the conditional formatting should stop. 

    0
  • Katherine Petruszak

    Thank you for the response, Matthew. I am aware I altered the range, but fixing it to apply to the entire column will not solve my issue. My question is whether the formulas used within conditional formatting can be dynamic because if I apply what I currently have to the full column, it will only take into account the value in cell L3 and apply it across all of the other controls which is not what I want.

    0
  • Matthew H Fink

    The way you've written the formula should work. I've done the same thing to apply conditional formatting to the whole column, i.e., cell reference is $CR, where C is column letter, R is row number. e.g, 

    0
  • Katherine Petruszak

    Thank you, Matthew. I've tried playing around with the formulas, but seem to be stuck and would appreciate your help. Do you know why the conditional formatting isn't working as i've designed it? The green and blue colors aren't being applied as i'd like them to be and i'm not sure if there's an issue with my formulas. It seems to all be based on cell G3, which isn't what I'd like to have happen. If there's an easier way to do any of this, please let me know. Otherwise, i'll come up with an alternative.

    0
  • Matthew H Fink

    You don't need the =if(CONDITION,true,false) statements, you can simply use CONDITION if it evaluates true/false - e.g., anywhere your condition is an OR() or AND() you can use that on its own, you can also nest them, e.g., NOT(OR(a,b)) = anything but a or b

    The Otherwise statement seems to act like IF false, then continue with rest of conditional formatting... but it's not always clear what's happening. Recommend not combining AND and OTHERWISE blocks - if you need to apply multiple conditions within one block write a single formula to do it, e.g., when formula is true fx=and($L3<$L$1,or($G3="Not Started",$G3=".....)) >> format red, then OTHERWISE, then next block,...

    But I don't know why you're having the problem above with green formatting where it shouldn't be. It may be your OR statement for the green condition is too complicated. Try stripping the IF(OR(),T,F) statement down to force it to evaluate as false (e.g., just write FALSE where it says formula is true to force it) and see if it skips over to apply the next condition. If you can get it to work then, build your formula back up to where it is now step by step and see what breaks it. Should help pinpoint the problem anyway. 

    Could also try switching to make the green condition last in the chain so the Not Started ones come first...

    0
  • Katherine Petruszak

    I tried playing around with it more and unfortunately, it still wasn't working. I created an alternative that my team can use. Thank you for your time though in trying to help me troubleshoot this.

    0

サインインしてコメントを残してください。