Conditional Formatting based on the status of Predecessors / Dependencies

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

Find more posts tagged with

Accepted answers

Ramzi K

@Glen Sykes

Here's one way to do it:

Add a helper column n with the value 1 in each cell
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
Create a Checkbox column called Blocker and put this formula in it: =IF(COUNTIFS(Predecessors:Predecessors, Row@row, Status:Status, "Blocked") > 0, true)
Lastly do conditional formatting on the Blocker column.

I hope this helps.

Cheers,

Ramzi

All comments

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:

Add a helper column n with the value 1 in each cell
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
Create a Checkbox column called Blocker and put this formula in it: =IF(COUNTIFS(Predecessors:Predecessors, Row@row, Status:Status, "Blocked") > 0, true)
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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.