Equivalent of 'FILTER' function

Dakota Szumilas · 02/11/21

Hi there!

I am looking for a solution to the below issue:

We have a Field Employees Sheet Master that has all of our employees listed from across 16 territories. We also need a seperate Field Employees Sheet per each territory that would populate new rows in the corresponding Territorial Sheet once new employees are added to the Master Sheet.

In Google Sheet's I would simply resolve this with the FILTER function, however, there is no equivalent in SS. Report Builder would not work in this case as well, since I need to add additional rows to the Territorial Sheets that are not a part of the Master Sheet.

For easier reference, I am attaching a picture of an example 'Master Sheet'

and Territorial Sheet for Territory B

Do you have any suggestions on how to resolve this?

Mike Wilday · 02/11/21

Yes! Smartsheet reports are excellent for this sort of thing.

You can create a report for each Territory. By pulling in all the information from the master sheet, selecting which columns you want to display and then filtering the data by the territory. Specifically, look at Step 2 Configuring Your Report, on this help about reports.

Reports | Smartsheet Learning Center

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

Learn more about Smartsheet reports.

Let me know if you have any questions.

Mike Wilday · 02/11/21

Reports will always update with new additions to the master sheet that match the filtering criteria. You can even add the report to a Dashboard for easy access to the data.

Dakota Szumilas · 02/11/21

Mike Wilday

Feb 11, 2021

Reports will always update with new additions to the master sheet that match the filtering criteria. You can even add the report to a Dashboard for easy access to the data.

Hi Mike, thanks for the reply. Like I mentioned I can't use a Report Builder, as the territorial sheets would have some additional fields that are not part of the original Master Sheet. Unless there is a way to customize the report and add them without having to add them to the master sheet?

Sameer Karkhanis · 02/12/21

@Dakota Szumilas What Mike is trying to say is that -- You should select both the Master and the Territory sheets (multiple sheets report) when creating the report and you will be presented with fields/columns from both the sheets to be displayed.

Mike Wilday · 02/12/21

Correct.

Dakota Szumilas · 02/12/21

That makes sense, I appreciate the suggestion. Unfortunately, that is still not useful to me, as I need to have those sheets connected to Zapier and it does not integrate with the reports.

If you have any other ideas on how to achieve it, I would be more than grateful.

Heather Duff · 02/12/21

Could you possibly use the Copy Row automation to copy a new row from the Master sheet over to the corresponding Territory sheet? It requires that all columns from the Master sheet be a part of the Territory sheet, but not vice-versa.

The only downfall there is that if updates are made on one sheet, unless you've got all of the cells linked, they won't update on the other sheet.

Dakota Szumilas · 02/12/21

Heather Duff

Feb 12, 2021

Could you possibly use the Copy Row automation to copy a new row from the Master sheet over to the corresponding Territory sheet? It requires that all columns from the Master sheet be a part of the Territory sheet, but not vice-versa.
The only downfall there is that if updates are made on one sheet, unless you've got all of the cells linked, they won't update on the other sheet.

@Heather Duff thanks for the suggestion. Master Sheet will be used by the admin for onboarding and it has some sensitive information that can't be a part of the territorial sheets. Thanks for the idea th

Andrée Starå · 02/14/21

Hi @Dakota Szumilas

I hope you're well and safe!

To add to previous excellent advice/answers.

Two other options. Dynamic View or Dynamic View Light.

1/ Another solution that probably would be perfect for this use-case is the premium add-on, Dynamic View.

More info:

Dynamic View

https://www.smartsheet.com/product/dynamic-view

Enable business owners to share information to the right people at the right time for viewing or editing with Smartsheet Dynamic View.

2/ I recently developed a (what I call) Dynamic View Light. It's quite advanced, but in short, it's using cross-sheet formulas and INDEX/MATCH and a specific structure and method, so it's only possible to show data that should be available.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support 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!

Formulas and Functions

Formulas and Functions

Equivalent of 'FILTER' function

Answers

Reports | Smartsheet Learning Center

Dynamic View

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA