Best way to layout sheet for multiple user data entry and charting

Vatechtigger · 10/23/18

New to the program so am looking for some guidance to head down the right path.

Lets say I have 10 different metrics that need to be updated once a week for 52 weeks. Each metric is owned by a different employee. I want them to enter their data, not send to me to enter. At the end of the day I need to be able to print one full page chart for each metric to display in a project control room. Would also like to be able to have each chart in a live dashboard on smartsheet.

Some options I think:

Create a sheet with each metric in a different row, with the 52 weeks as columns. This way I think I assign a user to the row and send them requests to update, etc.

Have each metric in a column, with the weeks a different rows. Not sure I can assign a column to someone, but perhaps give everyone access to the sheet and hope they don't mess with someone elses stuff.

With both of the above options, I think I can create a dashboard tab with one chart targeting whatever data I want, be able to print them, or also pull the data into an overall dashboard that has all the charts in one smartsheet dashboard.

Is there a better option of the above, or something else entirely? Any direction is appreciated. Thank you.

Andrée Starå · 10/23/18

Hi,

It sounds to me like option one would be the best choice. That will make it easy to request updates. Another way could be to send reports where they can update the metrics.

I'll get back to you if I have any other ideas.

EDIT: One more option could be to have a weekly update request or report and then you or someone else could copy the values to the correct week.

Do you have any material that you could share on how it works today?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

L_123 · 10/23/18

It depends on how much effort you want to put into it. I have developed a system for weekly submissions that uses updates and forms.

The columns go

responsible | Task | time to complete | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10| Created

I use an update request on each of the tasks that has a link to a webform in it. The user clicks on the webform link to submit the task. These submissions go to hidden columns, that are then parsed out. This handles weekly, biweekly, monthly and any other duration of repeated tasks. Then I have formulas in columns 1-10 that parse out the submissions into periods.

The benefit of this is that I have almost no sheet maintenance. I don't have to go in and update this every year. all of the information is summarized quickly for the last 10 periods, whichever duration it may be. It also has built in forgiveness. If the task wasn't performed in the past, it is forgotten about after 10 periods, though the information still exists if I desire to go looking for it.

This is a rather complicated way to do it, but I have found it to be highly beneficial, and have used it for cleaning systems in many environments.

Vatechtigger · 10/23/18

Nothing to share because it doesnt work today. Basically I send out a master excel sheet to the various people, and they update their section. however, since the updates occur at the same time, we end up with multiple versions that need to be collated which is cumbersome.

Can you explain "Another way could be to send reports where they can update the metrics"?

Best way to layout sheet for multiple user data entry and charting

Comments

Categories