Tracking dependent tasks across multiple projects and reporting on a dashboard

Kate OK

I'm looking for help with tracking and reporting on tasks that are interdependent across multiple projects.

Background:

We are running a Program Office that includes 8 strategic workstreams plus a BAU stream. There are a number of tasks/activities that have cross project dependencies. For example, a monthly board meeting which is a BAU activity is relevant to any workstream that needs to provide an update to the board in that month. In other examples, there are outputs from one workstream that are an input to another.

Each workstream has a project dashboard and the program also has a strategic program dashboard.

What we already know how to do and understand:

1. We know how to link rows from one sheet to another
2. We know how to turn on the highlighting feature so that we can easily see changes that have occurred.
3. We understand how to set predecessors so that the activities are directly linked to each other
4. I've just read on another post that we can set an alert/notification for when these activities are changed, so that's really helpful and will investigate that further as well.

Our specific questions that we don't know the answer to:

1. Is there a way to do conditional formatting or somehow visually highlight these rows that are linked from another sheet? We would like each project manager to be able to quickly and easily see where they have an activity that links out to something else, or where they have an activity that links in from something else.
2. Is there a way to show this visually on the gant chart, that there is activity from another stream that this project is dependent upon, or that another stream is dependent upon this activity?
3. Is there a way to create a report that shows just these interdependent tasks. We'd like to include this report on the project and program dashboard.

Thanks in advance.

Katie.

Kate OK

Update FYI - I ended up creating a column and called it "Linked Task" and made it a drop down with two options "Linked In" and "Linked Out". Then I did conditional formatting based on the value in this cell and I can generate a program report filtering for these values. Winning!

Stefan

Hi @Kate OK,

sounds like the key to all your open questions would be a so called "helper column".

1. As a standard, cells with incoming or outgoing links have those little triangle marks in the cell. Alas, as far as I know, there is no way to have a conditional format triggered by incoming or outgoing cell links.
   
   But this would be a great feature, so you might ask for this here:
   https://community.smartsheet.com/post/idea/smartsheet-product-feedback-%26-ideas
   
2. and 3 and eventually 1. too:
   You can add a (hidden) helper column with a column formula checking criteria identifying rows with cell links. Conditional formats could then be based on the value in the helper column.
   For 2. you could build a format for the colour of the bar in the Gantt chart.
   For 3. you could use the helper column to filter a report.
   And for 1. From your board meeting example I assume, that most of the time dates would be linked. So a conditional format could be designed to identify tasks named "board meeting" and mark only the "start date" column in the affected row.

Hope this helps!

Greetings

Stefan

Kate OK

Thanks so much Stefan. I've submitted the idea via the link provided.

I think your helper column idea sounds like a possible solution. Formulas are not my area of expertise, so could you please provide me an example how I might write the formula to identify rows with cell links?

Kate OK

Update FYI - I ended up creating a column and called it "Linked Task" and made it a drop down with two options "Linked In" and "Linked Out". Then I did conditional formatting based on the value in this cell and I can generate a program report filtering for these values. Winning!

Georgie

Hi @Kate OK,

Thank you for posting your solution. I'm glad you found a way to do this!

Georgie