Conditional Formatting

Kathy Coulthard

I have a question that someone may be able to help me with. 

If I have a column called Finish Date.

On line 5 - I enter a date that my customer wants project to be finished

On line 4 -  I have a automatic date that is from a predecesor based on finish date so that it populates automatically depending on the tasks completed above it.

- My question is how do I add a conditional format to line 4 (turn it Red and Bold say) based on the auto date being within 5 days of the date that has been entered on line 5? 

I hope that someone can give me a suggestion as it would be great!

 

Thanks  

 

Peter Kirkham

Kathy,

 

I have a similar requirement in my smartsheets. Trick that I use is to add another column as a symbol type (I use a flag). Then in that column use a logic formula for the row that you want to have conditionally formatted e.g. =IF(ABS(Finish4 - Finish5) <= 5, 1, 0) which will set the flag to 1 if the finish date in row 4 is within 5 days of the target date in row 5. Now you can set conditional formatting for that row so that the entire row is set to bold red when the flag column is flagged. Since the flag only gets set by the logic you specified, you've essentially leveraged the full power of formulae to set conditional formatting.

 

Hope this trick works for you,
Peter

Kathy Coulthard

Thank you so much for your feedback and assistance it is much appreicated. I was not really wanting to do this as we have many other columns after the dates and these all link to different reports for different people. Other sheets have been arranged with the same columns so that links auto fill. I will be creating a lot of work in order to add the column and then try to work out all the reports and links that this will now create errors too. However it is a great suggestion and maybe my only option if I want to continue with this requirement!  Again thank you for your time and explaination.  

 

Regards

Kathy

Peter Kirkham

I could be wrong, but I think once you make the links using the autofill for the linking, it makes links to each named column and row. So adding rows and columns to the master sheet should not affect the dependent sheet which has the links. I use many links and I'm adding rows and columns all the time and I've never noticed any unintended behaviours.

 

Best way to do it would be to make 2 dummy sheets, one with a set of data, and another with the links similar to your sheets. Set them up using the same method as you used for the main sheet. Then add the column for the flags and see if it causes any problems with the linked sheets? I do this all the time when I'm testing out new ways of doing things in Smartsheet so I don't accidentally do anything to the main datasets.

 

Anyway, all the best with getting the solution that works best in for your case and glad to know that I've helped a little.

 

Peter

J. Craig Williams

Kathy,

 

The columns can be at the end (all the way to the right) and hidden. 

Your users should never know what hit them, just seeing your reports 'magically' change colors as dates near.

 

Hope this helps.

 

Craig

 

 

Kathy Coulthard

Thank you for your comments, much appreciated and a big help. Will try each suggestion but I think putting at the end of the sheet and hidden sounds great!

 

Thanks

Kathy