Dynamically auto-populate list based on cell values

[Graeme_Clark2295]

Hi

I wondered if anyone could possibly help. In my quest to make a more dynamic assessment system for my school, I'm stuck on a formula. I've searched and search and just can't quite find exactly what I need.

All pupils are given either the value of STRETCH or PAUSE as can be seen in the image below. I need to, based on these values, create two lists on another sheet which are dynamically populated by these pupils; one list of pupils with STRETCH against their name and another list of pupils with PAUSE against their name.

Could anybody help? I'd be so grateful.

[#2]

@Graeme_Clark2295 

Let's say the names are in B2:B100 and the STRETCH/PAUSE values in J2:J100 on a sheet named Student Sheet.

On another sheet in for example A2 to list the STRETCH students:

=FILTER('Student Sheet'!B2:B100, 'Student Sheet'!J2:J100="STRETCH", "")

And in for example C2 to list the PAUSE students:

=FILTER('Student Sheet'!B2:B100, 'Student Sheet'!J2:J100="PAUSE", "")

The formulas will spill to as many cells as needed.

[#3]

@Hans Vogelaar 

Thank you so much!

That's done the job nicely. I really appreciate it.