Executive Level Management View?

PureSPP_bizops

Hello all....

I am looking for a way to create a report or sheet that will auto-populate with the information in our main sheet. We use Smartsheet to log and track our teams activity in different "pursuits" our team is working on.

For example - when our SPP leader is updating a sales manager on the status of a pursuit, I'd like for him to pick from a drop down of all available (open pursuits) - then it populates the needed info for the executive view (not ALL info available in the "card" for that pursuit).

We'd think of this as a "pursuit on a page" and would be an executive level view. There also may situations where there would be multiple pursuits for one account - but I guess I need to figure out how to get 1 to populate before worrying about multiple.

Does anyone have any examples or templates how you've set this up before? After a Pro-Desk session, we talked through using Index Match formulas to populate the data, but I'm trying to find more visualization of this report first so I can frame my thoughts and functionality.

Thanks for your help!

Darren Mullen

@PureSPP_bizops what is stopping you from creating a report that is simply filtered on the pursuit column? The SPP leader could easily change the report filter as needed. The report would be based on your master sheet, but you could only show the columns you need to show. or... am I missing something that you are trying to achieve?

PureSPP_bizops

Thank you for your reply. I think I'm not explaining what I'm visualizing correctly unfortunately. I also think there may be some issues with tasks and sub-tasks and what we're looking for.

Originally - the team came to me asking me to produce something like this but have it auto-fill with data in Smartsheet.

I was hoping someone may have some advice or have created some sort of auto-generated report based on the info in our "engagement cards" below. Some are sub-tasks (but have their own cards).

So the team members put the info in the fields in the cards - then the manager can bring up a formatted report that can be summarized for an executive-level view using index-match formulas.

Does that help? Thank you again for all your guidance and suggestions.

Andrée Starå

Hi @PureSPP_bizops

I hope you're well and safe!

The way I read your questions now, I'd agree with Darren, and a Report seems to be the solution.

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

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 Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

PureSPP_bizops

Hi @Darren Mullen and @Andrée Starå !

Thank you for the replies.... yes, I agree that a report is the best way to go, but I think I probably didn't explain it as I should have. Also, I may end up running into some issues with the relationship between a main task and a sub-task, but I'll worry about that later.

Maybe this will help? (also - I thought I posted a comment, but it must have been deleted... so hopefully this one will work)

We are using Smartsheet to manage the activity of our team. Each member puts in info and fills out fields in the "card" view. Each card is an engagement and we may even have "master" cards with separate sub-tasks or phased engagements underneath. See below.

Originally - my management came to me and wanted to produce some sort of visual like the "Pursuit on a Page" and executive view below ... so that's I was thinking about creating a drop-down menu, then the rest of the fields will auto-populate based on the data in the card using index-match formulas.

I don't think a simple report with a filter would work - I need to create a graphic view and setup like something below that references and pulls data from the other (main) sheet (above). I may even need to create additional sheets for it to reference if I need to add more metrics-driven details that I would have to create in formulas (i.e. how many pursuits in a certain region/segment, etc.).

Hopefully that helps some.... I was mainly looking for some sort of inspiration on Executive Level views using data referenced on other sheets. I believe only Index-Match formulas will work, but if someone has another idea, please let me know.

Thanks again for all your help and suggestions! Have a wonderful day!

Andrée Starå

@PureSPP_bizops

You're more than welcome!

I'd recommend developing a solution structure you could show on a Dashboard. You could create a template and duplicate it for each Pursuit or similar.

I recently developed a Solution for a client where they have a form field (which could be a sheet dropdown instead) where they selected a salesperson that they wanted to see all relevant KPI information on, and when they submitted the form, the Dashboard would reload with the selected salesperson's information) (It's a fairly advanced solution, so it might not be an option)

What do you think?

Darren Mullen

@PureSPP_bizops Another option, if you don't need it to be on a dashboard, would be to use Document Builder to generate a PDF in the visual format that you want. It would have to be generated every time you wanted it updated, so that may not be feasible for your process. Otherwise:

1. You can do like @Andrée Starå mentioned.
2. Use a data visualization tool like Tableau or Power BI to build a filterable dashboard that has a connection to your data in Smartsheet.
3. Use another tool like Appsheet to make a dashboard with your Smartsheet data.
4. Develop a separate custom web interface and pull in data from the API... but that's getting out of vanilla Smartsheet and requires some development. I've done this sort of thing in Google Apps Scripts for example.

@Andrée Starå Not to hijack the thread, but In the solution you developed to update the dashboard with a form, I assume you collect the form answer in a sheet, lookup the data you need from the form entry, and then the dashboard pulls in data from the most recent row after refreshing the dashboard? If so, that is a pretty slick solution. I've seen this sort of thing done elsewhere, and I also just created a form based lookup on a Smartsheet dashboard for a client as well using the same principle.