Filter table based on list?
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.
Best Answer
-
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".
Answers
-
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!
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.
-
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".
-
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.
-
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.
-
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.
-
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.
-
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
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.
-
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
-
Happy to help! 👍️
-
@Daniel Cardenas You're more than welcome!
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.
-
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,
-
@Kristian Eklund You would replace the range with a {Cross Sheet Reference}.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives