Dashboard - Select various sheets

mproudfit · 01/13/21

Hello, I am wondering if it is possible to have a dashboard view with a dropdown for various sheets within a workspace. I have multiple different project budgets - all using the same templated budget form and Id like to create a single "Project Dashboard" where I can analyze the project-specific data upon selection from dropdown. I hope this makes sense and I also hope it's possible.

Paul Newcome · 01/13/21

I can't think of an easy way to make this happen with dropdowns, but you could include links to the sheets on the dashboard.

mproudfit · 01/13/21

https://community.smartsheet.com/discussion/comment/274998#Comment_274998

Hmm. Im not opposed to this I suppose. It may become cumbersome once project load ramps up. Is there any way to automate this (i.e., links are added to that widget within dashboard view when a new sheet is added)?

Paul Newcome · 01/13/21

Not necessarily. Are all projects going to be saved in the same folder or workspace? If so, you could provide a link to the folder or workspace URL.

Another option if they are all part of the same workspace and all using the same template. You could add a hidden checkbox column and check the box only on row 1.

Then you can create a report that only shows the sheet name column and have it pull from the entire workspace every row from every sheet that has that particular box checked. Since it is only checked on one row for each sheet, it will basically list each of the project sheets one time. If they are all in the same workspace, the report will automatically update as new project sheets are added. You can then put this report on the dashboard.

mproudfit · 01/13/21

Yes, they will all be part of the same workspace.

Im currently working from the following structure:

Client workspace

Project Budget Template
Master Program Overview

Ideally Id like to automate the copy of data from a newly created project budget (using the supplied template) into the Master Program Overview but I dont really want to copy entire ROWS but rather certain CELLS of data (and this would be consistent across each newly created project budget).

Sandra Guzman · 01/13/21

@mproudfit I actually like your suggestion of being able to have a dropdown feature within the dashboard. This could be helpful when reporting on more than 100s of active projects. Even having the automation of links uploading to a widget is a great suggestion, however it is not a function that is currently available.

I am currently exploring data analytic and visualization tools = self service BI (Spotfire & Tableau).

I would suggest submitting a product enhancement request by submitting your ideas via the enhancement form by clicking here.

All the best,

Sandra

Paul Newcome · 01/13/21

Ok. My suggestion would be the hidden checkbox column built into each of the Project Budget sheets with only the first row checked.

Then build a report and select the workspace as your reference. Filter the report to only show rows where that hidden column is checked. Put that report on your dashboard.

mproudfit · 01/13/21

https://community.smartsheet.com/discussion/comment/275010#Comment_275010

so I tried this and Im striking out, I think. I have my (for now, visible) checkboxes and I want to copy the data from those "checked" rows into my master program page. I go and edit the data in the budget template but over on the Master Program page, it only copies in the column names from my Budget Template with no data included. There has to be a way to automate the transfer of simple data but I just cant figure it out.

Paul Newcome · 01/13/21

I thought you were just trying to get a dynamic list of links to each of the sheets?

Andrée Starå · 01/13/21

Hi @mproudfit

To add to Paul's excellent advice/answer.

Here's another alternative.

I've developed a solution that might help.

You'd select what you'd want to see by using a form and then refresh the Dashboard.

It's a rather advanced solution, and depending on the specifics, there need to be so-called helper columns added and more.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

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

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

mproudfit · 01/13/21

Well, I did shift directions on you a bit. Im struggling through both needs at the same time and they kinda overlap.

Paul Newcome · 01/13/21

@Andrée Starå That was my initial though using a form and refreshing dashboards, but I am trying to keep it as simple as possible. Plus if multiple users are submitting forms, it could end up showing the wrong person the wrong data.

@mproudfit What is the overall goal?

mproudfit · 01/13/21

https://community.smartsheet.com/discussion/comment/275039#Comment_275039

My overall goal is to somehow find a way for a newly created "Project Budget" to add its corresponding data back into my Master Program page. I tried to save the template with reference formulas but when I "use template" they are no longer there.

Paul Newcome · 01/13/21

I personally use hidden helper columns and cell linking.

I list each data point in its own cell in row 1 of the hidden columns in the same order as the columns in my master sheet. I can then cell link from the master sheet to this hidden row in one go so that it can be done relatively quickly.

I have also attached very detailed written instructions with lots of screenshots to the template so that other users can follow them step by step then delete from their sheet.

mproudfit · 01/13/21

I just cannot figure this out. I tried to setup an automation workflow to copy helper cells (see below) into another sheet once rows are modified/changed. However, this ends up creating duplicate entries (see above) and not merely updating the existing entry. When I update the project budget form I just want that to show in the Master Program sheet.

Paul Newcome · 01/13/21

Yes. That's why I use the cell linking instead of an automation.

Dashboard - Select various sheets

Answers

Categories