Automation: Copy cell value to another sheet

Angela Chen

Hi, not sure whether this has been answered yet but my question is as follows:

I have a sheet, Sheet A, that stores all the projects and their information, such as Project ID, project status.

I have another sheet, Sheet B, that stores only projects that have reached a certain stage (ex. in execution).

And I just want to know if it is possible to automate copying JUST the Project ID from Sheet A over to Sheet B when it has reached the execution stage. I have tried formulas and automation but automation can only copy the entire row, when I only need the Project IDs. Also, both Sheet A and Sheet B have a different structure, hence copying the entire row would not work for me.

Would appreciate any help or suggestion with this, thanks!

Andrée Starå

Hi @Angela Chen

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.

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!

Angela Chen

Hi @Andrée Starå , thanks for the quick response.

I tried using the functions as you mentioned but those would only return the first Project ID that matches the condition, Status = In Execution. I would like to retrieve all the Project ID that meet such condition, is there another way to do it? How do I set up a function that will find the next project ID that satisfies such condition? Thanks a lot!

Andrée Starå

@Angela Chen

Happy to help!

Yes, we'd probably need to use the COLLECT function.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Angela Chen

Yes sure, @Andrée Starå

So I want to find a way to automate pulling the PIN from Sheet A when Project Status = Execution to Sheet B.

This is Sheet A.

This is Sheet B.

Thanks!

Angela Chen

Hi @Andrée Starå, would appreciate if you could share more about the COLLECT function. Do I use it inside the INDEX / MATCH function?

The formula I have tried in Sheet B but did not work was: = INDEX(Project ID range from Sheet A, MATCH("Execution", Project Status range from Sheet A, 0))

^ only retrieves the first ID that meets such condition

Thanks!