Automation to copy information back to row with identical auto number

Options
Paul G.
Paul G. ✭✭✭✭✭
edited 09/15/22 in Smartsheet Basics

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 09/15/22 Answer ✓
    Options

    @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.

    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 09/15/22 Answer ✓
    Options

    @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.

    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!

  • Paul G.
    Paul G. ✭✭✭✭✭
    Options

    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
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

    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!

  • Paul G.
    Paul G. ✭✭✭✭✭
    Options

    @Jeff Reisman

    Thank you that answers my question.

    Paul

  • Paul G.
    Paul G. ✭✭✭✭✭
    Options

    @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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @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!