Tracking Employee Effort Across 170 Projects

RLH02

I have a little experience using Smartsheet in my previous role at FSU and am now trying to help them adapt to some (unforeseen at the time) scaling and relational issues on the sheet/form I built. Specifically, this tool has been implemented as a way to track effort/time on a large 5 year federal contract.  

Our current dilemma centers around the scope and size of scaling this across 170 individual sub-projects. The tool worked well when we had ~60 projects for year one, but as we added placeholders for future projects in years 2-5, the number of columns in the main sheet and logic limitations on the forms have hampered our ability to grow this in a way that keeps the user experience simple and clean. 

Some helpful bg info:

1) ~20 Employees use the form monthly to submit effort (which must total 100%) across 170 projects.

2) On average, most employees only claim effort against 5-7 projects

3) About 3-4 employees may claim effort on up to 30 projects

4) Not all projects are active. Typically, the number of active projects at any given time is ~30-40

5) Currently, we have a defunct project list that needs updating. It does not have a start/end/active status column but I could see this coming in useful once updated

6) Projects have names and a sequential numbering system. Placeholder projects are titled TBD and we have encountered issues with logic not taking name changes in the past, breaking the form

7) The form has a multi-select box to select the projects they wish to claim effort on, which populates the corresponding box to input effort associated with that project...The logic worked fine with 60 but broke when we scaled up

8) Every one project column has two calculated columns, which caused column limits. The original purpose of the calculated columns was to feed other sheets that connect to a dashboard. We have decided to forgo those calculated columns given that we output these data to excel and can perform the calculations there instead.

I've played with another version of the form that asks them to select how many projects they need to report effort against, but again hit logic limitation issues.

If you've made it this far and your eyes aren't crossed...Thank you! I would welcome your thoughts on how best to make this tool more efficient and suit our needs.

Paul McGuinness

Hi @RLH02

Thats an interesting issue, with the sheer volume I would possibly drop the webform and create a master sheet with the 170 projects listed in a column and the 20 colleagues listed as individual columns.

You could then build 20 separate reports, 1 for each colleague that only has the project column and name column. Reports remove the possibility of filling in someone else's effort. (NB - if they are licensed users you could do this in Work Apps to be even tidier)

The defunct list when tidied up could be used to filter out expired projects to reduce the number of rows in the report per person to make the completion easier, or if you have certain colleagues only working certain projects you could add them in an additional column on the master sheet and have the report filter on that also as that would only present the projects that apply to the colleague specifically?

That master sheet can then be used to populate whatever reporting you need via formula if you then need to change that colleague by column data into row data etc.

Just a few initial thoughts that may be of help.

Thanks

Paul

RLH02

Thanks so much Paul for your feedback and idea...I like that! I'll play around with this...Going this route then, with the form being out of the picture, each employee would then have access to their own report they could edit. My knowledge on reports is limited, and I could likely find the answer with a little digging/experimenting of my own, but had thought reports didn't allow direct editing. If they can edit it in a sheet format themselves though and have that roll down into one master file, that would be ideal.