Sheet summary filters - remove anyone with 0

Laura G

Hi Everyone

I have created a sheet summary report for our dashboard to show overdue status on activities across 13 staff members.

I have tried to use the filter in the report to only show users with tasks above 0 or between 1-100 but it is not removing users with 0 tasks against there name. I am using the OR function here

The idea of this report is to at any given time only display users with tasks that are overdue. As some users have 1-2 tasks over a year they will most likely have less overdue tasks compared to someone with 300 plus tasks in a year.

What is the best way to remove users with 0 overdue tasks

Thank you

Paul Newcome

@Bassam Khalil You do not use "@row" with cross sheet references.

@Laura G You are not able to do this with the report filters because your people are columns. The filters only act on the rows. If your people were the rows and the statuses (such as overdue) were the columns, then you could.

Bassam Khalil

Hi @Laura G,

By employing a combination of the COLLECT and INDEX formulas, you can efficiently filter out users who have tasks greater than 0 or within the range of 1-100. The formula you'll need is as follows:

=IFERROR(INDEX(COLLECT({Employee Name}@row, {Done Task}@row, AND(@cell > 0, @cell <= 100)), 1), "")

If you're interested in having me implement this directly into your Smartsheet, please share a copy of your main and summary sheets with me. Before sharing, ensure to remove any sensitive or critical data to maintain confidentiality. This step is crucial for safeguarding your information while allowing me to assist you effectively.

Laura G

Hi Bassam

Thanks for your response, given this is a report I can't add any formulas.

What steps do i need to take to employ your formula ?

Many thanks

Paul Newcome

@Bassam Khalil You do not use "@row" with cross sheet references.

@Laura G You are not able to do this with the report filters because your people are columns. The filters only act on the rows. If your people were the rows and the statuses (such as overdue) were the columns, then you could.