Tracking dependent tasks across multiple projects and reporting on a dashboard
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:
- We know how to link rows from one sheet to another
- We know how to turn on the highlighting feature so that we can easily see changes that have occurred.
- We understand how to set predecessors so that the activities are directly linked to each other
- 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:
- 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.
- 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?
- 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.
Best Answer
-
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!
Answers
-
Hi @Kate OK,
sounds like the key to all your open questions would be a so called "helper column".
- 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 - 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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
- 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.
-
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?
-
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!
-
Hi @Kate OK,
Thank you for posting your solution. I'm glad you found a way to do this!
GeorgieNeed more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives