I am looking for a round about way to create a search function in SmartSheet.

Stephanie W

I am trying to create a formula to use on a 'Search Sheet' that, when a keyword is entered into a cell, it will return several columns of information from potentially more than one row of the source sheet. I have done so much research that I think it may involve Index, Match, Collect, and maybe some other functions.

Basically, on the source sheet, I have several columns of data (one that includes keywords), but I only need to return specific columns (e.g., Project Name, Primary Category, Subcategory, Resource Type, Team, Content Designer, SME, Audience, Resource Link) to the 'Search Sheet.' If a keyword is found in more than one row, I need it to return the information in those columns specified above, from both all rows having that keyword.

I feel like this is possible with all the feeds I've found and even with the help of ChatGPT, but I can't seem to get it to work.

Paul Newcome

Is there a reason you are doing this through a sheet as opposed to creating a report? Do you have access to the premium add-on Dynamic View?

Stephanie W

@Paul Newcome From everything I've been reading, everywhere it seems… This seems to be the way people have found a way to "search" for something on a sheet. Essentially, I am trying to find a way to mimic "Control F" to find the word on a sheet (even if it is only to highlight the information, so a user can know where to go.

Basically, the source sheet has rows and rows of completed projects. I'd like the users to be able to search keywords and have SmartSheet highlight where those words are.

Of course it would be even cooler if I could create some sort of keyword entry on the dashboard to then have the rows returned back to the user.

I am not sure if our company has the dynamic view.

Stephanie W

@Paul Newcome It does look like we have dynamic view.

Paul Newcome

In that case I would suggest Dynamic View since it seems you don't want the users operating directly in the main sheet.

Reiner

Were you able to figure this out? It's a challenge.