Using Formulas in "Assigned to" collumn?

cbrucelaw

I'm a newbie here so don't be too harsh!

 

I am trying to determine if if is possible to set up a dependancy to auto-assign a task to a team member. At my law firm I am creating a few templates that assign certain tasks on each case to one of several paralegals or attorneys.

 

What I'd like to be able to do is designate in a certain part of the sheet which team member is the paralegal/attorney on a case and then have the sheet auto-assign each paralegal/attorney task to that designated team member.

 

I can do similar functions when setting up date dependancies, but the sheet does not seem to work with assigning team members to a task, which results in having to go in and manually assign 100+ tasks each time a template is used.

 

I contacted support, but they suggested using an IF formula without specifics and then suggested posting here.

 

Has anyone confronted this and came up with any type of work around?  It seems like that the ability to set up dependancies to assign users would be about paramount for those who desire to use templates over and over for repetative tasks.

 

Any help would be appreciated!

JohnHinkle

First, it's not possible to add formulas to Contact List columns. 

 

As far as creating dependencies for contacts between tasks - how did you want this to work?

 

For start/end dates and predecessors, a task's date is dependent on the date of another task. I am confused how this would work for contacts. 

 

Where would it get the correct contact name from? What would trigger the name? How would it know who to assign a task to?

 

Could you give us information about how envision this working?

J. Craig Williams

cbruce,

 

I second John's queries. 

 

If you are using only the name (for a report for example), then you could change the 'assigned to' column type to Text/Number.

If you need the email address or are using any of the alerts/reminder features, then we need more info to give you a better answer.

 

Hope this helps.

 

Craig

Dan Brenner PMP

I just ran into a need for this as well.

 

We've got a 170 task project plan template and there will be multiple tasks assigned to each member of the project team.

 

The inability to put a formula in the assigned to column means that Instead of being able to assign only the first "parent" row for the entire project to the project manager and have formulas automaticaly fill in the other tasks, we'll have to manually change the assigned to name in 45 tasks.

 

The alternative would be to add another column and put the formulas in there, but then we lose the resource tracking feature which looks like it will be a REALLY nice feature when it comes to reporting our workload to the c-suite.

 

It's not the end of the world... but it'd be nice to live in a world where we could eliminate these 45 clicks 

Mattisphere

I was using formulas in contact columns just fine up until yesterday where all of a sudden everything was replaced with #CONTACT COLUMN DOES NOT SUPPORT FORMULAS. I just submitted a help ticket.

Roland Horwood

Hi,

 

even just the ability to put "=a cell" so it would show the same contact as another cell would be useful. I have some project sheets linked to a summary sheet. I have all the links in a nice set of hidden intermediate columns to help make the linking easier. I can't link the contents of a contact column to the master sheet via this intermediatary without losing the "contactness" of the data. I then can't use all the nice alert and notification features from the summary sheet. Bit of a pain.

 

Mattisphere, did it really work previosuly? Did you get any reply to your help ticket?

 

Roland

Mattisphere

Yes, it really did used to work. The response to my ticket was that it was not supposed to work that way and the reason it worked for me was due to a glitch. They corrected the glitch which broke all of my contact column formulas. To speed the process up of manually entering all of the contacts, I now have a text column referring to the contact types which contains formulas and a separte Contact column type. I save filters for each contact type (Engineer, Project Administrator, etc...) so I can see only the rows for those contacts. I enter in the name once, copy it down, change the filter to the next type, enter the contact name, copy down, etc... At least this way it doesn't take as much time, but it's still annoying to do it a hundred times just to be able to use the reminders/notifications features.

ker9

Adding my vote for the ability to use a formula to assign a team member based on another cell (contact cell) in the sheet.

scottlove

Adding my vote as well.  Similar to the above comments, it would really speed up the Onboarding/Project set up time if I could automate (via formula) who gets assigned to a task.

I like the filter Idea mentioned earlier for a workaround but trying to cut down on the clunkiness

J. Craig Williams

scottlove,

I submitted a Product Enhancement Request for this. You should too.

Craig

Tricia Rice

Another workaround for anyone seeking a solution.  

I have opted to create templates which assign the role name in the "Assigned To" column. This allows me to quickly use find and replace to update several hundred rows of tasks for multiple resources within minutes. 

 

Just ensure you tag the variable differently than any term you would not want to be replaced.

Example of placeholder to be replaced with name: <Project Manager> 

Mattisphere

Tricia, I was under the impression that using find and replace wouldn't necessarily associate the contact field with the correct contact for reminders or other actions - i.e. you can't paste in a list of names and expect the contacts column to work properly. I could be wrong

DillonHug

Adding my vote as well. My issue was cell linking contact cells all individually. I thought I found a work around by making all my tasks that need to be cell linked indented by a parent row and changing the Contact column to text/number and cell linked an email address to the parent row with using formula =PARENT as all the child tasks but then I realized I could not set up alerts based on a text email and it need to be a contact which gives me an error if I try and change to contact.  

James H

+1 for me please.

I have a scenario where the user enters the project ID and it auto-populates the Project Manager's name based on a lookup table on a reference sheet. The idea was then to notify the PM that something had been assigned, but this is not possible since the notification requires a contact list column which I can't place the formula in.

sas-ss

+1 vote for this. Same problem. I have a sheet that should auto-populate the assignee based on other column data in that row. Once it's auto-assigned, the assignee should be notified (via Smartsheet alert). I'm having trouble seeing any way to do this if I can't use a formula in the contact field to do the auto-assigning.

Chris McKay

I'm an embedded customer, however, the Smartsheet Product Team's lack of direction, their inability to create even high level solutions to fundamental issues that have been raised by customers and the almost complete disregard for the serious impact changes that they initiate (see above for an example) have on existing solutions is pushing me to consider other platforms when my firm's renewal comes up. I'm currently in negotiation with your sales team, but I can tell you it's far from a done deal.

Once again, the last update was something that nobody (I'm aware of) requested, as is the upcoming changes to the interface. Why did you decide to prioritise moving toolbars and menus around the screen instead of fixing the long standing issue with system dates not working properly? Why did you prioritise linking directly to the formatting options panel from a chart axis rather than tackling any of the myriad of enhancement requests large sections of your customers have been screaming out for over many years on this very site? What about continuing to develop the very rudimentary documentation you only very recently created for Control Center (an application I still believe is in Beta at best)?

I understand you have a development roadmap and that it will be aligned to corporate goals (revenue targets, resource availability etc.), but I am finding it increasingly difficult to continue promoting Smartsheet as the tool of choice within my organisation. Smartsheet has not been communicative about what direction the platform is taking and has not effectively corresponded with it's customers to provide a level of comfort that the Product Team is listening to them.

As I have said many times, you guys do a lot of stuff very well, but let yourselves down with some of the basics.