Automation to copy information back to row with identical auto number
An Inquiry sheet contains all inquiries received via a Smartsheet form. Each inquiry receives an auto number generated by Smartsheet. Automation is set to copy the inquiry information to a regional sheet for a staff member, regional representative, in the area to respond to the inquiry. Once the regional representative completes all of their tasks associated with an inquiry they enter the completion date.
Once a completion date is enter in the regional sheet I would like for that completion date, or the entire row to be copied back to the Inquiry sheet and entered on the row with the identical auto number. How can this be accomplished?
Thank you for your time and response.
Paul
Best Answers
-
How many regional sheets do you have? You need to use INDEX/MATCH formulas inside nested IFs to look up the completed dates from the regional sheets. The formula would look something like this:
=IF(Region@row = "Northeast", INDEX({Northeast Regional Sheet Complete Date Range}, MATCH(RowID@row, {Northeast Regional Sheet RowID Range}, 0)), IF(Region@row = "Southeast", INDEX({Southeast Regional Sheet Complete Date Range}, MATCH(RowID@row, {Southeast Regional Sheet RowID Range}, 0)), IF(Region@row = "Central"... etc.
Wrap the whole thing in IFERROR so that upon initial entry, the Completed Date remains blank.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
The "Record a Date" functionality is the only automation that could do this. It would need some indicator on the Inquiry sheet to tell it to record the current date. So you'd still end up needing to use nested IFs to decide which sheet you need to look at in order to check for a completed date.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
How many regional sheets do you have? You need to use INDEX/MATCH formulas inside nested IFs to look up the completed dates from the regional sheets. The formula would look something like this:
=IF(Region@row = "Northeast", INDEX({Northeast Regional Sheet Complete Date Range}, MATCH(RowID@row, {Northeast Regional Sheet RowID Range}, 0)), IF(Region@row = "Southeast", INDEX({Southeast Regional Sheet Complete Date Range}, MATCH(RowID@row, {Southeast Regional Sheet RowID Range}, 0)), IF(Region@row = "Central"... etc.
Wrap the whole thing in IFERROR so that upon initial entry, the Completed Date remains blank.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you @Jeff Reisman for your quick response.
That was another option I had considered. I was hoping there might be some form of automation that could be set. I am going to leave this question as unanswered for a couple days to see if anyone else has other ideas. I have tagged your answer as insightful.
Again thank you very much.
Paul
-
The "Record a Date" functionality is the only automation that could do this. It would need some indicator on the Inquiry sheet to tell it to record the current date. So you'd still end up needing to use nested IFs to decide which sheet you need to look at in order to check for a completed date.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
-
A thought just occurred which would simplify the formula, set automation so when a completion date is entered on the regional sheet the row is cut and paste into an Archive Inquiry sheet. INDEX and MATCH can be used to search only the Archive sheet for a match and the completion date.
Paul
-
@Paul G. That's a good idea!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives