How to reference Summary Sheet field in Report Filter

Dan G. · 02/01/23

I have a Sheet Summary Field that I want to use as part of the filter criteria in a report. How to I reference it in the filter?

Alternatively, how can I use the Sheet Summary field in formula on a different sheet (than the Sheet Summary Field is created on) to mimic the report filter.

Samuel Mueller · 02/01/23

@Dan G. in a regular report (Row Report) there is no way to use a summary field in the filter.

However, you can create a helper column in your sheet, and then put a column formula something like

=[summary field]#

that will replicate your summary field in every row of your sheet. Then you could filter with that if you want.

To answer your second question you could use the same column in a cross sheet reference on a different sheet.

Let me know if you have more questions!

Samuel Mueller · 02/01/23

I wanted to add that if you are using a "Sheet Summary Report" which is a different type of report, you can use the sheet summary fields as a filter. The only columns you can choose though are Sheet Summary Fields

referring to this type of report when you create one:

Dan G. · 02/01/23

https://community.smartsheet.com/discussion/comment/359888#Comment_359888

Thanks for the input. Source data for the report is on sheet (a list of tasks) and the filter value (Sheet Summary field) is from a status report sheet.

How do I link the two? If I can access the Sheet Summary Field in the Task sheet, I can figure it out, but can't determine they syntax for that or how to do that via the Reference another Sheet link within the formula builder.

Samuel Mueller · 02/01/23

@Dan G. I think I am a little confused on your ask, could you provide a screenshot of what you are trying to do?

If you are trying to update a sheet summary field from a different location I don't think there is a straightforward way to do that. But you can get the sheet summary field data into your sheet so you can reference it's value (since you can not create a cross sheet reference directly to a sheet summary field). That way when you change the sheet summary value it will update in your sheet(s) as well.

If this is what you are trying to do then you would need to:

Create a helper column on the sheet that has the summary field, then in that helper column put a column formula with this syntax

=[name of sheet summary field]#

replace "name of sheet summary field" with the name of your sheet summary field

On your task list you would create a cross sheet reference to the helper column you created, if you wanted to replicate the value of your sheet summary field on every row in your task list then you could use a column formula with the formula

=index({Cross sheet reference name},1)

Dan G. · 02/01/23

@Samuel Mueller -

Thanks - you confirmed that I couldn't do what I wanted to do the way I wanted to do it.

I basically have a status report sheet where the user creates status summaries for each week and function - one is selected to be shown in a report.

On the plan/tasks sheet, key tasks from that function are supposed to be shown on the report.

I was using a Sheet Summary field on the status report sheet to determine based on all of the rows in the sheet, which is the selected report and what function is it for using the index function.

I was then trying to use the function calculated in the Sheet Summary above to determine which tasks to show from the plan (attempting to filter by the Sheet Summary field) in the report.

I basically just moved the index function to the plan sheet and used the Reference Another Sheet capability to get all the data from the status report sheet and determine which function is selected and compare that to every task to see which ones match.

Doesn't seem like the most efficient way, but it does work.

Samuel Mueller · 02/01/23

@Dan G. yeah sounds like what you came up with is probably the best option.

Dan G. · 02/01/23

Actually - I just figured out how we did it originally (and went back to it). The index function was usd in a Sheet Summary field on the Plan Sheet, so the calculation is only done once. Then I compare each row's function to the Sheet Summary field function, if they match I show in the row in the report.

How to reference Summary Sheet field in Report Filter

Answers

Categories