Filtering on dashboards?



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 03/02/20

    @Lajaun Case

    Try making these adjustments...

    Sheet Summary: Category#

    =INDEX([Project Requests Metrics Sheet Range 3}, Row#)

    Sheet Summary: [Filter Checkbox]#


    Main Sheet: [Filter Checkbox] column

    =IF(FIND(Category#, Category@row) > 0, 1)

    EDIT to tag Lajaun.

  • @Paul Newcome

    Thank you so much for your help. I have everything working except on piece. On the Sheet Summary, I have Row as =COUNT({Filter Requests})

    But, when I put a new entry on the form, it does not look at cell 1 for the status. It continues to pull the previous value which is now in cell 2. (Even if I refresh the page. It is like it is hard coded to the previous value.)

    On the Sheet Summary, if I "Edit Reference" the Row field and click on the Filter Requests sheet, Status column, row 1 cell, then it updates and all of the checkboxes update. How to do force the Row field to look at the first cell only?

    Thanks again!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Lajaun Case If you are trying to pull the top row only, then you would not use a COUNT function in it's own cell. You would adjust your INDEX function in the Status field to

    =INDEX({Other Sheet Status Column}, 1)

    This will hard code the number 1 so that it always pulls from the first row.

    Does that help?

  • @Paul Newcome

    I got it working!!! I wouldn't have been able to do it without your guidance. Thank you so much for taking the time to assist!!!!

    Lajaun Case

  • @Paul Newcome - I read through the thread and was wondering if you can assist me with my similar requirement? Should I start a new thread or add this? What is you preference? My requirement is a bit different as I would like users to be able to choose the project type from my master schedule.

    1. Master Schedule has up to 100 project types they can choose from? lets look at an example like. "CORE Implementation" Project type.
    2. There are standard tasks that I want included in all project plans. These are repeatable tasks like welcome call or kick off etc. No matter what project type the user chooses I want these standard tasks / sections to always display.
    3. I want users to be able to choose the project type i.e. CORE Implementation
    4. I want user to enter a go live date
    5. I want the standard tasks to display for the user
    6. Once steps 3,4 and 5 are done, I only want the information related to thee tasks to display.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Harley Esguerra I think your request is different enough to warrant a new thread. Please feel free to @mention me so I get notified of it, as I do think I might have a couple of ideas. If I understand what you are asking correctly, I do something very similar, but I have just under 20 services that could be mixed and matched for an implementation.

  • @Andrée Starå I'm interested in your #3 from the first option up top. Any chance you could provide details on how to do a drop-down filter? Totally fine to reload after every change.

  • belz
    belz ✭✭

    Hi @Paul Newcome, I am new to Smartsheet.

    We are trying to build a centralised activity calendar that could be shared between faculties and central departments. I am trying to adapt your solution above to what I am trying to build.

    Basically, I have a data sheet ("Intl Engagement Calendar 2020 with Form") with all the information we would like to capture with regards to an activity (ie dates, month, country, activity type, purpose of activity, faculty required etc). I have set up the Summary Sheet in my data sheet, created a Filter Request sheet as well as a filter request form.

    This is how the Filter Request Form and the sheet that I am filtering is displayed (side by side) on my dashboard.

    My questions are:

    1. Is the checkbox column (Column31) meant to show "#BLOCKED"?
    2. When I fill out the form and submit it and then refresh my browser/the dashboard, nothing happens. The sheet still looks the same.
    3. What am I doing incorrectly? Any advice you could give would be much appreciated. Many thanks!
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭


    The #BLOCKED error means there is an error somewhere else that is preventing the formula from running. Work backwards checking all ranges referenced in the formula in [Column31] looking for an error. Let me know what you find.

  • Hi @Paul Newcome , thank you for all of your help so far. We had to switch gears due to covid, but are starting back up on our dashboard listed above. Is it possible to filter a report or only possible to filter a sheet with the option given above? We have multiple sheets going into a report and would like to filter the report. If it is not possible to filter a report with many sheets are there any other alternatives we can look into? Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Filtering can only be done at the sheet level. To "filter" a report, you would need to adjust your criteria in the Report Builder.

    What exactly do you want to filter your report on?

  • Please see above responses in this thread from 2/25/20-3/2/20. @Paul Newcome

  • We are wanting to use the dashboard "filter" workaround to filter a report on the dashboard when the page is refreshed.

  • @Andrée Starå - I quickly scanned this thread and did not see the answer the original 3rd options. "A selectable drop-down that could filter what data to shown on the Dashboard...". My use case - I have multiple workstreams in the same plan. I am building a status dashboard which will pull in several types of data but let me just use two for this use case: plan data and risks and issues. I want the drop down list at the top of the Dashboard to be "visible" to other report objects on the dashboard page to pickup the workstream filter and re-render just downing plan data for the a given workstream and the risks/issues data for the workstream. This way I ONLY have to build a SINGLE Dashboard rather than 9 dashboards of the exact same type which results in a bunch of re-work if there are desired changes to the main structure of the dashboard. Is this possible?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!