Report Builder: Include and exclude on same column
In Report Builder for WHAT, I want to include one term but not another for a specific column. (See attached.)
But the "Exclude selected items" check box is all or nothing.
It can't be this hard. There has to be a "contains" and "does not contain" filter ability.
To be clear, I want a result that will show all the primary tasks that contain the word "Assembly" but do not contain the word "Resource".
What am I missing, please?
Comments
-
A "Helper column" that simply duplicates your Primary column's (fill down: =Primary@row) would allow you to filter all that has criteria:
- Primary Contains "XXXX"
- Helper Doesn't Contain "ZZZZ"
-
Hi Mark,
Great idea! That would be a great addition to Smartsheet features.
Please submit an Enhancement Request when you have a moment.
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks, Ezra.
To be clear- I would have to create this helper column in all my source sheets, correct? This isn't some sort of "calculated column" I can create like within an Excel Pivot Table, no?
I ask because I am actually creating two similar reports, each with other a hundred source sheets...
-
Honestly, if that is the only solution, I am just going to export to Excel and do all my work in a pivot table.
-
Another option could be the Smartsheet Premium Add-on, Pivot App.
https://www.smartsheet.com/marketplace/premium-apps/pivot-app
Would that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
If it's only a couple hundred source sheets, I'd just run through proceduraly them in batches of 20...
- open all of them in tabs in a separate window
- add the column and give it whatever helper column name you choose (I usually like to keep the names logical, but it could be "Sparkle" or "DearDiary" if you feel like smiling whenever you run across it)
- copy the column name and run through all the tabs to add the column as a first pass.
- the next pass would be pasting the formula at the top of that column
- final pass would be to copy-down (ctrl-D) the formula for that column
Wouldn't take more than 15 or 20 minutes.. and it would be more useful than exporting it, and stepping through another few processes to get what you want.
-
That's a golden workflow, Ezra. Thanks for sharing your technique.
-
Thanks, Andree. Looks like it might work too.
I'm disappointed it's an add-on. In my limited perspective, it should not be that hard to build a two part filter in Report Builder. Simply allowing the ability to click "exclude" for one criteria and "include" for another in the same column shouldn't require paying more money. But, again, I may not appreciate the complexity. Still, thanks for your suggestion.
-
Nicely done!
Thanks for sharing Ezra!
To add. Another option could be to have a Master sheet where you add the column and a row with the formula with a unique character in front of it.
Something like this. *=IF(COUNTIF(A@row:F@row; 1) = 6; 1)
- Add column
- Add row
- Add formula with the unique character in the beginning
- Copy that row to all the other sheets, and that would create the new column automatically on those sheets
- Open each sheet and do a Find and Replace with and replace the * with nothing and that would activate the formula
- Fill the rest of the column with the formula
Would that work/help?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Happy to help!
I agree that it should be possible to Include/Exclude and I think Smartsheet will add that feature soon. The Premium Add-on, Pivot would help, but it's much more powerful, so I don't think it's for this use case. It would work, but it's not why it's a premium add-on.
Please submit an Enhancement Request when you have a moment.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
AHA! I figured out a solution that worked in my limited case.
I used the search operator " " to restrict my results to only the term I wanted (in this case "Assembly") which excluded everything I didn't want ("Resources").
So simple. I feel foolish I didn't think of it before asking for help or going to the rabbit hole of workarounds but hopefully this can help others.
-
So obvious it seems, but we all missed it
Thanks for sharing!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
+1 for this feature. The " " solution won't work for me. I want to do a filter that has a start date "in the next _ days" but is NOT today. Doesn't seem possible without a 'this but not that' in the same column feature.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives