Best practice for sending notificatons from 1 smartsheet based on users in a 2nd smartsheet

Peggy

Hi,

I have a master smartsheet with client name, a date and the clients product lines, on a row. The product line column is a multiple select, restricted to a specific list of values.

I have second smartsheet by department and users within the department. I am thinking of adding the product lines which the department is responsible for.

I would like to have a workflow on the master smartsheet which can look at the second smartsheet to determine who to send the row data to when a specific date is met.

Is this the correct way to set this up or is there a better way to manage this?

Thanks.

Mike TV

@Peggy

You're right about adding the Product Lines to the 2nd sheet with the department/users. After you do that, on the 1st sheet you'll want to use an INDEX/MATCH formula which drags in the user who needs the notification. You'll match against the product line listed on sheet 1 and 2.

Once you've got that setup and working, on the 1st sheet you can create an automation which is triggered by the date field and alerts the contact field which is the user who's INDEX/MATCHed from the 2nd sheet.

Mike TV

@Peggy

You may need to use an INDEX/COLLECT formula instead if you have multiple people listed on the 2nd sheet and you want to pull in someone with a specific title. COLLECT will help you specify multiple criteria which would be the product line criteria and the person's title criteria.

Mike TV

@Peggy

You're right about adding the Product Lines to the 2nd sheet with the department/users. After you do that, on the 1st sheet you'll want to use an INDEX/MATCH formula which drags in the user who needs the notification. You'll match against the product line listed on sheet 1 and 2.

Once you've got that setup and working, on the 1st sheet you can create an automation which is triggered by the date field and alerts the contact field which is the user who's INDEX/MATCHed from the 2nd sheet.

Mike TV

@Peggy

You may need to use an INDEX/COLLECT formula instead if you have multiple people listed on the 2nd sheet and you want to pull in someone with a specific title. COLLECT will help you specify multiple criteria which would be the product line criteria and the person's title criteria.

Peggy

Very helpful, thanks!

Peggy

Hi-

Not sure if i am doing this correctly.

I want to look in Sheet 1 - People (could be 1 or 115) where the Product line matches in sheet 1 and sheet 2 (if it is blank in the sheet 1 pull everone (all 115 ppl). and/or where GEO matches in sheet 1 and sheet 2 (if it is blank in the sheet 1 pull everone (all 115 ppl).

=INDEX(COLLECT({People PL Geo}, [Product Line (Rnw)]@row = {Notif PL}, [GEO (Rnw)]@row) = {Notif GEO}, {Notif Name})

Mike TV

@Peggy

No. You do it like this:

=INDEX(COLLECT({Range to collect data from}, {Criterion Range}, Criteria, {Criterion Range}, Criteria), 0)