Automated Workflow - Move row to Sheet (with Index Match Formulas)

Hi there,

I have a automated workflow where I have multiple source sheets copying rows into a separate sheet. Since the copied row information on the separate sheet can change on different columns, I have a Index/Match set up to auto populate any changes from the source sheet.

The problem I'm having is there is another layer, where if the row term end date is in the past - all the sheets have a additional workflow to move the row to their EXPIRED sheet. Since the Index Match formula uses the source sheet as a reference, once a row is expired and moved, the row is now a NOMATCH, and does not carry over the end date to the separate sheet.

I tried setting up the expired workflow to one day prior to end date, but it does not move the index match values. Do formulas get moved when using the automated move row workflow?

Thanks

Matt

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hello @MattMattMatt ,

    You could add an IFERROR() to your INDEX(MATCH()) formula with the 'error' value being a second INDEX(MATCH()) formula that references the EXPIRED Sheet. That way, instead of a #NOMATCH the second lookup will kick in and you will have your data.

    If you have access to DataMesh this would be a good use case for that tool.

    Depending on your process you might be able to combine your Sheets and use reports to parse the data rather than using lookup formulas.

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!