Friday, May 26, 2017

Organizing an ILE (Part 1?)

So one of the things that has been very apparent in my first term and a bit anchored in a Habitat at Ormiston Primary is that organization is both key and very messy. The more innovative things you try, the harder it is to actually make them work because there are so many moving parts. Over the last few weeks we have introduced a lot of choice for our learners and a few issues have cropped up.

  1. We want to make sure we know (and the learners know) where they are going and when without having to stop and call out every learner individually or write all 90 learners every day
  2. We want to make sure that every learner actually does attend workshops (a common question we've had from educators who we've spoken to)
  3. We didn't want chaos or to spend lots of time sorting it out.

So with some ingenuity (and lots of trial and error!) I've found something that is working (for now). This is a multi step process, but once it was up and working, it's actually quite quick to sort out.

The first step is that in our weekly meetings, my fellow learning coaches and I choose the workshops we are going to offer (based on a variety of factors, which could be its own blog post). After that is decided (usually during our Tuesday meeting) we then make up a Google Form with those choices:

Now comes the fun part.  Initially I had just sorted them out by alphabetizing the results. But that took more time than I wanted to spend, so I used the filter formula in Sheets to get from this:

to this:

I made a separate page for our literacy and our math workshops.

For those of you interested, the formula was essentially this:

=filter('Form responses 1'!B:B,('Form responses 1'!D:D="Ideas"))

With Ideas being the name of the workshop. I've further simplified it by using this formula and only changing the headings as necessary.

=filter('Form responses 1'!B:B,('Form responses 1'!C:C=A2))

The A2 just references the top of the list. By separating these into lists it was very easy to cut and paste them into another document for displaying in the habitat:

The next trick was to make sure that our learners were able to look at these and know in advance where and when they needed to be. Having Apple TVs on all of our Habitat TVs proved to be a slight advantage. While I couldn't get our Google Slides to play sans device, I was able to use Flickr to create albums each day so that all the relevant information would cycle through the TVs throughout the day so that the information would always be accessible.

Each week, to save work, we "Copy to" the sheets we sort onto (eg. the Math Workshops) into the new Spreadsheet created for the new form.

But I wrote above that we also wanted to make sure we'd have all of our learners choosing workshops. One (clumsy) solution was for us to go in and check them one-by-one. But I don't like that and it takes a lot of time (and it's not visual).

So I did a bit of googling and found a very useful formula "on the line."

=IF(ISERROR(MATCH(A1,'Form responses 1'!B:B,0)),"Not Registered","Registered")

Essentially what this does is it checks the value in A1 (or A2, A3, A4, etc when you copy it in every line) and sees if it has turned up in Column B in the initial responses. So all I had to do was get a list of all of our 90 learners and put it in Column A and then copy this formula into all of Column B, add a quick conditional formatting (Green if it's Registered, Red if it's Not Registered) and this is what we get:

All but one of our learners have registered this week (and the one who hasn't is in Queenstown).

So that's how we're currently organizing our workshops. It seems like a lot, but once set up, it's very easily copied into a second, third, etc week. Hopefully this has been helpful. I'll make an attempt to share more of my organizational tips on here and if anyone has any ideas of how to improve this system, I'd love to hear from you!


  1. Hi Michael,

    This looks very cool!!! I like how you have sourced the formula to help check that all learners are attending a workshop. This could be a great way for us to offer workshops based on the maths learning progressions and so much more.

    You have given me heaps to think about and how we could use this system in LH6.

    Loving your work.


  2. Hi Michael,
    I agree with Lisa. Very innovative approach. I am anxiously awaiting a future reflection where we find out that it is still working, learners have choice and voice and are in the right place at the right time doing the right thing (whatever that may be!). I will be provocative and challenge your initial statement "organization is both key and very messy" I agree that it is key, but it should not be messy IMO as mess implies that things are not in the right place at the right time, it may also imply that not all team members are clear of the process. Do you think this is scalable? Is the process 'stand-alone' now or does it need 'Magic Michael' to continue? Important questions as SLT to consider.

    I admire your tenacity, resilience and patience. Keep going Canuck!

  3. Hi Michael,
    What a brilliant idea!!! I thoroughly enjoyed reading your innovative approach. Now I am thinking how do I simplify this approach for LH2 learners.
    Oresome work