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.
Best Answer
-
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), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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
andMATCH
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
-
Are you able to provide some screenshots for context? Generally speaking, INDEX/MATCH should work.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks, Paul! Once again, you have solved my problem.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!