Sheet summary filters - remove anyone with 0
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
Best Answer
-
@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.
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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
-
@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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives