Generate a Report by using sheet summary

Nikki. A

I'd like to generate a report that shows :

Which tasks are in progress ( Including their parent )

which tasks are at risk ( Defined by tasks that were supposed to be started but are not started yet ) and it should show their parent as well.

One of the problems I have is that under a given milestone, there can be several tasks such as Requirements, Development, testing, etc that can be the same across different milestones, unless the parent is also displayed, I'd have to idea which task it is referring to.

I've tried using both the smart sheet summary as well as reports but I was unable to get anything close to this.

Genevieve P.

https://community.smartsheet.com/discussion/comment/327656#Comment_327656

Yes! Thank you for posting screen captures, that's exactly what I was going to say.

Does it work now that you're looking for "Completed"?

Paul Newcome

Do you already have a column that has the status in it (in progress, at risk, complete, not started, etc.)?

Nikki. A

Yes I do

Here's an example of what I have

Nikki. A

To give you an update, I now have this :

My formula for the status column is:

=IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green")), "Green")

What I am trying to do on the summary sheet is to :

1) Show an overall symbol of Green, Yellow, Red that indicates the overall status of the project. I've tried this formula:

=IF(Status@row <> "Completed", COUNTIF(Health:Health, "Red") > 2, "Red", COUNTIF(Health:Health, "Yellow") > 5 , "Yellow", "Green")), "Green")

But it gives me an error. Ideally I'd like this to actually be tied to the parent view I need as mentioned here https://community.smartsheet.com/discussion/83230/formula-to-automate-the-symbols-on-a-sheet#latest but if that is not possible above would work.

2) Show the tasks that are in progress ( Including their parent ) and their due date

3) Show the tasks that are at risk ( Defined by tasks that were supposed to be started but are not started yet ) and it should show their parent as well.

I know the formula =[Task Name]@row + " | " + PARENT([Task Name]@row) works for showing the parent + task name but how do I incorporate the IF condition or the For condition here.

I tried this formula and the problem is it only returns the first task and it only returns the task name without the parent.

=INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell = "In Progress"), 1)

Nikki. A

For my question #1, the following formula works:

=IF(COUNTIF(Health:Health, "Red") > 3, "Red", IF(COUNTIF(Health:Health, "Yellow") > 5, "Yellow", "Green"))

But this formula does not work:

=IF(Status@row <> "Completed", IF(COUNTIF(Health:Health, "Red") > 3, "Red", IF(COUNTIF(Health:Health, "Yellow") > 5, "Yellow", "Green")), "Green")

Nikki. A

Hi @Paul Newcome

Thank you for your response, would appreciate your help on this one.

Thanks,

Nikki. A

A quick update here in case others are having similar issues:

1) Show an overall symbol of Green, Yellow, Red that indicates the overall status of the project by skipping the parents row ==> Solved

=IF(COUNT(CHILDREN()) = 0, IF(COUNTIF(Health:Health, "Red") > 3, "Red", IF(COUNTIF(Health:Health, "Yellow") > 5, "Yellow", "Green"))

2) Show the tasks that are in progress ( Including their parent ) and their due date

On the smartsheet summary, I am able to use this formula to generate a list of current tasks that are in progress

=JOIN(COLLECT([Task Name]:[Task Name], Status:Status, "In Progress"), CHAR(10))

What is missing is being able to pull in the parent and/or parents of these tasks and the specific due dates.

I've tried several variations of using PARENT() but none of my formulas are working.

3) Show the tasks that are at risk ( Defined by tasks that were supposed to be started but are not started yet OR Tasks that were supposed to be done but are not done yet ) and it should show their parent as well.

I am able to use this formula:

=JOIN(COLLECT([Task Name]:[Task Name], Health:Health, "Red"), CHAR(10))

The issue is that this is only looking at the tasks that are red, so only taking into account the tasks that were supposed to be done but are not done. I want to be able to add an AND statement here to also show/collect the tasks that are yellow. Additionally I have the same problem with showing the parent / parents of the task. For the parent/child relationship, I have tried several PARENT and Ancesstor functions as well as using substitude and all give me an error

4) I also want to show tasks that were completed in the last two weeks. But my IF statements with Collect don't quite work

Note: I know I can add a helper column to combine the child name with parent names, but i rather not do that, as I would have to manually do that across many many projects.

Genevieve P.

Hi @Nikki. A

2) Parent names with "In Progress"

The PARENT() and CHILDREN() functions can only be used in the grid of the sheet where the Hierarchy is in place. This means that you won't be able to use PARENT() in a Sheet Summary formula to automatically pull the Parent name - you would indeed need a helper column in your sheet that combines the Parent and Child names together, to then use this helper column in your Sheet Summary formula as the data to bring in to the cell.

3) Adding Yellow Tasks

You can add yellow tasks into this formula by using + and adding a second JOIN(COLLECT:

=JOIN(COLLECT([Task Name]:[Task Name], Health:Health, "Red"), CHAR(10)) + CHAR(10) + JOIN(COLLECT([Task Name]:[Task Name], Health:Health, "Yellow"), CHAR(10))

4) Last Two Weeks

You can add in criteria to your JOIN(COLLECT that looks at a Date Range in the END column, using the TODAY Function:

=JOIN(COLLECT([Task Name]:[Task Name], Status:Status, "Complete", End:End, @cell > TODAY(-14), End:End, @cell <= TODAY()), CHAR(10))

Cheers,

Genevieve

Nikki. A

Thank you @Geard Ledet

Is there a way for me to return parent +Task name in any of the join functions?

For example, JOIN(COLLECT([Task Name]:[Task Name], Health:Health, "Red"), CHAR(10)) + CHAR(10) I want this to return Parent + Task Name as opposed to Task Name

I know you've already said this can't be done, but here is me hoping.

Also, how can I use my collect function to return the task date plus the end date with a " | " in betweek

Genevieve P.

Hi @Nikki. A

For both of these formatting requests you would need to have helper columns in the actual sheet to format the data how you'd like, then the JOIN brings that information into your Summary Field cell.

So for the Parent name:

=PARENT([Task Name]@row) + " - " [Task Name]@row

You can add in the Task Date and End Date in this helper formula as well:

=PARENT([Task Name]@row) + " - " [Task Name]@row + " " + [Task Date]@row + " | " + [End Date]@row

Then this column with the helper formula would be what you'd use in the JOIN COLLECT:

JOIN(COLLECT([Helper Column]:[Helper Column]...

Cheers,

Genevieve

Nikki. A

Thank you for your response. Unfortunately, this won't work for me, as I would have to add 3 helper columns. Unless there is a way to add columns to one of my sheets then automatically replicate it across other sheets.

Nikki. A

Hi @Genevieve P. I ended up taking your suggestion and adding hte helper column.

I now have a new formula

=JOIN(COLLECT([Full Name]:[Full Name], Status:Status, "Complete", End:End, @cell > TODAY(-14), End:End, @cell <= TODAY()), CHAR(10))

But it is not working . The purpose is to show tasks completed in the last two weeks

Nikki. A

To give you some more context, I have even tried to create a column that calculates if the task was done in the last two weeks, then used

=JOIN(COLLECT([Full Name]:[Full Name], Completed:Completed, "Done Last Two Weeks"), CHAR(10))

and still am not getting the rows returned. It showed them returning once and then disappeared.

Genevieve P.

https://community.smartsheet.com/discussion/comment/327116#Comment_327116

Hi @Nikki. A

The formula is structured correctly, so if the values are blank it's not finding a match with your criteria. For example, if your grid says "completed" but your'e searching for "complete".

When you say it's not working, can you clarify what you mean? It would be helpful to see a screen capture with the formula open in the sheet.

Nikki. A

It someitmes work, sometimes does not. It is very odd indeed. There are clearly items that match the criteria but they don't show up.

Ideally, I don't want to have to use a helper column, is there a way for me to make this work in smartsheet summary

=JOIN(COLLECT([Full Name]:[Full Name], Status:Status, "Complete", End:End, @cell > TODAY(-14), End:End, @cell <= TODAY()), CHAR(10))

Genevieve P.

Hi @Nikki. A

You should be able to put this formula into a Sheet Summary field!

Here's what it looks like in my test sheet:

I've added Conditional Formatting so I can quickly see the rows that meet the two conditions.

It would be helpful to see a screen capture of your own sheet with the formula open, like above, as well as the current output, but please block out sensitive data.