Multiple sheets, multiple criteria leading to a date being copied from one sheet to the other

I have two sheets that I am using for this issue. Sheet A is a form intake sheet where individuals answer several questions and upload a file/report. That report is then reviewed and, if determined to be complete/accurate, I select "Approved" in a drop-down box (Column named "Approval") on Sheet A.

On Sheet B, we have 500+ locations each designated with a 3-digit alphanumeric code (Column named "Facility Code").

What I want to happen is once the "Approved" designation is given on Sheet A, I want a date from a date field on Sheet A (Column named "Date Submitted") to transfer to the corresponding date field (Column called "New Date") in the row with the matching 3-digit alphanumeric code on Sheet B. (ie Facility Code 002 has been placed into Approved status so 2/14/24 now copies from Sheet A over to a blank date column in the row for Facility 002 on Sheet B).

I have tried INDEX/MATCH, INDEX/COLLECT, JOIN, and several other less promising things but cannot return this date. 2 and 1/2 hours in and all I am seeing are commas, braces, and parentheses. Where is automation when you need it? Thanks, any help would be greatly appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=IFERROR(INDEX(COLLECT({Source Sheet Date Column}, {Source Sheet Approved Column}, @cell = "Approved", {Source Sheet Facility Code Column}, @cell = [Facility Code]@row), 1), "")

All comments

Bassam Khalil

Hi @SASardy,

To achieve the desired result in Smartsheet, where a date from Sheet A is transferred to Sheet B based on a specific condition (the "Approval" column being set to "Approved"), and matching a 3-digit alphanumeric code, we will use a combination of Smartsheet's automation features and formulas. Unfortunately, direct formula references between sheets for conditional data transfer are limited, but automation can help achieve this workflow. Here's a step-by-step solution:

Step 1: Set Up a Cross-Sheet Reference

First, ensure you have a cross-sheet reference set up in Sheet B to pull the necessary data from Sheet A. This involves using the INDEX and MATCH functions in Smartsheet, but since you've mentioned these haven't worked as expected, we're going to focus on automation to trigger the action based on the "Approved" status.

Step 2: Automation Setup

Smartsheet’s automation feature can be used to trigger actions based on specific criteria being met. Here's how you can set it up to solve your issue:

Create an Automation Rule in Sheet A:Go to "Automation" > "Manage Workflows" > "Create a Workflow".
Name your workflow (e.g., "Approval to Transfer Date").
For the trigger, select "When rows are changed" > "Condition is met", and then set the condition to when the "Approval" column is changed to "Approved".
For the action, you'll face a limitation: Smartsheet doesn't natively support direct row data transfer between sheets based on a workflow action. However, you can work around this by using Smartsheet's "Update Request" or notifications to manually or semi-automatically update Sheet B.

Step 3: Use Smartsheet Connectors or API for Integration

Since direct automation might not fully cover your needs, consider using Smartsheet connectors (if available for your use case) or the Smartsheet API for more complex integrations. The API can programmatically find the row in Sheet A that was marked "Approved", extract the "Date Submitted", find the matching "Facility Code" in Sheet B, and update the "New Date" field.

Step 4: Manual or Semi-Automatic Workaround

As an interim solution, you could:

Use the automation rule to send an update request to a designated reviewer for entries marked as "Approved".
The reviewer can then manually enter the date in Sheet B or use a prepared form that simplifies updating Sheet B based on the alert from Sheet A.

Step 5: Consider Third-Party Integration Tools

If manual or semi-automatic methods are too cumbersome and the Smartsheet API is beyond your current scope, third-party integration tools like Zapier or Workato might offer a more user-friendly interface for setting up complex workflows between sheets. These tools can listen for changes in Sheet A (like an "Approved" status) and then automatically update Sheet B based on your criteria, without the need for manual coding.

SASardy

Thank you for the suggestions Bassam but none of those seem to get me to the spot I need to be. APIs and 3rd Party integration tools are currently not an option for me. I really did not anticipate this being as complicated as it has turned out to be. Seems to be a relatively simple ask of Smartsheet.

Paul Newcome

Are you able to provide some screenshots for context? Generally speaking, INDEX/MATCH should work.

SASardy

So the first screenshot below is from my Form input sheet. Everything other than "Approved" comes in from a form. Once I review the data, I select "Approved" from the dropdown. What I want to have happen is to copy the date (#3) from the form input sheet to the data sheet (second screenshot). It has to look for "002" and then put it in the correct line. So there are basically two variables: approved and facility code.

Paul Newcome

Try this:

=IFERROR(INDEX(COLLECT({Source Sheet Date Column}, {Source Sheet Approved Column}, @cell = "Approved", {Source Sheet Facility Code Column}, @cell = [Facility Code]@row), 1), "")

SASardy

Thanks, Paul! Once again, you have solved my problem.

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