Apr 04 2020 10:07 AM
Good Morning, I have a formula running in cell K11 based on other cells (J11 and current date) that provides a number, then conditional formatting to highlight the cell based on the value.
Next I wish to use information from a 3rd cell (M11) to turn off the formula and formatting in K11, Once anything is present it M11, K11 goes blank with no highlight.
Likely this is a new formatting rule --> Use a formula to determine which cells to format, that's as for as I get. Can anyone help with this? Thanks....
Apr 04 2020 10:28 AM
You may check if it is something in M11 or not by checking length of the value of this cell.
To return empty string to K11 you shall update your current formula for K11 with above condition.
Not to highlight K11 in such case you may add another conditional formatting rule on first position which check M11 length and apply Fill No color format K11.
Apr 04 2020 11:30 AM
1--take my formula that I am generating the value for K11 (=Days(J11, J3(the cell containing the current date))---then add to this formula to have it also check M11 so that it checks for anything there. if anything then the formula yields no valve and therefore no formatting kicks in. If any thing is there in M11 then go ahead and do the calculation and my formatting happens?? Can you provide any sample language? I don't know how to right it. Thanks....
Apr 04 2020 12:01 PM
Perhaps we may generate concrete formula if you provide small sample file.
Apr 04 2020 02:06 PM
@Sergei Baklan So from the attached file you can see I am tracking the progress for receiving a submittal. I have a date that I need to receive the document. I am comparing today's date to the need by date, and displaying the number of days remaining. I am them color coding the cell as the days grow shorter. Once I log a date into M11 announcing it has been received I need the tracking occurring in K11 to stop. That cell needs to display no number and return to white background.
So is that maybe having the formula in K11 first check for a value in M11 first, if valve-->stop, if no value proceed to run=DAYS(J11-J3). Conditional formatting I could do from there, no valve no formatting, whatever number --> formatting.
Thanks....
Apr 05 2020 02:31 AM
SolutionI updating the formulas a bit starting from row 12 and till end of the range.
In I12
=IF($G12="","",$G12-($H12+$I12)*7)
it shows empty cells in column J if no req date (col G)
In K12
=IF(OR($M12<>"",$G12=""),"",J12-TODAY())
It returns empty cell if no req date (column G) or order is closed (date in column M)
Conditional formatting:
All rules are expanded on entire range (not to generate bunch of new rules applying cell by cell with Format Painter) and one more rule on the top with "no color" if the cell is blank
Please check in attached file.
Apr 05 2020 08:06 AM
@Sergei Baklan Thanks much for this. I am studying the formula trying to see what you have done. It is doing what I wanted, trying to learn something new. Again Thank you.....
Apr 05 2020 08:29 AM
@GeneP , you are welcome. Please ask if something is unclear.
Apr 05 2020 02:31 AM
SolutionI updating the formulas a bit starting from row 12 and till end of the range.
In I12
=IF($G12="","",$G12-($H12+$I12)*7)
it shows empty cells in column J if no req date (col G)
In K12
=IF(OR($M12<>"",$G12=""),"",J12-TODAY())
It returns empty cell if no req date (column G) or order is closed (date in column M)
Conditional formatting:
All rules are expanded on entire range (not to generate bunch of new rules applying cell by cell with Format Painter) and one more rule on the top with "no color" if the cell is blank
Please check in attached file.