Copy automatically partial data from master sheet to different sheets

Housing Justice

Hi all,

This is my first post, I hope you can help me:

We have created a form that populates essential data on projects, and we have created some sheets for different purposes. For instance, we have one for membership, one for tracking progress, and one for contacts. All of them have several columns that are the same, and the data is taken from the form, but we don't want to copy all the data in the form into each of the sheets, just a few columns that help us identify them, such as the region, local authority, name of the project and manager.

Master sheet (the form)

Destination sheet

In the past, we used cell linking, but every time a new project fills the form, I have to add a new row in each sheet and link the new cells, which is cumbersome and prone to human error, so I was trying to find an alternative way of copying the information from these cells only into all the others, and also link them in a way that if the project changed name or we assigned a new manager, the change is recorded in the master sheet and it would automatically change it in all the rest.

I thought a solution could be to use reports and use the form as the master sheet, but then we would not be able to add new columns to the reports, unless we add them to the master sheet. This would make the master sheet really big and difficult to manage. Any tips on that?

I also read about cross sheet references, which would mean every time a new row is added, the data is added in others, but so far all I could find uses formulas, whereas I just want the same information copied across.

And a third way is to use workflows, so every time a new row is added to the master sheet, it is copied across to all the others. The two cons are that it can only copy the whole row, not just specific columns, and I don't think they get linked so that if I change the data in the original sheet, it is reflected in the others.

Any help with this would be much appreciated!

Andrée Starå

Hi @Housing Justice

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.

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.

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!

Housing Justice

Hi Andrée, thanks very much for your reply. I've tried to do the vlookup and index/match formulas, but I don't think I'm advanced enough to make them work.

I've tried the following:

=INDEX({Region 2}, 1, 1) , where {Region 2} is the name I've given to the reference in the other sheet, and it references the whole column.

This is the closest I've been to not getting an invalid data or unparseable error. But the problem is that when I try to convert it to column formula, it will keep on returning the value for the first row, and I'd have to manually alter the formula on each row to match them.

Do you have a post explaining in more depth how your method works with helper sheets?

Thanks!