Status Report based on certain conditions, i.e. dates, status etc

Thiago Castro

Hi Smartsheet community, 

I'm trying to come up with a status report to display information on what has been done in our projects between certain dates. I would really appreciate your expertise and help!!!

For example:

Project A Status Report for the period 04/Nov/19 - 17/Nov/19

What I want to show is:

1. Completed Deliverables (Phase/Milestone/Task) in the period mentioned above

2. Incompleted Deliverables (Phase/Milestone/Task) that had its End date set for >= 04/Nov/19 and <=17/Nov/19 which did not have it's % Completed = 100%

3. Planned Deliverables for the next Status Report Period (Phase/Milestone/Task) that had its End date set for >= 18/Nov/19 and <=1/Dec/19 which currently its % Completed isn't set as 100% Completed

Note:

I have a Cell where I can put "Status Report Start Date and another End Date" also can create another 2 Cell to show Next Period, which means if I want to report for the full month or just a week I can adjust the dates accordingly.

Any suggestion?

 

 

Sarah Thompson

I would just create a report based on that sheet. The "when" would be the dates in question. If you really want to look at each of your 3 items individually, you could create a report for each one and display them on a personal dashboard for your own viewing.

Alternately, you can create a filters on your source sheet and change them according to what you want to see, but I find filters to be less user-friendly than reports.

Mike Wilday

You can also use cross-sheet formulas and pull your data together in a new sheet. Or use the summary data fields to summarize your data within the sheet. 

• https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets
• https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Paul Newcome

I personally would create a helper column of the text/number type and call it [Needed for Report].

.

In this column you could use a nested IF statement to look at your date cells and your criteria and enter either Completed, Incompleted, or Planned.

 

Then you can pull your report based on this column not being blank, and since it is specified what type it is, you can sort your report on that same column and have everything a little more organized.

.

For this example I will reference date cells as if they are in rows 1 - 4 of a column simply called "Dates".

Dates1 = Current Start Date

Dates2 = Current End Date

Dates3 = Next Period Start Date

Dates4 = Next Period End Date

.

The formula in the helper column would look something like this...

 

=IF(AND([End Date]@row >= Dates1, [End Date]@row <= Dates2), IF([% Complete]@row = 1, "Completed", "Incomplete"), IF(AND([End Date]@row >= Dates3, [End Date]@row <= Dates4, [% Complete]@row < 1), "Planned"))

Thiago Castro

Hi Paul,

Thanks for always be there to help the community.

I'll try this and come back to you.

In the meantime, any idea how I could also bring its parents to the same report?

i.e. Phase then Milestone and the conditional @row (Completed, Incomplete or Planned)?

Thanks in advance.

Paul Newcome

Try something like this...

 

=IF(COUNTIFS(CHILDREN(), ISTEXT(@cell)) > 0, JOIN(COLLECT(CHILDREN(), CHILDREN(), ISTEXT(@cell)), ", "), IF(AND([End Date]@row >= Dates1, [End Date]@row <= Dates2), IF([% Complete]@row = 1, "Completed", "Incomplete"), IF(AND([End Date]@row >= Dates3, [End Date]@row <= Dates4, [% Complete]@row < 1), "Planned"))

.

The bold portion at the beginning checks if for any child rows. If there are child rows and any of them are text (meaning it was populated with one of the three options in the previous formula), then it will join those results together so that your parent will show the status of each of the child rows that fall under it (as a summary of sorts). The unbold portion of the formula is just the previous formula to otherwise populate the child rows.