Help with cross-sheet automation and formulas

I have 2 separate sheets to manage my team's assignments. One is a weekly assignments sheet that pulls from a partner request form and a daily assignment sheet where I manually assign tasks and priority level to each assignment. I want to make it so that when I assign Tier 1 or Tier 2 priority on the 2nd sheet, it automatically generates a new row with the assignment (combining Item 1, Item 2, and Date) in the 1st sheet. The column names in the sheets are different. Below is what I would want to see based on the information in the 2nd sheet. I don't know if there is a way to do this.

1st sheet:

Assignment Assigned to Deadline

X and Y 3/30 Sam

A and B 3/31 Cal

2nd sheet:

Date Time Item 1 Item 2 Priority Assigned to

3/30 9:00 X Y Tier 1 Sam

3/31 15:00 A B Tier 2 Cal

Find more posts tagged with

Sheets

Formulas

Automations

Accepted answers

Paul Newcome

You would first insert a helper column into the first sheet that uses a formula to combine the [Host] and [Guest] fields. You would name this column "Assignment" to match the second sheet.

=Host@row + " and " + Guest@row

You would then set up a copy row automation to copy the row based on your desired trigger(s) and condition(s). It will copy all columns to the second sheet, but those that are not needed can be moved to the right of the sheet and then hidden to keep from cluttering things.

All comments

jmyzk_cloudsmart_jp

Hi @EduardoS

Could you please clarify why it's necessary to add rows from Sheet 2 into Sheet 1?

Would it not be sufficient to reference the priority and other fields using cross-sheet formulas like the one below?

[Prioriy] =JOIN(COLLECT({Priority}, {Item 1}, CONTAINS(@cell, Assignment@row), {Item 2}, CONTAINS(@cell, Assignment@row)))

https://app.smartsheet.com/b/publish?EQBCT=8970f9fe2dce4199a285a385a73a7477

Using a formula like this allows you to dynamically retrieve the priority (or even the time using a similar formula) based on the assignment content without duplicating entire rows between sheets.

[Time] =JOIN(COLLECT({Time}, {Item 1}, CONTAINS(@cell, Assignment@row), {Item 2}, CONTAINS(@cell, Assignment@row)))

If your goal is only to extract specific columns such as Priority or Time into Sheet 1, this method should fulfill the need with minimal maintenance.

However, if you absolutely need to copy only specific columns from Sheet 2 into Sheet 1 as new rows, you would need to use Smartsheet Bridge or the Smartsheet API since standard automation does not allow selective column mapping.

Otherwise, if you simply copy the entire row automatically, the result may include unnecessary columns and become harder to read — as shown in the image below, where the auto-copied rows include duplicate or unrelated data. Do you think this approach is still practical for your use case?

https://app.smartsheet.com/b/publish?EQBCT=e0db5eb1d43c439aa45362afb0add3da

https://app.smartsheet.com/b/publish?EQBCT=e566f1ccb72840878ff50fba277c04f4

By the way, you can add to sheet 1, Item 1 and Item 2 columns, and use copy row automation to avoid manula input in sheet 2.

EduardoS

As part of our jobs, we cover events at different levels. There are 2 aspects to it…some events get crib notes ahead of time and some do not. We use 2 separate sheets for these 2 aspects. The first one is internal only and we use it to keep track of all events, even those that will not get crib notes ahead of time.

The 2nd one is where all our pre-event planning lives and is visible by all of our partners. The sheet also receives requests from our partners when they fill out a request form.

What I have been doing is assigning coverage level for events in Sheet 1 (Tier 1, Tier 2, or Tier 3) and then going into Sheet 2 to create the assignment. I was hoping to find a way to automate this process, and have the assignment created automatically when as I categorize an event as Tier 1 or Tier 2 and assign it to a staffer.

As you can see, I do not have or need fields for Date, Time, or Coverage in Sheet 2. The Deadline will usually be 2 days before the Date of the event and I can fill that out.

I hope this explains what I am looking to accomplish.

Paul Newcome

You would first insert a helper column into the first sheet that uses a formula to combine the [Host] and [Guest] fields. You would name this column "Assignment" to match the second sheet.

=Host@row + " and " + Guest@row

EduardoS

Thank you! This worked great!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.