Mastertask tracker and subTasks

Samplease

Hello everyone, I've been dealing with Smartsheet for a few months now and is a great tool.

So, my Boss requested to set up Master Task sheet where he can add tasks for team members and set up to each team member a subtask sheet. He wants also to be able to add tasks and for the task to show up straight away on the sheet it assigned to. Also, if a team member modifies status, comments, task health etc on the sub task that will modify the Main Task sheet.

So i tried to :

Create sheets but from what i understood you cant link entire sheets from a master task sheet.

I tried to use reports for each team member and stakeholder instead of task Sheet and filter by assigned to so each person could see only their task (which is really important for confidentiality reason ) i though i nailed it ! but apparently people cant modify report if they are not invited on the main task sheet .

Is there a way ?? i really cant find a solution .

p.s my apologies for the typos english isn't my main language

MichaelTCA

Hello @Samplease

I understand the issue for confidentiality. The only way to restrict views but still allow editing would be through third party permissions (in this case automations) or software add-ons.

You could set up an update request automation, on a scheduled or a triggered automation, that sits in their inbox until they're ready to update the progress.

Otherwise, like you said, they would need permission to the sheet and report in order to manage/edit all the work required for the task.

Hollie Green

Will this be for just a few team members or for a lot of team members?

Matt Johnson

Hi @Samplease

If your company is on the Enterprise plan, Work Apps allows users to edit a report, within the Work App, without having access to the Sheet.

I hope that helps.

Matt

Samplease

@MichaelTCA

Thank you for your Answer.

So if i understood properly Smartsheet's built-in features are limited when it comes to enabling team members to update a master task sheet directly from their personal subtask sheets without having direct access to the master sheet, right ?

Which do you think is the most practical way for someone like my Boss that has to deal hundred of stakeholders and Team members in different projects?? The thing is i would like to save his time by avoiding all the ( pardon my newbie language) "programing".

At this point maybe the easiest way would be to create directly the subtask sheet for each member ( where he would have to be adding tasks) and link each one of the subtasks sheet to one or + reports. What do you think ?

Samplease

@Hollie Green

Thank you for taking time to help Hollie.

Would be for 10/20 Team members and probably externals.

Hollie Green

I am not aware of an easy solution and you may run into cross reference limitations depending on how many columns you need updated on the master tracker. But it is possible if the cross reference limitations do not block it and you have the bandwidth to build all the needed formulas.

Set up an Automated Row ID on the master tracker and create a subtask sheet for each individual and set up automations to copy the row to the individual sheet anytime a new task is added for that individual on the master tracker. Then on your master tracker set up formulas that will Index(Collect the data you need from the individual trackers. It will be an extremely long and complex formula with adding Index(Collect formulas.

Below is an example of the index collect formula to bring in a Date Assigned on the Master Sheet you would have to continue adding the formulas and creating the references for each team member.

=IFERROR(INDEX(COLLECT({Date Assigned Team Member 1}, {Row ID Team Member 1}, [Row ID]@row,{Team Member 1},[Team Member]@row), 1), "")+IFERROR(INDEX(COLLECT({Date Assigned Team Member 2}, {Row ID Team Member 2}, [Row ID]@row,{Team Member 2},[Team Member]@row), 1), "")+IFERROR(INDEX(COLLECT({Date Assigned Team Member 3}, {Row ID Team Member 3}, [Row ID]@row,{Team Member 3},[Team Member]@row), 1), "")

I would also recommend if you use this option that you set up an automation to Move rows once all the data is filled out and will not be changed or move all completed tasks that were completed a set amount of months back to a back up sheet so the sheet will not get too big for all of the cross reference formulas.