When status changes in one sheet, how to get it to cross reference into another sheet?

skytriv

Hello, I am trying to create a workflow when the parts status changes to "ready to schedule" in the Parts Log sheet gets copied into the sheet Project Management. I was able to create this workflow.

The problem I am facing is that every time the status changes it gets duplicated into the Project Management sheet. How do I prevent duplications of rows that have the same WO and just has the cell in the row change?

Thanks,

Skylar

Andrée Starå

Hi @skytriv

I hope you're well and safe!

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.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the 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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

skytriv

Thanks, Andre. I tried to set up a VLOOKUP for this but kept getting a formula error.

Andrée Starå

@skytriv

Happy to help!

What did you try? Can you paste it here?

skytriv

Thank you!

So this is what I am trying to do. Ignore the first question.

I have sheet 1 and sheet 2. I want the information that gets updated on sheet 2 to populate into sheet 1 without the row copying over to sheet 1 and duplicating.

Fields I want to have changed: WO Number, Date Completed, Overall Status

I have something like this but receive circular reference error:

=IFERROR(INDEX({Validation Work Order Log Range 2}, MATCH([WO Number]@row, {Validation Work Order Log Range 2}, 0)), "")

When I go to reference the sheet name this is what I'm going off of:

Andrée Starå

@skytriv

Happy to help!

Are you selecting the whole columns for the ranges?

I'd be happy to take a quick look.

Can you share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

skytriv

I just shared the sheets with you. Thanks!

Andrée Starå

@skytriv

Excellent! Thanks!

skytriv

Hi, Andre. Hope you're well. Have you had a chance to look into this? Thank you!