Welcome!

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?

Find more posts tagged with

Comments

Ezra

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"

Andrée Starå

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

Mark Lappegaard

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...

Mark Lappegaard

Honestly, if that is the only solution, I am just going to export to Excel and do all my work in a pivot table.

Andrée Starå

Another option could be the Smartsheet Premium Add-on, Pivot App.

https://www.smartsheet.com/marketplace/premium-apps/pivot-app

Would that work?

Ezra

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.

Mark Lappegaard

That's a golden workflow, Ezra. Thanks for sharing your technique.

Mark Lappegaard

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.

Andrée Starå

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?

Andrée Starå

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.

Mark Lappegaard

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.

Andrée Starå

So obvious it seems, but we all missed it

Thanks for sharing!

Michael Dillenberger

+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.

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…