Sheet Summary - Show completed tasks and status

Nikki. A

Below is an example of one of my sheets in smart sheet

I would like to add a sheet summary that :

1. Shows the status of the overall project automatically ( in Green/Yello or Red) by calculation, as in looks to see if the status of the items that were supposed to be finished in the last two weeks are Done and if the status of the items that were supposed to start in the last two weeks is set to In Progress
2. I'd like to also add a field that shows all the items that were completed in the last two weeks including their parent children
3. I'd like to also add a field that shows all the items that are supposed to be completed in the next two weeks ( including their parent )

Nikki. A

To give you more context, I've even tried this to show tasks that are due this week

=INDEX([Task Name]:[Task Name], MATCH(“This Week”, [Weekly Dues]:[Weekly Dues]))

and it gives me an error

Genevieve P.

Hi @Nikki. A

Sheet Summary fields only have one cell to fit information into. It sounds like it may actually be better for your process if you created two Reports for 2. and 3.

You can set filters on Reports to filter by what was Completed and by a date range. Then you could put both Reports on a Dashboard so you can see them side-to-side. This would allow you to surface more than just the task name.

The alternative would be to use a JOIN(COLLECT formula instead of INDEX(MATCH. JOIN would bring together all the values from one column based on the filters you input in the COLLECT Function.

For example:

=JOIN(COLLECT([Task Name]:[Task Name], [Weekly Dues]:[Weekly Dues], "This Week"), ", ")

Note that in your formula your quotes are curved “This Week” but they will need to be straight up and down "This Week".... " versus “ . If you type quotes into the cell in Smartsheet it should automatically type in the correct version.

For your Point 1, can you define exactly what each colour represents? What would make it Green, what would make it Yellow, what would make it Red?

Cheers,

Genevieve

Nikki. A

Thank you for your thoughtful response @Genevieve P. Can you please check this issue here:

https://community.smartsheet.com/discussion/90155/generate-a-report-by-using-sheet-summary

I've outlined the questions, the formulas that work and the issue I am having with them.

Genevieve P.

Hi @Nikki. A

Thanks for letting me know! I've hopped over on to that post with some suggestions.

Cheers,

Genevieve