Automated Notification Based on Number of Days and Selections in Multiple Smartsheets

OhCanada2000

Hello, I'm hoping someone might be able to help or at least get me on the right track. What I would like to do is have an automated notification go out to the "(D) Assigned to" from SS2 if they are assigned a state/territory in the "(C) Out of State" cell in SS2 that contains the same state/territory as SS1 "(B) State/Territory" (in this example, "Alaska") and ONLY if it has been more than 90 days [with the starting date from SS1 "(A) Date of Last Check" and the end date from SS2 "(E) Date Assigned"].

An added issue is that there are times where a "(C) Out of State" cell may have the same state listed twice and assigned to ["(D) Assigned to"] different people and, if possible, we only want one person to receive the notification (the notification is an assignment, and we don't want to duplicate assignments).

Thank you for any help you can provide!

Michelle Choate 2

What you will need to do is have helper columns in the sheet you want to send notifications from.

So for instance if you want them to be sent from SS2 where the "(D) Assigned To" column is located, you will need 2 helper columns:

• The first is an option of either just bringing in the "Date of Last Check" directly and setting your automation to read off of that date, or it will be "90 Days or More" checkbox column with a formula that looks something like IF({SS1 Date of Last Check} > TODAY(-90),1,0), and then send the automation based off of when that box is checked.

• The second will be "State/Territory Confirmation" checkbox with a formula that looks at the "(C ) Out of State" column, and then the "(B) State/Territory" column in SS1 to compare them, something like =IF(HAS({SS1 State/Territory}, [(C ) Out of State]@row) 1, 0).

Then you could set up your automation to be from either option in the first helper column, with a criteria that the second column is checked. Now to only send to one person, instead of everyone in the cell, you will have to set up different paths in your automation for each set of criteria, and then who it is supposed to go to. If you only want Alaska to go to one person, then you would use that in your criteria and select send to a specific person. Unfortunately if you only want to send to one person in a multi-select contact column, that is not currently a possibility. So you would have to set up a criteria tree with each possible option, and select each person that it would need to go to.

Paul Newcome

To make sure I understand…

If I am on Sheet 2 and see "Alaska", I need to search Sheet 1 for "Alaska". If "Alaska" does exist on Sheet 1 and the [Date of Last Check] is more than 90 days before [Date Assigned on Sheet 2, we need to send an assignment notification.

But… As an added challenge, if those requirements are met on two separate rows in Sheet 2, we only want to send to one of those people.

Does all of that sound right? If so, the only question I would have would be, how do we decide which person gets the assignment notification in the event there is more than one row that meets the requirements?

OhCanada2000

@Paul Newcome Yes, this is correct. We don't have any specific requirements for assignment, best case scenario would be to rotate between the 15-20 people in that column evenly, but random assignment would work fine too.

Georgie

Hi @OhCanada2000,

Building off Michelle’s suggestions and the clarification from Paul and yourself, I’d do the following, which requires multiple helper columns with column formulas in your “SS2” sheet:

1. Add a helper text/number column and use an INDEX(MATCH) formula to pull in the date of the last check from the SS1 sheet (you’ll create cross sheet references as you create the formula, and you can name these as desired - see Create cross-sheet references for more information), eg:
   1. =INDEX({Date Last Check}, MATCH([(C) Out of State]@row, {State/Territory}, 0)
      
2. Add a helper checkbox column (I called mine “Last check more than 90 days ago?”) and use the below formula to check the box when the Date Assigned minus the Date of the last check is greater than 90:
   1. =IF([(E) Date Assigned]@row - [Date of last check]@row > 90, 1, 0)
      
3. Add another checkbox helper column (I called mine “State/Territory in SS1 sheet?”) and use the formula provided by @Michelle Choate 2 to check if the State/Territory is in the SS1 sheet:
   1. =IF(HAS({State/Territory}, [(C) Out of State]@row), 1, 0)
      
4. Add another text/number helper column with the formula below to count the number of assignments per person for each value in the “(C) Out of State” column:
   • =COUNTIFS([(C) Out of State]:[(C) Out of State], [(C) Out of State]@row, [(D) Assigned To]:[(D) Assigned To], [(D) Assigned To]@row)
     
5. Add an “Alert next” Contact List column with a formula similar to below (you’ll need to ensure you’re using the column names from your sheet). This returns the contact from “(D) Assigned To” that matches the value in the “(C) Out of State” column in the same row and where the count of assignments value for that row matches with the lowest number in the count of assignments column for the state/territory:
   • =INDEX(COLLECT([(D) Assigned To]:[(D) Assigned To], [(C) Out of State]:[(C) Out of State], [(C) Out of State]@row, [Count of assignments per user per state/territory]:[Count of assignments per user per state/territory], @cell = MIN([Count of assignments per user per state/territory]:[Count of assignments per user per state/territory])), 1)
     
6. Set up your alert workflow to send to users in the “Alert next” column when rows are added or changed and any field changes, with conditions set to “Last check more than 90 days ago?” is checked and “State/Territory in SS1 sheet?” is checked.
   

Check out this help article for more information on INDEX(MATCH) and INDEX(COLLECT) formulas: Formula combinations for cross sheet references. You might also want to review this community thread which helped me with the "Alert next" column! 

Does that work for you?

Georgie