Hi I've got a feeling I'm missing something very simple, but would appreciate help
I'd like to create a table that is a subset of an existing table, but I don't think a pivot table or Advanced Filter are going to work
In Table_1 I have a column 'Pupil ID' and a column 'Year'.
In Table_2[Pupil ID] I want the column to contain the Pupil IDs from Table_1[Pupil_ID] where there is a "3" in Table_1[Year]
Table_2 needs to be automatically populated, and I need to it to be able contain other data from Table_1, correpdonding to the Pupil ID.
An Index & Match array formula doesn't seem to work because Table_2 is a proper table which doesn't allow array formulae.
I tried creating a data relationship but that didn't work because some of the cells in Table_1[Pupil ID] are blank and an error message indicates thinks these are duplicate values.
Any tips greatly appreciated
I'd like to create a table that is a subset of an existing table, but I don't think a pivot table or Advanced Filter are going to work
In Table_1 I have a column 'Pupil ID' and a column 'Year'.
In Table_2[Pupil ID] I want the column to contain the Pupil IDs from Table_1[Pupil_ID] where there is a "3" in Table_1[Year]
Table_2 needs to be automatically populated, and I need to it to be able contain other data from Table_1, correpdonding to the Pupil ID.
An Index & Match array formula doesn't seem to work because Table_2 is a proper table which doesn't allow array formulae.
I tried creating a data relationship but that didn't work because some of the cells in Table_1[Pupil ID] are blank and an error message indicates thinks these are duplicate values.
Any tips greatly appreciated