Getting values in one row from another sheet where they are in multiple rows

Kamya Pamnani

Hi,

I have a master data where country wise and department wise email ids are there in separate rows. I n my separate sheet, I need to pick email ids in one row where let's say Country is AU and Department is IT. I have tried to use Index collect formula but that doesn't work as in my master data email ids are in separate rows. If multiple email ids are in one row then I can pick with Index (collect) but since they are in separate rows, Index (collect) formula doesn't work. Please help if you have faced this kind of problem.

Thanks.

Bassam Khalil

Hi @Kamya Pamnani,

Could you please add a screenshot of your sheets? This will help me create the formula for you. Additionally, it would be better if you could copy and paste the column names in your reply.

Kamya Pamnani

Hi Bassam Khalil,

Please fine below screenshot of master data:

Below is the screenshot of the data where I need values. I have used Join (collect) formula and the multiple email ids got populated in same cell but the problem is with Automation. The automation workflow is not going to those email ids. Please help if you know of other formula.

Bassam Khalil

Hi @Kamya Pamnani,

When you use the JOIN(COLLECT()) formula to aggregate email addresses into a single cell, the cell contains a string that represents all the email addresses separated by a delimiter (e.g., a semicolon). While this works for display purposes within Smartsheet, it may not be recognized as individual, actionable email addresses by the Smartsheet automation workflows. Automation typically expects individual, discrete email addresses rather than a single string of multiple addresses.

To overcome this, you might need to reconsider how you structure your workflow. If your goal is to send an automated message or notification to all the email addresses that match certain criteria, here are some possible approaches:

1- Instead of trying to send one notification to a concatenated string of emails, set up individual notifications for each email that matches your criteria. While this might not be as convenient as sending a single email to multiple recipients, it ensures that Smartsheet recognizes each email address properly. and i suggest to add a helper columns to split the Joined text into individual email addresses. then using the automation.

2- If you require a single email to go out to multiple recipients, consider using third-party tools like Zapier or Integromat. These platforms can interact with Smartsheet to read the cell with the concatenated string, split it into individual email addresses, and then send a single email to all those addresses. Here's a high-level view of how this could work:

• Trigger: Your automation is triggered by a change in Smartsheet or on a schedule.
• Action: The tool reads the cell with the concatenated email addresses from Smartsheet.
• Data Processing: The tool splits the string of email addresses into an array of individual emails.
• Send Email: The tool sends an email through an email service provider (like Gmail, Outlook, etc.), including all the individual email addresses as recipients.

and here is step by step how you can use Zapier to do it:

Step 1: Set Up a Trigger in Smartsheet

First, you need to set up a trigger that will start the Zapier automation:

1. In Smartsheet, ensure that the cell containing the concatenated email addresses updates when new matches occur. This might be based on form submissions, status changes, or regular updates.
2. Make sure your sheet is shared with a Zapier-integrated Smartsheet account.

Step 2: Create a New Zap

1. Sign in to your Zapier account. If you don't have one, create a new account.
2. Click on "Make a Zap" to start creating a new workflow.

Step 3: Choose Smartsheet as the Trigger App

1. For the Trigger, choose Smartsheet.
2. Select the Trigger Event, such as "New or Updated Row" or "New Row" depending on how your data is set up.
3. Connect your Smartsheet account to Zapier when prompted.
4. Set up the trigger by selecting the specific sheet and column to monitor for changes.

Step 4: Test the Trigger

1. Zapier will prompt you to test the trigger to ensure it can successfully retrieve data from your Smartsheet.
2. Make sure Zapier finds a recent change in your sheet.

Step 5: Format the Data (Optional)

1. You might need to add an Action step to format the data if necessary. This can be done using Zapier's built-in "Formatter" to split the string of email addresses into an array.
2. Choose "Text" for the Formatter type and then "Split Text" as the transform.
3. Specify the delimiter used in your concatenated email string (e.g., semicolon).

Step 6: Set Up the Email Action

1. Choose your email app (like Gmail or Outlook) as the Action app.
2. Select the "Send Email" action.
3. Connect your email account to Zapier.
4. Configure the email action by mapping the "To" field to the output of the Formatter step, which should be the array of individual email addresses.
5. Fill out the rest of the email fields (Subject, Body, etc.) as required for your notification.

Step 7: Test Your Zap

1. Send a test email through Zapier to make sure everything is working as expected.
2. Make any necessary adjustments to the formatting or email template based on the outcome of the test.

Step 8: Turn On Your Zap

1. Once everything is set up and tested, turn on your Zap.
2. Zapier will now monitor the specified Smartsheet for changes and automatically send out emails when the concatenated email address cell is updated.

Step 9: Monitor and Maintain

1. Regularly check the automation to ensure it's running smoothly.
2. Monitor the outgoing emails for any issues or failures.

And here is the details how zapier can interact with Smartsheet to read the cell with the concatenated string, split it into individual email addresses, and then send a single email to all those addresses.

Step 1: Setting Up Your Smartsheet Trigger

• Log in to Zapier: Create an account or log in to your existing Zapier account.
• Create a New Zap: Click the "Make a Zap" button to start.
• Choose a Trigger App: Search for and select "Smartsheet" as the trigger app.
• Choose a Trigger Event: Select "New or Updated Row" as the event, which triggers the Zap whenever a row is added or modified.
• Connect Your Smartsheet Account: When prompted, log in to your Smartsheet account and authorize Zapier to access it.
• Customize Your Smartsheet Row: Pick the specific sheet that contains the concatenated emails and specify any additional criteria needed to target the correct row.
• Test the Trigger: Zapier will fetch data from Smartsheet to confirm that the setup works correctly.

Step 2: Splitting the Concatenated String

• Add an Action Step: After setting up the trigger, click on "Plus" to add a new action.
• Choose a Built-In App: Select "Formatter by Zapier".
• Choose an Action Event: Select "Text" for the action event, as you'll be working with text data.
• Set Up Action: Choose "Split Text" from the Transform menu.
• Customize Text: In the "Input" field, use the Custom option to select the field from Smartsheet that contains your concatenated emails.
• Separator: Input the character you used to separate emails in your concatenated string (e.g., semicolon ";").
• Segment Index: Choose "All" to split all the emails into an array.

Step 3: Sending the Email

• Add Another Action Step: Click on "Plus" again to add the email action.
• Choose an Email App: Select your email service provider (e.g., Gmail, Outlook).
• Choose an Action Event: Pick "Send Email" from the list of available actions.
• Connect Your Email Account: Allow Zapier to access your email account.
• Set Up Email: In the "To" field, use the output from the Formatter step (this will be your array of email addresses). Fill out the rest of the email fields like Subject, Body, etc.

Step 4: Test and Review

• Test the Email Step: Send a test email to make sure that it correctly sends to the list of split email addresses.
• Review and Edit: Make any adjustments needed to the email content or the way the email addresses are split.

Step 5: Activate Your Zap

• Turn On Your Zap: Once the test is successful, and you're happy with the configuration, activate the Zap.
• Monitor: Keep an eye on your Zap's activity to ensure it's working as intended.

Just for your information i used some help from CHATGPT to complete this Answer.