Automation to copy information back to row with identical auto number

Paul G.

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

Jeff Reisman

@Paul G.

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.

Jeff Reisman

@Paul G.

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.

Jeff Reisman

@Paul G.

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.

Paul G.

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

Jeff Reisman

@Paul G.

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.

Paul G.

@Jeff Reisman

Thank you that answers my question.

Paul

Paul G.

@Jeff Reisman

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

Jeff Reisman

@Paul G. That's a good idea!