Master Roll Up Sheet and Reports

Jana H

Hi there. I'm still rather new to using Smartsheet and am being tasked with really thinking outside the box to creatively build views and reports with Smartsheet.  I am currently working on a project where we have 8 auditors (with 8 separate sheets of their own).  Each of the sheets has categories and subcategories of the work involved with an audit. Our thought was to then aggregate the data into a higher level view. I'm struggling but managed to create a test sheet with roll up columns and the SUMIFS calculation. Now that I have this in a roll up sheet for mgmt., I can't figure out how to create a report on a specific subcategory across all the sheets or from the roll up sheet.  It can't be that hard, right?  Any pointers?

 

Jana

Gio Sanchez

Hi Jana,

 

Welcome to Smartsheet Jane.

When you say, roll-up sheet, I assume you mean a sheet that contains linked cells versus a Smartsheet report.  

A tip I learned is that Linked Sheets are far more manageable when each source sheet contains a common sheet metrics section.  This common sheet metrics contain the metrics you're collecting across all sheets.  Once each sheet has this section, it's a lot easier to display these metrics in a SIGHTS, or target Linked Sheet.  

For example, if I want to collect a count of all rows that contain the sub-category value:  Real-Estate.  If each sheet has Sheet Metrics section, you could add a "Metric Row" for calculating the count of rows containing the Real Estate value.  The final step would be to then pull from each source sheet the Real Estate metric, and roll it up to a parent row.  See the attached screenshot for an example of a Sheet Metrics section.

Hope that helps.

 

Jana H

Hi Geo.  Thanks for your quick response.  Yes, for each sheet I created a roll up with the SUMIFS calculation so it would pull in the amount of time (hours) it takes for that subtask.  I then created a master roll up sheet with cell links to those parent/children rows to see all 8 auditors (time) in one sheet.  In your example above, are you suggesting putting in a metric row on each sheet so that it appears on my master roll up?

Here's an example of what my exec mgmt. team wants out of the data.

We have each auditor enter how many minutes/hours it takes for each subtask associated with the Work Type (Pre-Audit, Fieldwork, Post-Audit).  While pulling in a roll up using cell link, it's a nice view.  However, how so I drill down so that once we see this data on one sheet we can filter on a particular subtask (not for an individual, but for the task).

For example, if we are interested in knowing how many hours it is taking for the subcategory E/M across all auditors, I can't seem to simply pull that data into a report.  I feel like I am missing something key.  And it's likely I am still just too new to this platform and need more training.

Jana

Si Spence

Thanks Gio, this has really helped me.

another two question please...

1. in Row 4 on your screenshot, you show 5 metrics in one cell, out of interest how did you achieve this please?

2. Do you know how to create a metric from a 'flag'? i have used flags to symbolise high and low risk and would like to add up how many of each flag we have, however the 'countifs' don't seem to work the same on flags as they do with words.

Any help is appreciated,  Thank you.  Si. 

 

Mohammed khan

Hi Gio

 

I would like to know the howe I can get the summary of the task completed from my sheet.

example:

Total Task: 100

Completed :10

hold: 20:

canceled:30

in Progress:40

Gio Sanchez

Hi @Si_Spence

 

I see you responded a year ago.  Sorry for the late reply.

Regarding your questions:

1.  In row 4, 5 metrics in one cell?

Answer:  I used this kludgy hack where i concatenated the labels + metrics results into a single long string; and then format cell to wrap the cell value.  

In the cell you are referencing, I used this formula:

="Discovering.." + COUNTIFS(RowType:RowType, "L2 - Project", [Current Phase]:[Current Phase], "Discovering") + " Active.........." + COUNTIFS(RowType:RowType, "L2 - Project", [Current Phase]:[Current Phase], "Active") + " Complete......." + COUNTIFS(RowType:RowType, "L2 - Project", [Current Phase]:[Current Phase], "Complete") + " On-Hold....." + COUNTIFS(RowType:RowType, "L2 - Project", [Current Phase]:[Current Phase], "On-Hold") + " Cancelled..." + COUNTIFS(RowType:RowType, "L2 - Project", [Current Phase]:[Current Phase], "Cancelled")

 

2.  How do create a metrics from a "Flag".

All symbol fields are actually drop-down fields w/ text values that visualize the text as an image.    When you click on the drop-down value, you should see the text value.  But if you don't write the formula:

''=Flag2'     //assuming that your symbol column is called Flag; and value is in row 2.

And then to count the values, use the formula

=COUNTIF(Flag:Flag, "High")   

Check out the attached screenshot.  Hope that makes sense.

Good luck!

Gio

Gio Sanchez

Check out the response I just posted above.  In there, I provide the formula I used to calculate and display multiple metric values in a single cell.  

 

Note:  I recommend calculating each metric in their individual row.  It's much easier to write and utilize for Dashboards, etc.   I only use the multiple metrics in a single cell when I need to save space in a Dashboard.  

 

Hope this helps.