Building Filterable Timesheet Dashboard

J Powers

I am attempting to create a dashboard that can be filtered to each employee for a time sheet. The employee will submit their time entries through a form, that populates a sheet with an approval workflow. At the end of the week, our admin team needs the ability to filter to the individual employee, and that pay period. I have attached a screenshot of what I would like to do in theory. I need to be able to filter the bottom sheet with the information in the top form (I only created the top form for reference and has no functionality). The metric summary information is something that I will also need to be able to filter by. Any assistance would be greatly appreciated. If there is any suggestion on how to attack this at a different angle, that would be welcome also. Thanks!

.

Debbie Sawyer

You could set up a filtering area on the smartsheet which allows a user to fill in the credentials they are looking for then the SUMIFS type function can look to the filter area for its criteria.

Very simplified version here:

Data table in Yellow; Filter area in Pink (with drop down lists to ensure accurate filtering) statistic area in green. This particular one is looking at the data and finding the total number of hours for the date range (inclusive) of 1st - 7th April for Debbie.

This shows the formula in the statistics cell.

I've made the example simple so that you can adapt it to your needs. Hopefully this explains how your scenario is achievable.

You could put the filtering and statistics on a different sheet to the data table and use the blue Reference another sheet option when building your function.

Hope this helps.

Kind regards

Debbie

J Powers

Debbie,

Thanks for taking the time to look at this. This looks like it will work for the Pay Period Summary. Now, is there is way that I could filter the report to just the employee's rows during that pay period. On the screenshot example I provided, about 5 of those entries is for one employee. When I filter, I would like to only see those five entries, rather then entries for all employees. Thanks!

Debbie Sawyer

In my MS Access days we used to have something called Query by Form. Where you select some criteria on a form and it could dynamically update a report/list of data on the same screen.

Unfortunatley I've not found that functionality with Smartsheet yet. (I believe this is what you are after)

The best I have been able to do in Smartsheet on this is to have a shortcut that takes you to a sheet where by you set the criteria for the filter, then on returning to the dashboard (via the browser back button) the sheet saves and the widgets on the dashboard will refresh and show the new filtered results.

Would this work for you?

Kind regards

Debbie