Assigning A Person Based on Tier and Zone

Hi, I am creating an Escalation Tracker for one of our teams. Escalations will be entered via a form and the Submitter will choose their name on form. The Submitter will need to select an Escalation Tier (1, 2 or 3) and a zone (Global or Domestic). When a new row is added, they would like certain individuals "assigned" to the row in an "Assigned To" column. The issue I have run into is that if the Escalation is a Tier 1, Global or Domestic, they want it assigned to the "Submitter". The Assigned To column needs to be a Contact List because they want to add workflow notifications for the Assigned To column. I tossed around the idea of creating a formula for the Assigned To column, using an IF/OR statement to populate the Assigned To, but then running workflows would be difficult, as the column would be a Text/Number format.

I created some workflows to "Assign People" on new rows added based on the Tier and Zone, which work, but the only problem I have is with this first one, Tier 1, Global/Domestic, Assigned to the "Submitter".

Anyone have any suggestions to get around this? I was also thinking of using a workflow to change cell value to a text entry and then create a helper column, which would populate email address next to the Assigned To column, which I can then use for workflows.

Thanks in advance and let me know if this is clear or if you have any questions.

Chris

Find more posts tagged with

Formulas

Workflows in Smartsheet

Accepted answers

All comments

Paul Newcome

You can use formulas to populate a contact column. Did you try some formulas and run into issues?

BristolCVN

@Paul Newcome Hi Paul, no I haven't. Let me try that now and I'll circle back. Thanks!

BristolCVN

@Paul Newcome Hi Paul, so I tried with formulas but not getting the correct outcome. This is the logic I am trying to create.

If [Global/Domestic]@row is "Global" or "Domestic" and [Escalation Tier]@row is "Tier 1" then [Assigned To}@row is the row Submitter ([Submitter]@row).

If [Global/Domestic]@row is Global and [Escalation Tier]@row is Tier 2, then [Assigned To}@row is "Name"

If [Global/Domestic]@row is Domestic and [Escalation Tier]@row is Tier 2, then [Assigned To}@row is "Name"

If [Global/Domestic]@row is Global and [Escalation Tier]@row is Tier 3, then [Assigned To}@row is "Name"

If [Global/Domestic]@row is Domestic and [Escalation Tier]@row is Tier 3, then [Assigned To}@row is "Name"

Paul Newcome

What are a couple of the formulas you have tried thus far?

BristolCVN

@Paul Newcome This one:

=IF(OR[Global/Domestic]@row = "Global", IF([Global/Domestic]@row = "Domestic"), IF([Escalation Tier]@row = "1", "=Submitter@row"), IF(AND[Global/Domestic]@row = "Global", [Escalation Tier]@row = "2", "Molly"), IF(AND[Global/Domestic]@row = "Domestic", [Escalation Tier]@row = "2", "Christy", IF(AND[Global/Domestic]@row = "Global", [Escalation Tier]@row = '3", "Erin", IF(AND[Global/Domestic]@row = "Domestic", [Escalation Tier]@row = "3", "Christy", "")))))

Paul Newcome

Give this a try:

=IF([Escalation Tier]@row = 1, Submitter@row, IF([Global/Domestic]@row = "Domestic", "Christy", IF([Escalation Tier]@row = 2, "Molly", "Erin")))

BristolCVN

@Paul Newcome Thank you, looks like I over complicated it. Since I want to use it in a Contact List column, would I be able to put their email addresses in for the names?

Paul Newcome

Yes. You would need to use email addresses instead of names.

BristolCVN

@Paul Newcome Thank you so much! Have a great weekend!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.