1

I have a spreadsheet to track student loan outs:

Column A is the student name
Column B is how many loan outs they have received
Column C is the class period Column D is the teacher name
Column E is the type of loan out received
Column F is the date received Column G is loan out returned.

Columns E,F,G repeat in each row 5 more times.
Column B has a Counta formula count of each time a loan out is given to that student.

I would like each teacher to have a sheet within that book that lists the students that have 2 or more loan outs instead of having to look at the master list.

So for example, as a teacher I would have a seperate sheet to look at and when a student of mine gets two or more loan outs it would copy that row into my seperate spreadsheet.

So I am looking for a formula or script that would first look in column B for a number greater than 1 and then by teacher name in column D and pull the data to the seperate sheet. I would do this for each teacher.

I already have a script running that automatically adds the date when a loan out is entered. I don't know if this possible too. Here is a link to a blank sheet than I am using: Example Sheet

1

2 Answers 2

1

I had a similar problem. sheetSpider does not appear to be available anymore. I tried writing my own script and it kinda worked but it was slow and didn't update reliably when the source data changed.

I eventually solved my problem with a pivot table (Data->Pivot table report...). Just select the range, then add all the columns you want to see as Rows (sounds counterintuitive I know) and set Filters for Column B > 1 and Column D = teacher's name. Create a new pivot table for each teacher on its own sheet where the second filter varies. More details about pivot tables in Google Sheets here. For me it is quick and updates well whenever the source data changes.

-1

You could try using sheetSpider from the script gallery. (Tools > Script gallery)

It's built for situations like the one you have presented.

1
  • The script gallery is not available anymore.
    – Rubén
    Apr 3, 2017 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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