Filter table based on list?

Daniel Cardenas

In Excel, you can filter a table based on criteria defined in a range. (Data> Sort&Filter> Advanced Filter) In the screenshot below, the criteria range at the bottom would be used to limit rows displayed in the table above. Is it possible to do this in Smartsheet?

I want to avoid using the filter interface and adding each row one-by-one.

Paul Newcome

It is somewhat possible using a formula and 2 helper columns, but you would still need to use the filter interface to filter based on the helper column.

You would put your table to filter on in a different column [Filter List] from the data you want to evaluate. Then in the other helper column (checkbox for example) that you will filter on you would use a formula such as

=CONTAINS(Address@row, [Filter List]:[Filter List])

This will check the box for each row where the data in the Address column matches any of the data found in the [Filter List] column. Then build your filter to show only rows where the Checkbox column is checked. This allows you to have what essentially becomes a "dynamic filter".

Andrée Starå

Hi Daniel,

Unfortunately, as far as I know, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

There might be a possibility to develop a workaround.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

It is somewhat possible using a formula and 2 helper columns, but you would still need to use the filter interface to filter based on the helper column.

You would put your table to filter on in a different column [Filter List] from the data you want to evaluate. Then in the other helper column (checkbox for example) that you will filter on you would use a formula such as

=CONTAINS(Address@row, [Filter List]:[Filter List])

This will check the box for each row where the data in the Address column matches any of the data found in the [Filter List] column. Then build your filter to show only rows where the Checkbox column is checked. This allows you to have what essentially becomes a "dynamic filter".

Daniel Cardenas

Thanks guys, I've sent an Enhancement Request as Andrée suggested. This is the screenshot I included in the request with a little more information. Basically I'm just looking to get away from the repetition of manually selecting one record at a time in a Smartsheet filter. I have a list of fields I'd like to filter the sheet on and I wish I could just specify that range as a filter, or maybe upload a text file or spreadsheet containing the list. When you need to select hundreds of records, the manual process of selecting one record at a time is inefficient.

Instructions below are for executing the filter in Excel.

Paul Newcome

@Daniel Cardenas

I know it isn't exactly like Excel, but you may be interested in my solution above. You build out a table to filter against just like you are describing. Then you put a basic formula in a checkbox column and use a single filter pointed at the checked boxes. I really do think it will work for you.

Daniel Cardenas

Thanks Paul, I'll give it a try. I got hung up on where you said "you would still need to use the filter interface" in your initial suggestion because I assumed it would still involve a whole lot of manual selection/clicking, etc., but I'll give it a shot.

Paul Newcome

Nope. You only need to set the filter to look at the checkbox column and specify to look at either checked or unchecked rows depending on what you want to remove.

After that, you shouldn't have to touch the filter again other than to turn it on or off.

All you should have to do is update your list.

You may need to refresh your browser after updating your list so your filter picks up the changes, but that will depend on your own personal settings. If you have auto-save turned on, it should automatically refresh at the save.

Andrée Starå

@Daniel Cardenas

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Daniel Cardenas

Hey guys just wanted to follow up to say thanks, I got a simple test of a handful of records with just a few fields working using Paul's suggestion and it does seem to do what I need. Thanks @Paul Newcome! I'm working on building out a reusable sheet that a user can add a list to, point it at a separate sheet and retrieve the filtered results they need. Should be useful for my team. Thanks again!

-Daniel

Paul Newcome

Happy to help! 👍️

Andrée Starå

@Daniel Cardenas You're more than welcome!

Kristian Eklund

Hi, would =CONTAINS(Address@row, [Filter List]:[Filter List]) work from other sheets as well? and could i build reports etc with it?

with regards,

Paul Newcome

@Kristian Eklund You would replace the range with a {Cross Sheet Reference}.