Automation column to column

jonathan carlone

I would like to automate the behavior of columns.

My case is to create a list of our department's contacts as task assignments and in a separate column note the team the person is on, so, for example, Jane Smith is on the Marketing Team, and John Doe is on the Operations Team.

Then I would create a USER REPORT for tasks but would also like to create a TEAM REPORT for leaders.

So is there a way when Jane Smith is assigned on task another column would automatically fill with her team?

JamesB

@jonathan carlone

I assume you have more than 2 teammates. In this scenario, I would recommend that you create a sheet that has a list of the teammates in one column and their department in an adjacent column.

You can then use an INDEX/COLLECT with a sheet reference to populate the department from the assigned user.

In this example, the name of the new sheet is Team.

=INDEX(COLLECT({Team Range 1},{Team Range 2},Assigned@row),1)

Team Range 1 = The Column with the Team Names

Team Range 2 = The Column with the Usernames

Hope this helps.

john.lamirande

If you maintain a list in Smartsheet of each person that could be assigned a task (e.g. Employee Roster), and it includes what team they are on and who their leader is, you should be able to do an INDEX MATCH to automatically pull that information from that employee roster. That roster could be imported as a .csv file from your HR system to simplify maintenance. It is important to note that the task assignment employee name will have to exactly match the Employee Roster for it to find a match, so you may want to consider a dropdown list on the task assignment list to ensure that match is perfect. Hope this helps!