Cyberpunk001
New Member
- Joined
- Aug 27, 2018
- Messages
- 13
Good day experts,
I want to be able to have a team name automatically updated as I filter a database of staff.
For example I have a list of staff members belonging to a team, team being an arbitrary name, such as a supervisor name.
As the database grows and more teams are assembled, a whole list of names will appear under TEAM.
At the moment, I don't have a viable method of automatically updating cell A4 to give me the name once I have filtered for any given team name.
I have a register with a header on top, one in particular for "TEAM", filtered by team name,which is a range from NQ6:NQ118.
Whenever I filter a team name, eg, DANNY, I want cell A4 to also output "DANNY".
<tbody>
</tbody>
In Cell A4, I have inserted a formula as below:
{=INDEX(TEAM,MIN(IF(SUBTOTAL(3,OFFSET(NQ6:NQ118,ROWS(TEAM)-ROW(NQ6),0)),ROWS(TEAM)-ROW(NQ6)+1)))}
The headings are in row 5.
Here TEAM denotes a defined name, ranged from NQ6:NQ118.
Sorry guys don't know how to upload a screenshot just yet
Any help will be greatly appreciated
I want to be able to have a team name automatically updated as I filter a database of staff.
For example I have a list of staff members belonging to a team, team being an arbitrary name, such as a supervisor name.
As the database grows and more teams are assembled, a whole list of names will appear under TEAM.
At the moment, I don't have a viable method of automatically updating cell A4 to give me the name once I have filtered for any given team name.
I have a register with a header on top, one in particular for "TEAM", filtered by team name,which is a range from NQ6:NQ118.
Whenever I filter a team name, eg, DANNY, I want cell A4 to also output "DANNY".
ROW NUMBER | COLUMN A | COLUMN B | COLUMN C | COLUMN NQ |
5 | NAME | SURNAME | CLOCK NO | TEAM |
6 | NAME | DANNY | ||
7 | SURNAME | JACOB | ||
8 | NUMBER | SAMMY | ||
9 | SURNAME | CATHY |
<tbody>
</tbody>
In Cell A4, I have inserted a formula as below:
{=INDEX(TEAM,MIN(IF(SUBTOTAL(3,OFFSET(NQ6:NQ118,ROWS(TEAM)-ROW(NQ6),0)),ROWS(TEAM)-ROW(NQ6)+1)))}
The headings are in row 5.
Here TEAM denotes a defined name, ranged from NQ6:NQ118.
Sorry guys don't know how to upload a screenshot just yet
Any help will be greatly appreciated