How to collect some data from several sheets to a main sheet in some automation way

Teresa Ding

Hello,

I have several sheets in different folders (one folder stands for one client), and one sheet one HO, all the sheets contain same columns. Is there a way to collect certain column data from all these sheets to one main sheet?

For example, all the sheets have Project Name, HO Name, Task Type, Start Date, Deadline columns, I'd like to collect all these data to one main sheet, and when there're new sheets created in these folders, the data will be automatically added to the main sheet. As there're some other columns in the main data, report or dashboard feature doesn't work in this case. And there'll will be several main sheets from different teams, a higher level report or dashboard will be created based on these main sheets.

What I'm thinking is use "Copy a row to another sheet" workflow to copy all rows from all the sheets to one new sheet, then create a main sheet, fill one column such as HO Name in manual way, and use vlookup or match formula to get other needed information from the new sheet to other columns. The conditions of the workflow I set are where some columns are not blank, when triggered, copy the rows. However, nothing happens, no rows are copied to the new sheet.

Would you please help me to figure out where I'm wrong?

Or do you have some other solutions to generate the main sheet?

Andrée Starå

Hi @Teresa Ding

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!