Summary Sheet Formula and Milestone / Key Task Gantt

Harley Esguerra

Hello community,

I am new to using the smartsheet so hoping you can help me with the below.

1. How can I update this fomula that I am planning to use in my Summary Sheet so that it only includes count of tasks that has a start date of in the future. I want to exclude those from my count.
2. I consolidated 3 projects into one integrated plan because of the dependencies between them. It was too hard and added risk if I separated them out into differenct individual project plans, dashboards etc. I know I could have stiched them via reporting.
   1. I want to try to split them apart from this integrated plan into separate sheets. Can I use the link functionality for this? Whats the pro's and con's?
   2. I want to build a separate gant view in my dashboard just showing milestones and key tasks AND also show the predecessors (dependencies). The challenge I am having is, I am using the approach puting "0" durations to my milestones (normal). However, when I to show that in a Gantt View, its not showing the dependencies because my predecessors for the Milestones are only for the tasks that I need to complete to hit the milestones. My milestones do not have predecessors to other milestones or tasks. (I hope that made sense). Question is, is there another way to build the Milestones and Key Task Gantt view so I can show ALL Milestones and Key Task in a Gantt view. Below is a screenshot of the hierarchy to show how my MS's are mapped.

How would you handle the above? Thank you in advance!

Harley Esguerra

I forgot to include the formula for question #1. Here is the formula I want to update.

=COUNTIFS(Progress:Progress, "Not Started", Level:Level, "")

Brian_Richardson

Hi Harley.

=COUNTIFS(Progress:Progress,"Not Started",Level:Level,"",Start:Start,>TODAY())

You need to have your data stored and updated in one spot, not two sheets. So either you need to keep them in 1 sheet and use reports to filter out separate workstreams, or split them into 3 sheets, but not both. If you split out into multiple sheets, you can link tasks across the sheets using the Cell Link feature. If you're driving a task on one sheet from another sheet, then I usually setup a task in the second sheet that represents the task in the first sheet, then cell link the first sheet task's end date to the start date of the second sheet's task.

I'm not understanding the last question. You have milestones, and predecessor tasks for those milestones, and you want to show both right? But presuming you have a fully baked set of dependencies (as it appears from your screenshot), it seems like you'd end up with pretty much every task on the sheet if you went that route?

If you want to show only certain tasks and related milestones, honestly the easiest thing to do is have a flag checkbox for "Key Task" and just flag what you want to show up. Especially since you don't have any predecessors to your milestones to "walk" with formulas.

If you do want to determine with a formula what the predecessors are for a given task and use that somehow, it's a little tricky, as you cannot directly use the Predecessors column in formulas. Also, if you aren't Predecessoring your milestones (why not?) then this isn't going to do much for you. However, you can use this framework to build on if you decide to somehow trace back tasks under the parent, for example:

1. Add a Successors column and use the formula =JOIN(SUCCESSORS([Task Name]@row),CHAR(10)) in that column and make it a column formula.
2. Add an Autonumber column if you don't already have one. I'll call it "Auto" here but use whatever name you want.
3. Add a Row Number column with the formula =MATCH(Auto@row,Auto:Auto,0) and make it a column formula
4. Now you can use these columns in formulas. For example, I do a count of predecessors that are "Complete" and have the same Parent Task with this formula: =COUNTIFS(Status:Status, "Complete", [Task Name]:[Task Name], PARENT([Task Name]@row), Successors:Successors, HAS(@cell, [Row Number]@row + ""))

Note you have to use +"" after Row Number in this formula because the Successors column is using JOIN, which returns text. The Row Number column returns a number. So +"" converts a number to text.

Harley Esguerra

Thanks Brian_Richardson - I am going to try this formula