Update cell of filtered column header selection

[Cyberpunk001]

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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ROW NUMBER[/TD]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[TD]COLUMN NQ[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]"NAME"[/TD]
[TD]"SURNAME"[/TD]
[TD]"TEAM"[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]RANDY[/TD]
[TD]DE BROGLI[/TD]
[TD]JAMES[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]SANDY[/TD]
[TD]REYNOLDS[/TD]
[TD]JAMES[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]GILL[/TD]
[TD]ABRAHAMS[/TD]
[TD]JAMES[/TD]
[/TR]
</tbody>[/TABLE]









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(NQ6,ROW(TEAM)-ROW(NQ6),0)),ROW(TEAM)-ROW(NQ6)+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!

 

[Cyberpunk001]

Good day all,

I have confirmed on Excel Forum's post that this formula does indeed work, as an array:

https://www.excelforum.com/excel-general/1244928-update-cell-of-filtered-column-header-selection.html

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