5

Hi there:)

I have a sheet used as a "Maintenance Request" sheet. It is populated by a form. Each person submitting a request provides their name/email into the "Submitted By" column. This allows them to receive an alert once the job is complete.

I have frustration coming from the maint dept. when assigning a job/task in the "Designated" column. Every contact from the "Submitted By" column also shows up in the "Designated" drop down menu.

If I restrict the "Submitted By" column, to list values only.....Nobody would be able to enter their names. Additionally, if their names are added to just a text field, they wont receive the email confirmation, once the job is complete. 

Is there a work around to a situation like this? I've tried so many different things but, nothing seems to work.

 

Comments

I've seen an interesting solution that involves cell linking to a contact list in a blank sheet.

  1. Set up a blank sheet with just a content-column and the primary row in it.
  2. Then create your list of contacts that should be in your selected list. 
  3. Include a "Choose Assignee" Contact at the top of the list (or whatever you want to call it.
  4. Create a cell link in your primary sheet to the Choose Assignee cell for each row of your project. 

Instruct your team to double click on the linked cell to choose an assignee which will trigger the method to relink to a different cell, they can then choose another contact and select okay to assign the task, as well as double click to change it to someone if needed. Right click on my screenshot below and choose open in a new window for an example. 

Would that work? 

In reply to by Mike Wilday

Wow.... I really like this!

There's one part Im not following though. If my sheet is populating new rows via a form, how is the link included in the new row?

In reply to by Andrée Starå

Thank you so much for this. Mike also provided this handy little trick.  There's one part I don't get. Can you tell me how this link is included within a new row that populates via a form?