Using Control Center to add filter to a Report

Julie Sutton

I have a sheet of subscriptions for all our customers being populated from another system. This is a 1-many list. I want to create a sheet with only the selected customer's data and use that in the control center blueprint. The end result would be a sheet with only 1 customer listed in the sheet created by control center. But, I can't seem to figure out how to 1 pull the data from a source sheet that meets a specific criteria and then also use that as a variable in the blueprint process of creating a workspace for each customer.

Any ideas?

Genevieve P.

Hi @Julie Sutton

What about setting up this sheet to have a cross-sheet INDEX(MATCH formula? Here's another Community Post that goes through how to create INDEX(MATCH formulas.

The Matching value would need to be content provisioned by Control Center (ex. the Customer Name, perhaps cell-linked in from another sheet in your Blueprint). Then the cross-sheet references can point to your external sheet and pull back data that matches that name.

I haven't personally tested this set-up with Control Center, so let me know if it works for you or not!

Cheers,

Genevieve

Julie Sutton

So i use index(match) when I know the cell the data is I need to pull and it's usually a set amount of rows that I'm pulling back a value for. What do I do when I don't know how many rows need to get pulled back? For another example, I have a combined contact list of all our customer contacts, multiple contacts per customer company. Say I want to pull back all customers matching the company name (this is a field in the contact list). Index(match) would need to know a matching condition to pull back but I don't know how to do that for an unknown amount of rows. I basically want to filter the entire contact list for only those customers that match the company in question and add them to another sheet. I'm not sure index(match) would work.

Genevieve P.

Hi @Julie Sutton

Thank you for clarifying! In this instance you could use a JOIN(COLLECT instead, but this will bring back all matching values into one cell instead of parsing them out down a variable number of rows. There isn't currently a formula that can parse down data like this, you would need to create a Report if there are an unknown number of rows/data to bring back and you want them on separate rows.

I would perhaps suggest booking a Pro Desk session (if your plan has access to it) on Control Center so you can show your current set-up over screen share to discuss best practices, see: https://www.smartsheet.com/pro-desk

Cheers,

Genevieve