How do I create a search tool within a sheet and referencing another sheet?

daniella.martin

Hello everybody,

I am a new user of SmartSheet and I was recently assigned a project of building a search tool within a data sheet. I got a way of doing it on Excel but I would prefer to build something similar on SmartSheet. Does anybody have any idea or how can I get to do this?

Basically I have a master sheet like this with 178 countries and 12 different products, the sheet is to know if we are able to ship these products to these countries.

and then, with that information I built another sheet to make it easier to navigate through this information. It is basically just to filter the information out. The user will give the sheet a country, then a product, and the same sheet will return the answer if the product is available for shipping (Yes or No)

this is the formula I used on the Excel version, does anybody know if I could replicate something like this on SmartSheet?

Any help would be greatly appreciated!

jmyzk_cloudsmart_jp

Hi @daniella.martin

The `COLLECT` function in Smartsheet bears resemblance to Excel's `FILTER` function. 😁

https://help.smartsheet.com/function/collect

Using `COLLECT`, you can retrieve values from the 'Available' range based on specified product and country criteria. 

To display the outcome, leverage either the `INDEX` or `JOIN` function. While my demonstration employs the Sheet Summary fields, the same approach can be applied within an independent sheet.

https://app.smartsheet.com/b/publish?EQBCT=cadc63a2597e4255802738375e0a6c88

jmyzk_cloudsmart_jp

Hi @daniella.martin

The `COLLECT` function in Smartsheet bears resemblance to Excel's `FILTER` function. 😁

https://help.smartsheet.com/function/collect

Using `COLLECT`, you can retrieve values from the 'Available' range based on specified product and country criteria. 

To display the outcome, leverage either the `INDEX` or `JOIN` function. While my demonstration employs the Sheet Summary fields, the same approach can be applied within an independent sheet.

https://app.smartsheet.com/b/publish?EQBCT=cadc63a2597e4255802738375e0a6c88

daniella.martin

Thank you so much for your help! It works perfectly, just as I imagined it to work