Help dynamic dashboard

Charlotte Patey · 11/11/20

Hello,

I'm looking for some help setting up a dynamic dashboard.

I have a sheet per project with various financial and activity information and I want to create a dashboard where a project manager can enter their project number onto a dashboard and see summarised information for that project.

I have tried creating and putting a form onto the dashboard where a user can enter the project number and the sheet summary report will have a filter that matches to this, each of the project sheets will have an;

If (project number = filter number, "Select", "Ignore")

formula but these individual sheets do not seem to update in the background so when a user submits the form the information does not change even if I refresh the dashboard or reload it. I seem to have to go into each sheet before it updates.

Does anyone have an ideas about another method?

Or can someone tell me if I'm doing something wrong with my current plan? I'm new to Smartsheet and extremely new to sheet summaries.

Many thanks,

Charlotte

Charlotte Patey · 11/13/20

Just a note to say - I have a solution to this.

It's not perfect but it's working and fairly quickly.

Thank you for reading.

Charlotte Patey · 11/13/20

https://community.smartsheet.com/discussion/comment/268739#Comment_268739

Hi Genevieve,

Of course, I wouldn't have been able to get there without help, in particular a post from Paul Newcome who describes how to do this when you have more than one user-input (I confess I got lost with that but the process is the same).

To summarise - a user inputs a project number in a form on a dashboard, then they refresh / reload the web page and various reports change to show information linked with that project number.

First the setup:

A sheet I called Filter Request:

Row ID is an autonumber
Project Number is added by users via a form (I selected to put new entries on top)

The data sheet (which for me is a collection of many individual sheets grouped onto one with manual cell linking because you cannot put formulas onto a report) but any sheet works.

The formula for the highlighted cell is:

=MAX({Filter Request Range 5})

So this is the most recent Row ID from the User Select sheet

The formula for the first row of User Select is:

=INDEX({Filter Request Range 6}, MATCH([Project Number]@row, {Filter Request Range 5}, 1))

This is the project number that matches to the row ID above

The formula for the rest of the User Select is:

=IF([Project Number]@row = $[User Select]$1, "SELECT", "IGNORE")

This is a helper column that identifies if the row matches the user input.

Next I created a report that only selects rows where the User Select column is 'SELECT'

Then I added the form and report/s to the dashboard:

A limitation is that users must refresh the page after submitting and in my set up users can only input one type of data.

This was a day and a half of ~~pure pain~~ and my colleagues won't ever know 🤪😂

Hopefully this is clear and helpful for others and if someone has a better (simpler) way I'd love to hear it especially if it involves using a report instead of a sheet with hundreds of rows of manual cell linking.

CP

Charlotte Patey · 11/13/20