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

MattMattMatt

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

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.