Summary Sheet Columns

Pete Cardenas

I've created a summary sheet to display the totals of all other sheets, but my columns to select on the summary sheet are limited to:

Created

Created By

Modified

Modified By

My sheets all contain

Task Type

Number of Tasks

Percentage Completed

Each Task Sheet is for a different departmetn

Is there something I'm doing wrong?

Mark Cronk

Hi @Pete Cardenas ,

Did you create a sheet summary report? Sheet summary reports retrieve data from sheet summary fields that you have to create. Sounds like you may have missed this step. See help below.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Create the same summary fields with the same name and formulas in each of your task sheets.

Then create a new Sheet Summary Report and select all of your Task sheets. If each sheet contains the same data field names you should see all of them in the report builder. Select the ones you want and create the report. Help below.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary-reports

Work for you?

Mark

Mark Cronk

@Pete Cardenas ,

No problem. We'll figure it out.

Unfortunately, it's not possible to sum column totals in a Sheet Summary Report. You'll need to build a New Sheet to collect totals from your other sheets.

You can do this a couple ways. One, you can create a section in each sheets with the totals you want to use elsewhere. See picture below. I usually insert a few rows at the top and enter a title in 1 column and the calculation in the next; ignoring the column titles. Pick columns that have the right property for what you're gathering; e.g. if you want to report a date you need to put the summary field in a date column. Lock these rows so they don't get changed once you're done building the. Then in your New Sheet do a cross sheet reference to the cell in each underlying sheet.

The other way to do it is to do your calculations in the New Sheet using COUNT or SUM functions with cross sheet references. If you just want total counts of projects your New Sheet would have 2 columns: Sheet Name and Project Count with a total at the bottom of the Project Count column. Can you construct the formula to count projects?

Hope that gets you going in the right direction.

Mark

Mark Cronk

Hi @Pete Cardenas ,

Did you create a sheet summary report? Sheet summary reports retrieve data from sheet summary fields that you have to create. Sounds like you may have missed this step. See help below.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Create the same summary fields with the same name and formulas in each of your task sheets.

Then create a new Sheet Summary Report and select all of your Task sheets. If each sheet contains the same data field names you should see all of them in the report builder. Select the ones you want and create the report. Help below.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary-reports

Work for you?

Mark

Pete Cardenas

Yup! That's the issue!

Thanks again!

Mark Cronk

Glad to help. Please mark my answer as accepted. Have a great day.

Mark

Pete Cardenas

Welp, I guess it's not what I'm wanting to do.

Sheet 1

Total Projects - 1

Sheet 2

Total Projects -2

Report

Total Projects -3

How do I get to that?

Mark Cronk

@Pete Cardenas ,

No problem. We'll figure it out.

Unfortunately, it's not possible to sum column totals in a Sheet Summary Report. You'll need to build a New Sheet to collect totals from your other sheets.

You can do this a couple ways. One, you can create a section in each sheets with the totals you want to use elsewhere. See picture below. I usually insert a few rows at the top and enter a title in 1 column and the calculation in the next; ignoring the column titles. Pick columns that have the right property for what you're gathering; e.g. if you want to report a date you need to put the summary field in a date column. Lock these rows so they don't get changed once you're done building the. Then in your New Sheet do a cross sheet reference to the cell in each underlying sheet.

The other way to do it is to do your calculations in the New Sheet using COUNT or SUM functions with cross sheet references. If you just want total counts of projects your New Sheet would have 2 columns: Sheet Name and Project Count with a total at the bottom of the Project Count column. Can you construct the formula to count projects?

Hope that gets you going in the right direction.

Mark

Pete Cardenas

Yeah. It just seems so much more difficult that excel, and I was told Smartsheets was more powerful. I can sum things up super quick on excel. I'll see if I can complete.

Mark Cronk

Good morning @Pete Cardenas ,

As someone who started with Excel for Macintoch version 1 in 1985, it takes time to get used to the Smartsheet way. It's like Excel, but not Excel. I started by viewing the training videos and building simple sheets. Lots of trial and error. I still use excel for many things but turn to Smartsheets more and more. If you share data, have multiple people viewing and editing, or need mobile access, Smartsheet is normally superior to Excel. Excel remains better for some stand alone solutions or when you need complex formulas and functions that don't reside in Smartsheets, e.g financial modeling and solving.

Hang in there. Once your brain shifts to the Smartsheet way you'll find all kinds of helpful uses.

Mark