Streamlining the workflow

AAL

Hi Smartsheet Team,

I hope this message finds you well. I've been working on a project in Smartsheet and could use your expertise in streamlining the workflow.

Here's a brief overview of the project: I've created a form to collect information from 60 individuals who will be reporting the number of hours they spend on various activities over a 6-month period. Each individual is required to complete a total of 20 hours across these activities.

My goal is to create a convenient and efficient way to build individual dashboards for each person. I'd like to seek your advice on the following approach:

1. Resource Sheet: Should I begin by creating a Resource Sheet with the 60 individuals, based on the information I collected from the Form? This sheet would serve as a basis for tracking their hours on various activities.
2. Individual Reports: Once the Resource Sheet is in place, I would then use this data to generate individual reports for each person. That means I will create a report based on this Resource Sheet - Group by Last name and then again copy and paste 59 of the same report. These reports would calculate the total hours spent on different activities for each individual.
3. Dashboards: Finally, I plan to use these individual reports as a foundation for creating personalized dashboards for each individual, displaying their progress toward the 20-hour requirement.
4. When I try to display the progress of each individual in the dashboard, what widget would be better to show the proportion? Pie chart? Completed vs In-completed Hours?

Do you believe this approach is the most efficient and effective way to achieve my goal, or do you have alternative suggestions that might simplify the process further? Your guidance would be greatly appreciated, as I want to ensure that I'm using Smartsheet to its fullest potential.

Thank you in advance for your help, and I look forward to hearing your insights.

Best regards,

Paul Newcome

Do each of the users have a Smartsheet log-in (doesn't matter if licensed or unlicensed user as long as they have a log-in)?

AAL

Those 50 people who need to submit the form have no license but we provided a link to them to fill in the form.

AAL

So is it the best approach to achieve my goal based on all those steps?

Paul Newcome

If everyone has a log-in (even if they aren't licensed), you can create a single set of reports/dashboards and have everything filtered by "Current User".

You can also leverage that to set your form so that it requires a Smartsheet log-in to be able to use it so that you can capture who submitted the form without them having to fill that field in which makes pulling metrics and whatnot by user much more simple as well.

AAL

Hi Paul,

Thank you for your advice. However, it's important to note that the 50 individuals filling out the form won't need direct access to the dashboard or reports. Access will be limited to myself and another staff member. Given this, do I still need to create 50 individual reports, each linked to its corresponding dashboard to track progress effectively?

I apologize, but I didn't fully grasp the meaning of your last paragraph. Could you kindly clarify or explain it once more for me?

Paul Newcome

My apologies. I was under the impression you were trying to provide this data to the users.

If it is just for you, I would include a checkbox column in the main sheet and then create a second sheet with a dropdown column that has everyone listed in the column properties.

Then you can use a formula in the main submission sheet with a cross sheet reference to automatically check the box next to whoever is selected in the dropdown of the second sheet.

From there you would adjust metrics sheets, reports, etc. to only pull in rows where this box is checked.

You can publish the dropdown sheet and then use a Web Content widget with the published URL to display on the dashboard. Once you select a new name in the web content widget and refresh your browser, all charts, graphs, etc. should then update to diplay to metrics for the selected person.

AAL

Hello Paul,

I appreciate your detailed response and guidance on this. I've followed your instructions up to point 1, where I included a checkbox column in the main sheet and created a second sheet with a dropdown column listing everyone involved.

this is main sheet

this is 2nd sheet (metrics)

However, I'd like some clarification on point 2. Could you explain the purpose of setting up a checkbox column in the main sheet? I want to make sure I understand the workflow correctly.

Regarding point 3, I gather that I should use the second sheet as my metrics sheet to perform calculations. This makes sense, and I'll proceed accordingly.

Lastly, for point 4, I intend to build a web content widget using the second sheet to create my dashboard.

Thank you again for your assistance. Your insights are proving invaluable as I work through this process.

Paul Newcome

The checkbox in the main sheet will check the box on every row that has the same student ID that is selected in the first row of the second sheet.

Think of this second sheet's top row of the Student ID column as the "filter" for all of your metrics and reports. You want the main sheet to flag every row that matches your "filter" on sheet 2 so that any reports and formulas referencing the main sheet are "filtered" to match that one student.

AAL

Thank you for the clarification, Paul. Just a couple of follow-up questions to ensure I'm on the right track:

1. If I understand correctly, when I select a specific student ID in the dropdown on the second sheet (let's say 12345), it will automatically check the corresponding checkboxes in the main sheet for every row with the student ID 12345. Is that the intended behavior?
2. Regarding flagging rows that match the 'filter' on sheet 2, do you have any specific formulas or functions in mind that would help me achieve this? I want to make sure I'm setting up the filtering process correctly.
3. Additionally, my dashboard will be simple, showing how many hours are complete and how many remain. To accomplish this, how many formulas do we need to build here, including steps 1 and 2?

Your guidance is much appreciated, Paul. Thank you for your patience in explaining this process."

Paul Newcome

1) Yes, but only after you save the selection sheet with the new selection.

2) It is going to depend on exactly what data you want to show and how you want to show it. Sometimes reports can be used in which case you would include a filter in the report for the checkbox column being checked. Other times you have to use formulas in which case you would adjust them to include a range/criteria set of the box being checked.

3) Piggy-backs on #2. How are you determining each? Is there a field in sheet 1 that has how many hours have been completed for each submission, is it a set number per form entry, etc.?

AAL

Hi Paul,

Thank you for your detailed response. I'd like to share my metrics sheet with you and walk you through my plan based on your guidance.

Below is the metrics sheet. I click the drop-down menu and pick the student id no. 2235718.

Below is the main sheet. As you can see, when I select a student ID in the dropdown in the metrics sheet, it checks the corresponding checkboxes in the main sheet, indicating their submissions. For instance, Adam has submitted his work twice.

Metrics Calculation:

Now, my challenge is how to transfer these entries back to the Metrics sheet. After I transfer these entries back to Metrics sheet, I will calculate the total hours the student earned. As they have to complete 40 hours. So for Complete Hours column: =40-Total Hours, Incomplete Hours: =40-Complete Hours.

Additionally, is there any way in one dashboard, I can click on the student ID no, then it will show the individual student's complete and incomplete hours in a pie chart? Then I don't need to create 58 student dashboards.

Could you please guide me on how I can implement this in my Metrics sheet? Your expertise is highly appreciated.

Thank you for your assistance.

Paul Newcome

For this particular metrics, you won't need to reference the checkbox. You can use a SUMIFS to get the total from the Hours column based on the ID column.

=SUMIFS({Hours}, {ID}, @cell = [Student ID]@row)

That will give you the total hours completed. Doing 40 minus this total will give you the remaining. Then you can use these two cells from the metrics sheet to generate your pie chart.

From there you would publish the dropdown sheet as editable by anyone and use a Web Content widget on your dashboard to display this sheet.

Then you will be able to select a new student ID from the dropdown right there on the dashboard, save the sheet in the web content widget, then refresh your browser window to see the updated pie chart.

AAL

Thank you Paul, I did it. Thank you so much for your great assistance.

This is my dashboard. Do you know if there is any way if I add the title with the student's name, can I refresh the browser and the name will be updated as well?

Paul Newcome

The title of the dashboard itself will not update, but you can use a Metrics widget and format it so that it looks like a title bar across the top.

AAL

Thank you, Paul. I made it. 😉

But I find the dashboard is kind of narrower than before. Is there any way I can make it wider on both sides?