Conditional formatting for columns, informed by row-level input.

Nic G

Hi all

 

I am building a template for which the top row has date columns set up so that all date columns change color based on the date (today/tomorrow/yesterday).  The first 2 date columns are, Start Date, End Date, and are not the concern. The remaining date columns, Milestone 1, Milestone 2, etc, are dates which are pulled from rows using "=[Start Date]XX".  

 

The rows also have rules which change color of the row, based on date (today/tomorrow/yesterday), BUT also have a rule applied so that if a checkbox is checked, the entire row gets a strikethrough, and the rules which change the color of the row based on the date, do not apply.

 

I can not figure a way to change the color/formatting of the Milestone columns, based on their associated row-level input.  I would like to have the Milestone columns change color to white/blank and/or have a strikethrough applied, when the associate row checkbox is checked. Could anyone help? 

 

Thanks in advnace!

Christian Wells

Hello Nic,

 

When you are selecting the value on a checkbox field there is a toggle above the formula area to select the possible values to set in the criteria

 

When you set up a Conditional Formatting the last selection to change is to select the Entire Row or to select the column/s to apply the change to..

Also you can use the drop down to add another criteria to the formatting .

 

This could give you an Ability to format a single cell (which is therfore the column) based on one or more criteria.  i.e. a checkbox (Checked) and a date value (In the past, Today, in the future).

 

Hope this helps

 

 

Nic G

Hi Christian, 

 

Thanks for your response!  Though, I am not understanding the method you suggested.

 

Below, I have included a screenshot of my template (please right-click and open in new tab for larger view).  As you can see, there are many rows extending downward, and many extending to the right side.  With the exception of Start Date, and End Date... all other (Milestone) date columns gather the date shown by using the =[Start Date]xx formula.  I believe I would need a formula, which would I would pair with the [Start Date] formula, that would allow each specific Milestone column to strikethrough, and/or change color, based on a input of a target checkbox.  Each Milestone column needs to be able to target a particular checkbox.  

 

Example of function needed:  If the checkbox for the row of IR 1 is checked, the first cell (or entire column) for IR 1 changes color to white, and/or applies a strikethrough to the date.  If the checkbox for the row of CR 1 is checked, the first cell of the column for CR 1 is affected, and so on...

 

I tried searching for hours, but can not write this formula.  Any help would be greatly appreciated!

 

Bobby Andres

I dont think you need a formula for this - just conditional formatting.

 

Set up a rule that says, if the checkbox is checked, apply this formatting (strikethrough) to the Start Date column.

 

Set up a second rule that says if the checkbox is checked, apply this formatting (white background) to the entire row.

Nic G

Bobby, the issue is that Conditional Formatting does not allow a rule to target a specific checkbox.  Only the entire column of checkboxes can be affected by conditional formatting. Unless there is a setting somewhere I am missing?

 

I need each particular checkbox of each row, to affect a different column.

Bobby Andres

Right, a rule will be applied for the entire sheet. 

 

How about you add an AND condition to your rule so it says something like this:

 

If the checkbox is checked and Task Name contains "IR", apply this format. 

 

To add an AND, right click on the rule and select Add Condition (AND)

Nic G

Aaaaah sounds like a great solve, but I tried it, and sadly, I does not affect Row 1.  Possibly because all other rows are children of Row 1?

 

I think a formula would work, as I am already using a formula to gather the dates from the rows, but I can not write the formula to monitor a particular checkbox and apply a style if checked.

Christian Wells

Hi Nic,  (Post 1)

I have tried to replicate your sheet (smaller) and have added the conditional formatting to format a calculated date based on the complete checkbox.

 

Christian Wells

Hello again Nic, (Post 2)

Adding a 'Milestone 2 Complete' checkbox I now have another conditional format, Same as prev post, which formats the Milestone 2 based on its Completion.

Again screen and Formatting rules

 

In this case each column will need its own formatting rule

Bobby Andres

Nic, it should work regardless of the row (assuming I understand what you are asking). I just tested this in my sheet with this rule:

 

 

This will apply the strikethrough if the checkbox is checked and Task Name contains IT.

 

One thing to check, make sure you dont have a rule that is taking priority. The higher the rule is on the list, the higher the rules priority. 

Christian Wells

Hi Nic,   (Post3) Last for now,

 

I have Changed the First Conditional Formatting Rule to format just the MIlestone 1 date.

I have now added a Format and formula to the Complete column, to both;

      Calculate the complete flag based on the MileStone 1 & 2 Being Complete

      Format the cell dark grey.

 

I hope these examples help you somewhat with the conditional formatting.

Which is what I see as the answer to what you have described.

Cheers

 

Christian

 

 

Nic G

First and foremost, I really appreciate everyone jumping in to help here, thanks so much!

 

But to further clarify, the top most row, of columns IR 1, CR 1, IR 2, CR 2, etc, need to be affected by input of the checkbox of their corresponding indented row.  Attaching my screen shot again, for reference.

 

Adding a conditional strikethrough on the cell of the red-colored rows, is already built into my template, I am having trouble getting individual cells of the top, white-colored row to strikethrough and/or change color.

 

Christian Wells

OK Nic, I am trying to understand what you are after..

You have described that the "If the checkbox for the row of IR 1 is checked, the first cell (or entire column) for IR 1 changes color to white, and/or applies a strikethrough to the date."  This is a conditional formatting for the entire column by how I read it.

 

Next you state "I think a formula would work, as I am already using a formula to gather the dates from the rows, but I can not write the formula to monitor a particular checkbox and apply a style if checked."   A Formula can monitor a specific cell/checkbox by the use of $[field name]$n then you can format a cell (That or a different)  based on that calculation's result.

 

No to tackle what I think you are after, which is;

Change the format of the first row of data only, milestone by milestone, based upon the completed values in the children.... 

I have used hard formula links to set the checkboxes and the previous formatting still applies (Grey & Strike)  See yellow shaded cells.

Christian Wells

Sorry, I t would help if I showed the row and column references....

D'oh!

Nic G

Christian, we're on the same page now!  But... I want to keep only the first column of checkboxes, as my sheet already has many columns, I want to avoid introducing new columns.

 

I have tried writing an AND IF formula to have a specific cell of row 1 strikethrough and/or change color, upon specific row of checkbox input, but I am very new to Smartsheet, so I am having a hard time with that.

Christian Wells

Unfortunately what you are after is conditional formatting based on a formula, like excel.  Im sure this is not available in smartsheet.

 

The workaround.....

I think you will have to add the column and then add the Check box formula and then hide the column.  Then format the Date column based on the value of the checkbox.

 

As you are aware the conditional formatting will only work on the same row.  so this will give you the value the base your format on in the same row.

 

Hope that helps.