0

I have a master sheet where the data is like this:

enter image description here Would it be possible to split this into multiple spreadsheets so it can look like:

enter image description here

and the sheets are divided into Weeks?

Scripts or formulae are both okay, as long as it's doable and sheets automatically adjust with the master is edited.

1

Nobody answered, so here's a simple solution using arrays, importrange, query in Gsheet.

With =IMPORTRANGE you can import data from your master sheet : don't forget to link the sheets between themselves. On my example, i importedrange from the same sheet but it'll works with any sheet you own.

There are multiples ways to do this (here's an example) :

First method : ImportRange + Array, this way you can import all the data, then use some filter. I used an array to select and combine the first 2 columns, then the week you need, then the emails.

={IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!A1:B7"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!C1:C7"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!G1:H7")}

Second method : ImportRange + Query all the data is imported then the query filter helps to select the columns you need (SELECT col with name, grade and week) and choose which students to pick based on the criteria ( Col1 contains 'student name').

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!A1:H7"),"SELECT Col1,Col2,Col3,Col7,Col8 where Col1 contains 'G3' OR Col1 CONTAINS 'g5'",1)

Third Method : Import range + Array + Query // pretty much the same except you choose which column you need with the import so you don't have to change the query, only the range of the second importrange to select the week of your choice (Col C here)

Edit : highlighted cells containing formulas

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.