Best way to calculate stats on a sheet tracking form submissions

AAhumada

I have a SmartSheet that logs "installation issues" as they are submitted via web form.  The sheet tracks the "locations" id number, address, owner, manager, issue, issue type, and who is working on the issue. Its a tracker you could say.    

I want to be able to calculate how many of "issue type" A or B have been submitted via the form. and maybe even the percentage that have a "issue resolved" date entered for them. Normally in excel, I would have the tracker on one sheet and do the formulas on a second sheet. However, in SmartSheets I know that you can't reference or calculate data in cells from another sheet. So it looks like I'm forced to do the calculations in the main tracker sheet. My question is, where do you all normally do your calculations in this sheet? I don't want to put it in the rows as it would blend in with the form data that's submitted. Maybe I'm over thinking this?

Robert S.

Hello,

 

Thanks for the question. It is true that currently Smartsheet doesn't support cross-sheet formulas, however this functionality is being worked on and we hope to release in Q1. Keep in mind however that this could change, so we don’t have an exact date at the moment. 

 

In cases like this, we generally recommend putting these formulas either at the top or the bottom of the sheet, whichever is opposite of where the form enters new rows. If you'd like to keep them separate, I've also seen other users create new columns for these formulas only, as well as create a parent column that the formulas can be indented under to be able to collapse them if needed. More on hierarchy can be found in our help center article here (https://help.smartsheet.com/articles/504734).

Mike Wilday

As Robert said, adding this data to "summary" rows is a practice I have seen MULTIPLE users use. You can create a row at the opposite end of your form entry place and indent multiple rows under it to create a summary roll-up section. 

raylene

Has this been implemented yet?

Andrée Starå

Hi Raylene,

Yes, it has. You can now use cross sheet formulas.

More info: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done