Counting number of submissions and various stages

s_mawer

Hi

I'm new to Smartsheet and trying to figure out ways it can help me keep track of the work we have ongoing in the department.  At the moment I have an excel spreadsheet where I enter every new submission to we make to an outside authority, a new line when it is validated and then another when it has been approved.  My boss has to present metrics at the beginning of each month of how many submissions have been made, validated and approved the previous month and also likes to keep an ongoing tally for the year.  Currently I do this through a pivot table on excel and I'd like to do something similar with Smartsheet.  I was thinking of having the initial submission as the parent line then each update against it as a child.  Is there a way of then easily gathering the stats my boss needs neatly compiled in one place? Would I need access to a dashboard to do this?

Any advice anyone can give me would be much appreciated.

Thanks 

Paul Newcome

Depending on the details, you could also keep everything on one row and automate the approvals. You could then build Metrics Sheets and reports and have everything compiled on a dashboard in an easy to read format.

Andrée Starå

Hi,

At a glance, it sure seems like what you want to accomplish can be done without a dashboard, but I think a dashboard would be much better.

Can you share some screenshots of the solution you have now in excel?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

s_mawer

Thanks, this is the kind of thing I have at the moment.  First image is a sample of the spreadsheet.  It gets very long as I do a new line for every update and it's not always easy to track which submission has been approved etc.

Second is the pivot table - which is the kind of thing I'd like to reproduce.

Andrée Starå

Do you have to add a new line for everything? It seems like it would be more straightforward in Smartsheet to keep it on one row.

The metrics that you need to show are reasonably straightforward and easy to set up in Smartsheet.

Best,

Andrée

s_mawer

No, I guess I don't need a new line for each update.  I could just have a column for date submitted, date validated, date approved etc couldn't I?  If I did that, what would you suggest I do to get the figures broken down by submission type and status?

Paul Newcome

I would suggest a metrics sheet which could be set up very similar to a pivot table.

 

Status would be determined on the original sheet by which dates have been filled out.

 

Metrics could then be pulled using cross sheet references and pretty standard COUNTIFS functions to establish the counts for each type by status, status by type, or however else you would want to pull it.

 

From there you could either leave the data as is on the sheet, or tie it in to a dashboard to make things easier to read using reports, graphs/charts, etc.

Andrée Starå

Yes, that sounds like a better structure. I agree with Paul, and a metric sheet would work great. Another option could be to calculate all metrics in the top of the sheet where it could be sectioned/hidden inside a parent.

Which method would work best for you?

Best,

Andrée

s_mawer

Hmm, both good options.  I feel like my boss might prefer to just have the information on a single sheet rather than seeing all the details.  Will give it some thought and have a play with COUNTIF functions.

Thanks for your help.

s_mawer

Thanks Paul, hadn't thought about creating a separate sheet with just the metrics.  Don't have the dashboard function unfortunately (only have a basic Smartsheet package) but might be able to argue it's utility based on this.

Andrée Starå

Happy to help!

Best,

Andrée

Paul Newcome

Happy to help!