Reporting/dashboard on form entries

Marissa T.

Our Referrals departments has recently started utilizing a smartsheet form to track key information on each referral that comes into our hospice. Each team member completes & submits the form when they get a referral. I have the sheet set to automatically date/time stamp each row as it comes in. The department heads are requesting some reporting that I would like to display in a dashboard. Here are the main 2 data points we are looking to capture.

1: Ability to see month over month how many referrals were submitted.

2: Ability to track how many referrals were submitted by each team member and previous month to current month.

I had created a couple reports, but they would require manual updating each month to update the month. Looking for something more automated.

All recommendations welcome! Thank you.

Austin Smith

@Marissa T.

The fastest, easiest way imo:

Build a helper column on primary page with column formula =month(created@row)

1) run sums on the months in the helper column in summary data or on another sheet

2)

Build out a summary data sheet using formula =countifs(created:created, =month(today()), [team member]:[team member], "Laura") - which gives you how many referrals Laura had this month.

Summary data sheet also has a column with =countifs(created:created, =(month(today()-1)), [team member]:[team member], "Laura") - which gives you her numbers for last month

Pull a report on the summary data sheet to show the comparison.

Austin Smith

@Marissa T.

Ah, typo.

Should be ...=month(today())-1), [team...

The way it was typed it subtracts a day, not a month.

Austin Smith

@Marissa T.

The fastest, easiest way imo:

Build a helper column on primary page with column formula =month(created@row)

1) run sums on the months in the helper column in summary data or on another sheet

2)

Build out a summary data sheet using formula =countifs(created:created, =month(today()), [team member]:[team member], "Laura") - which gives you how many referrals Laura had this month.

Summary data sheet also has a column with =countifs(created:created, =(month(today()-1)), [team member]:[team member], "Laura") - which gives you her numbers for last month

Pull a report on the summary data sheet to show the comparison.

Marissa T.

@Austin Smith Thank you so much! This works, except when I enter the second formula with the -1 to tally the previously month, for some reason the -1 doesn't seem to be registering and it is still tallying the current month. Do you have any suggestions or thoughts on why that might be occurring?

Thanks again!

Austin Smith

@Marissa T.

Ah, typo.

Should be ...=month(today())-1), [team...

The way it was typed it subtracts a day, not a month.