Conditional Formatting based on the status of Predecessors / Dependencies

Glen Sykes

Hi,

I'm looking to use Smartsheet to track the overall status of our software development team's progress. I'd like to be able to quickly identify any tasks in a list that are blocking other tasks from being completed.

For example, a task on say row 11 is dependent on row 2. Row 11 has a status of 'blocked' because of some outstanding work required to complete row 2. I'd like to highlight row 2 in a different colour in order to draw attention to the fact that it is blocking other work.

How might I achieve this?

Thanks! Glen

Ramzi K

@Glen Sykes

Here's one way to do it:

1. Add a helper column n with the value 1 in each cell
2. Add a helper column Row with the formula: =SUM(n@row:n$1) and copy it down to all cells this is your row number
3. Create a Checkbox column called Blocker and put this formula in it: =IF(COUNTIFS(Predecessors:Predecessors, Row@row, Status:Status, "Blocked") > 0, true)
4. Lastly do conditional formatting on the Blocker column.

I hope this helps.

Cheers,

Ramzi

Ramzi K

@Glen Sykes

Have you tried conditional formatting?

Cheers,

Ramzi

Glen Sykes

Hi Ramzi,

Yes, this is a question as to whether conditional formatting can achieve what I'm trying to do. With the standard rules you can choose from, it appears that you cannot (i.e. formatting a row based on values in a different row)

Cheers, Glen

Ramzi K

@Glen Sykes

Here's one way to do it:

1. Add a helper column n with the value 1 in each cell
2. Add a helper column Row with the formula: =SUM(n@row:n$1) and copy it down to all cells this is your row number
3. Create a Checkbox column called Blocker and put this formula in it: =IF(COUNTIFS(Predecessors:Predecessors, Row@row, Status:Status, "Blocked") > 0, true)
4. Lastly do conditional formatting on the Blocker column.

I hope this helps.

Cheers,

Ramzi

Glen Sykes

Neat! Didn't realise you could embed formulae in checkboxes.

Thanks very much, I'll try this shortly :)

Ramzi K

@Glen Sykes

👍

Cheers,

Ramzi