Copy certain data to secondary sheet based on condition

SteveE

I am struggling with coming up with a formula to do this and it may be as simple as me not fully understanding the INDEX/MATCH function.

We have a Project Intake sheet where projects are initially entered and assigned a project ID. Each project row may be updated several times before the project is approved. Once approved, I'd like to copy some of the row data to an Approved Project sheet.

Also, the Intake Sheet is not static as new projects will be entered. So I want to be able to copy those once they have been approved.

In the example below, ITS001 and ITS002 have been approved so I'd like to copy the Project ID, Project Name and Project Manager to the Approved Project Sheet.

Project Intake Sheet

Approved Project Sheet

The logic seems simple enough (if approved, copy some cell data to new sheet), but I have not figured out the correct formula.

Thank you in advance for any assistance!

James Keuning

You cannot copy just some fields. You need to copy the entire row.

What you can do is create a new sheet, where you add your Project ID field, and then use INDEX and MATCH to bring in relevant fields from the Intake Sheet. Create a date triggered workflow that MOVES the row if Approved Status = Approved. You use MOVE so that it doesn't get copied again and again. Alternatively, you can use COPY, but you need to create a new field, call it COPIED or whatever, and a second workflow puts the run date in that field. If you do this, you need to add a condition to the workflow that only runs if COPIED is blank.

You run this using a date trigger because you cannot base a condition trigger on a formula. So you can't tell smartsheet to run the workflow when Approved Status changes to Approved in a formula field.

Andrée Starå

Hi @SteveE

I hope you're well and safe!

To add to James's excellent advice/answer.

A note, I don't think you need to worry that it would trigger multiple times if you only Approve each project once.

Also, if I understand you correctly, James, you can actually trigger a Workflow from a formula field.

Here's a possible workaround or workarounds

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet, and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!