nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Welcome!

Automation Between Sheets?

Smaug

Hello! I have 2 sheets and I'm trying to design some notification automation between the two of them and I'm not sure what I want is within SS' capabilities.

Sheet 1 has an application associated with it, Form A. An applicant fills out Form A and the data is all populated in Sheet 1, the relevant info for this question is their email address and their departmental association. Sheet 2 is a fairly static database sheet of departmental contacts, who are in charge of their respective applicants, and their email addresses.

What I'm trying to automate is, when an applicant completes Form A, an email is sent to both the applicant (using their email that's stored in Sheet 1) and the relevant departmental contact from Sheet 2.

The automation for sending the applicant email is easy, I've got that piece. But I can't figure out how to ask the automation on Sheet 1 to go into Sheet 2 when it receives a completed Form A and look up the appropriate departmental contact to include in the email that goes out.

Is this possible with SS' existing automation? Associating Form A with Sheet 2 is another option I considered, but it's not really practical, the datasets are very different. For the same reason combining Sheet 1 and 2 is not really practical either.

Thanks for any help.

Find more posts tagged with

Automations

Comments

Paul Newcome

You would inset a new contact column in sheet 1 and use a formula to pull the appropriate departmental contact over.

=INDEX({Sheet 2 Contact Column}, MATCH(Department@row, {Sheet 2 Department Column}, 0))

Then you can set up your automation to reference this new column.

Smaug

Thanks so much, Paul!

I realized that I may have misrepresented the situation slightly. What I actually have in my form is a drop-down list and applicants can select their department from 11 options that then get populated in Sheet 1. So, for any given applicant, there will only be a value in one of those 11 columns (since they only work in one department). So is it still possible to tell SS to search those 11 columns to find the one that has a value in it and then reference the departmental contacts in Sheet 2?

Here's an example screenshot, this fictitious applicant I made works in Dental Medicine, under the Dental Hygiene Department. In this example, I would want SS to match up "Dental Medicine" here in Sheet 1 with the Dental Medicine departmental contact in Sheet 2.

Paul Newcome

In that case, my recommendation would be a helper column just called "Department" and put this column formula in it:

=INDEX(COLLECT([First Dept Column Name]@row:[Last Dept Column Name]@row, [First Dept Column Name]@row:[Last Dept Column Name]@row, @cell <> ""), 1)

Then reference this helper column in the INDEX/MATCH from my last comment.

Sign in to join the conversation!

Quick Links

Trending discussions

Is Grid View Development Frozen?
Questions for Smartsheet about Grid View… Will Grid View one day be replaced in total by Table View, thus removing / end-of-lifing Grid View? If so, on what time horizon? Will Grid VIew be updated to include new functions that have been / will be released to Table View, e.g. hiding & unhiding columns selectively;…
Dashboard Chart Help Needed
I've tried the new Chart builder using both AI and manual options. I've tried building a legacy chart. I've gotten really close, but I can't quite get the chart to display all the data. I'm flexible on what type of Chart is used. I'm working with 3 sheets. The primary sheet is a task/initiative tracker. The secondary…
New grid user interface is significantly slower than prior iteration
Hello, I'm not sure if anyone has experienced this, but the new user interface rolled out this week is slowing down my workflow. When utilizing predecessors, an update any predecessor related column (duration, start/finish, predecessor), it takes about 3-5 seconds to update related columns. Additionally, I am experiencing…