Google-sheets – Copying rows to another sheet based on column value

google sheets

I have a sheet in a document with details of hundreds of signups for an event. As part of this event, there are roughly sixty 'centres'. People have signed up through one form, and now I want to allocate them to centres.

I've created a new column in the big "Master Sheet" (where the form submissions are dumped) called "Allocated Centres", and a new sheet in the document for each centre – let's use "Centre A" as an example.

I would like to copy the whole row of information from "Master Sheet", where the value in the column "Allocated Centre" is "Centre A", to the new sheet created for that centre.

Best Answer

This can be done in a couple of ways, depending on how the spreadsheet will evolve later.

  1. Copy once, so that after copying the second sheet exists independently of the master (changes in one will not affect the other). For this, select the column with Centre information, choose Data > Filter in the menu, then click the filter dropdown in the first row and select only "Centre A" in the filter.

filter

After this, only the rows with Centre A will be shown. You can copy-paste into the new sheet; only visible cells will be copied.

Reference: filtering your data.

  1. Put a formula in the second sheet that will update its contents based on whatever changes are made to the master. This can be done with FILTER command: for example,

 =FILTER('Master Sheet'!A:D, 'Master Sheet'!D:D="Centre A")

With this approach, any future edits to the master will propagate to Centre A sheet. You will not be able to edit this data in Centre A sheet.

Related Topic