Automatically populate a new row in a report that contains formula references to a master sheet

AnnabelP

Hi, I am struggling to find a solution to something that should be straightforward, I think. I want to create a dashboard that plots (1) summary values from a master sheet extracted every 2 weeks. Alternatively, I thought I could plot these values from a helper sheet that contains the same formula with a cross-reference to the master sheet (countif formula). In this case, a new row would have to be added automatically every two weeks with the countif value being automatically calculated. Seems easy? Thanks for your help!

Paul Newcome

If you are already calculating these for somethign such as "past 2 weeks", you can use a copy row automation set to run every 2 weeks on the metrics sheet that is auto-calculating to push the count on a bi-weekly basis as static data as a new row on another sheet which in turn could be referenced by a row report which then populates your chart on your dashboard.

SueinSpain

I actually have fields (helper fields) that calculate True or False for This Week, This Month, Last Month, Last 3 months etc.

You could do the same to calculate if the record is applicable for the last 2 weeks

=IF([Raised On]@row >= TODAY(-13), "True", "False")

Tip - I do have an automation that runs every day at 1am and records a date in a dummy field I have called Analysis Date as this keeps TODAY being current otherwise it only gets updates when the smartsheet is updated

You can then have a summary field on the smartsheet that counts if the This fortnight = "True"

=COUNTIF([This Week]:[This Week], ="True")

=SUMIFS(Value:Value, [This Week]:[This Week], ="True")

etc.

I then create a report as a summary report just to get all the summary fields accessible.

Then on the dashboard you can either make a chart from the summary report or you can use a metric widget to directly take the value from the summary field on the smartsheet.

Hopefully this makes sense and helps :)

AnnabelP

Hi Sue, thanks for the quick reply! I am afraid I am not entirely sure how this works. Maybe I was not explaining well. What I would need is visualize the trend of the total amber and total red alerts by plotting the actual values every two weeks. How would you suggest to proceed?

Paul Newcome

If you are already calculating these for somethign such as "past 2 weeks", you can use a copy row automation set to run every 2 weeks on the metrics sheet that is auto-calculating to push the count on a bi-weekly basis as static data as a new row on another sheet which in turn could be referenced by a row report which then populates your chart on your dashboard.