Insert Row: Automatically Links Cells

Kelci Peterson

I am trying to figure out a way to best organize my project tracking...

For example, my company has four big projects coming up, let's call them projects A-D.  We have organized our sheets by project, and then created various sections within the sheet for each team working on the project, with sub-sections for task categories and the respective sub-tasks. Let's call these teams Team 1-7.  

My ultimate goal is to share with Team 1 ONLY the categories and tasks that apply to them across all four projects.  So what I thought would be best would be to create new sheets, for Teams 1-7, copy the cells from Projects A-D that pertained to their tasks, and then link the cells so that changes Team 1 makes to the Team 1 sheet also shows up on the Project A sheet (or any sheet for Project A-D).

The problem comes with inserting rows... Say I only created tasks that I was aware of.  When a member of Team 1 reviews the sheet, they see I forgot four tasks.  When they add these four tasks to the sheet "Team 1," (they do this by creating new rows), those new rows do not link to the sheet "Project A".

This was also the issue when I was considering using the "update request" feature; you can send certain rows from your sheet to collaborators so they can only see those certain rows, but I don't think the update request feature allows you to add rows.  Which is functionality I need.

Can someone explain a solution that may help?  If this is not possible, I strongly suggest adding functionality in an update where rows added from a source sheet - under a linked section - automatically are added to the sheet the cells were linked to.

Kelci Peterson

Also confused as to why "finish" and "predecessor" columns on a project sheet will not link.  if my team member wants to input a predecessor under their sheet, it will not show up in the sheet I have linked it to.

Not sure how to navigate this feature to meet my needs.

Travis

Hi Kelci - I would looking into our reporting feature. Reports allow you to pull in rows from sheets based on criteria you specify. You could have a master sheet (or multiple sheets) with all your tasks on them. Then each team member can have their own report which automatically pulls in all the tasks assigned to them across all their sheets (or just a single sheet). The report will stay up to date and will include any rows that are added to the sheet(s) that meet the given criteria. Reports are better than cell linking for this situation because they will automatically include new rows that meet the criteria and rows are editable within reports. Check out this help center article on reports: http://help.smartsheet.com/customer/portal/articles/522214-creating-reports

 

For your second question, Finish Dates are not included when linking cells because there are background calculations that are performed in the underlying sheets that could interfere with the settings of the sheet the row is linked to (such as working days). If you have dependencies enabled in the destination sheet and you link the start date and duration cells, the finish date will automatically be calculated. 

 

Predecessors are specific to each sheet (Task 2 may be on row 2 in one sheet and row 5 on another) and there are background calculations which are taking place and could cause interference with the data. 

Kelci Peterson

Travis,

The reporting feature does seem pretty useful, although I'm still not sure if it's exactly the solution I'm looking for.  My needs are:

-I need to have one master project sheet (with a Gantt chart) showing tasks and progress across all teams.  I will report on this weekly to my CEO.
-I need to have a capability for all my teams to collaborate on sheets involving their tasks; at this moment, our sheets are separated by project, not team, in order to meet the above requirement.  However, I do not want Team A to be able to view or edit Team B's tasks, which is why I initially considered update requests on the project sheet, or creating separate team sheets and linking them to my master sheet.
-I need to have a way for teams to be able to input or change data and have that be reflected on my master project sheet.  Yes, the reporting would be almost perfect, but it looks like it is missing the capability to include a Gantt chart, which is something my CEO has required.

That being said, is there anything else that may help?  

Travis

It sounds like reporting would work for you. Rather than a master sheet, you have a master report that pulls in rows from each team memebers sheets. You (and your CEO) can view the report and all the sheets as needed, but each team member would only need access to their own sheets. You can even share the master report with them, and they would only see the rows from sheets they are shared to.

 

The problem - you need a Gantt chart. I have good news! Our next release will include Gantt charts and calendars in reports! There is not a set date for this release but I would expect it within the next month.