Update cell of filtered column header selection

Good day all,

I would very much like to have a certain cell reference(A4) be updated with the team leader I select on the header filters.
Reason being, I have to print out the whole team for every team leader, and have the leader's name on top of the list.

I have a database with staff members belonging to different team leaders, for instance 30 random members belonging to a certain leader.
This database has many headers, of which the TEAM header is of importance here.

The structure looks like this:

CELL A4, NAME OF SELECTED TEAM ON HEADER FILTER.

ROW NUMBER COLUMN A COLUMN B COLUMN NQ
5 "NAME" "SURNAME" "TEAM"
6 RANDY DE BROGLI JAMES
7 SANDY REYNOLDS JAMES
8 GILL ABRAHAMS JAMES


So as I click the filter on the header "TEAM", cell ref NQ5, and select "Team leader 1", CELL A4 should automatically update with "Team leader 1".

Here is what I have tried so far, and it does not seem to solve the problem all that well:

=INDEX(TEAM,MIN(IF(SUBTOTAL(3,OFFSET(NQ5,ROW(TEAM)-ROW(NQ5),0)),ROW(TEAM)-ROW(NQ5)+1)))

I have made the range of Cells NQ6:NQ999 a defined name, called TEAM.
What this formula is trying to accomplish, is to look at the 1st cell below the header "TEAM", and display the result in cell A4.
You can imagine if I have multiple names in a list, all belonging to the same team, "James", and if I select "James" under team,
The first cell under the header will in fact say "James".

Hope this explains well enough what I am trying to convey.

Any help would be awesome!

Hi FlameRetired,

Thanks so much for the quick reply, and sound advice!

I have been waiting for a reply from another forum for weeks on end.

Don't know what I did wrong before, but now the solution works!
This is an array formula that I forgot to mention.

Hope this solution helps someone looking for this particular answer.