Forum Discussion

Graeme_Clark2295's avatar
Graeme_Clark2295
Copper Contributor
May 02, 2024

Dynamically auto-populate list based on cell values

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.

excel

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 02, 2024

    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.

Resources