Populate Table Based On Data In Another Table

[t0ny84]

Hi All,

I am wondering if anyone knows if the following is able to be done within Excel and if so how (preferably without a macro).

Excel Document includes 2 tables
tblStaffData - This includes everyone who works within a company's data, e.g. name, dob, employee id, etc.

tblTeam1 - This list needs to auto populate based upon the team name put next to a persons name in the tblStaffData table.

e.g.
tblStaffData -
Name John Smith
Team - Team 1

tblTeam1 would then show John Smith in that table, if his team was changed to Team 2 Excel would then remove him from Team 1 table and add him into Team 2 table.

I hope this makes sense as what I think in my head and what I want to say sometimes don't end up being the same. I do not currently have any demo\temp document I can upload to play around with as I haven't yet created this document I am just wanting to find out if it would be possible!

Thanks in Advance!
t0ny84

 

[Brunts]

Macro - yes, formulas - don't think so. How can you redefine the size of a Table and then add / delete rows using formulas only?
I'm having enough difficuly in coming up with a formula to dynamically identify everyone in Team 1 without getting an error message when there are no more

 

[Brunts]

1 - I needed to fix the size of tblTeam1 as something too big for expected numbers
2 - I also needed to insert a number column so that the formula knew which the index number of the team member it was looking for

However, if someone moves into or out of Team 1, then the info does change

Excel Forum.xlsx
	A	B	C	D	E	F	G	H
2	Name	dob	Employee ID	Team Name		No	Name	Team
3	Employee 1	15/01/1980	A0202100001	Team 1		1	Employee 1	Team 1
4	Employee 2	16/02/1981	A0202100002	Team 6		2	Employee 4	Team 1
5	Employee 3	17/03/1982	A0202100003	Team 4		3	Employee 7	Team 1
6	Employee 4	18/04/1983	A0202100004	Team 1		4	Employee 10	Team 1
7	Employee 5	19/05/1984	A0202100005	Team 3		5	Employee 13	Team 1
8	Employee 6	20/06/1985	A0202100006	Team 6		6
9	Employee 7	21/07/1986	A0202100007	Team 1		7
10	Employee 8	22/08/1987	A0202100008	Team 4		8
11	Employee 9	23/09/1988	A0202100009	Team 3		9
12	Employee 10	24/10/1989	A0202100010	Team 1		10
13	Employee 11	25/11/1990	A0202100011	Team 3
14	Employee 12	26/12/1991	A0202100012	Team 4
15	Employee 13	27/01/1992	A0202100013	Team 1
Sheet5

Cell Formulas
Range	Formula
G3:G12	G3	=IFERROR(INDEX(tblStaffData[Name],SMALL(IF("Team 1"=tblStaffData[Team Name],ROW(tblStaffData[Team Name])-ROW($A$3)+1),F3)),"")
H3:H12	H3	=IF(G3="","","Team 1")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Brunts]

Don't need the "No" column after all - the things you think off when watching Rugby!

Excel Forum.xlsx
	A	B	C	D	E	F	G
2	Name	dob	Employee ID	Team Name		Name	Team
3	Employee 1	15/01/1980	A0202100001	Team 1		Employee 1	Team 1
4	Employee 2	16/02/1981	A0202100002	Team 6		Employee 4	Team 1
5	Employee 3	17/03/1982	A0202100003	Team 4		Employee 7	Team 1
6	Employee 4	18/04/1983	A0202100004	Team 1		Employee 8	Team 1
7	Employee 5	19/05/1984	A0202100005	Team 3		Employee 10	Team 1
8	Employee 6	20/06/1985	A0202100006	Team 6		Employee 13	Team 1
9	Employee 7	21/07/1986	A0202100007	Team 1
10	Employee 8	22/08/1987	A0202100008	Team 1
11	Employee 9	23/09/1988	A0202100009	Team 3
12	Employee 10	24/10/1989	A0202100010	Team 1
13	Employee 11	25/11/1990	A0202100011	Team 3
14	Employee 12	26/12/1991	A0202100012	Team 4
15	Employee 13	27/01/1992	A0202100013	Team 1
Sheet5

Cell Formulas
Range	Formula
F3:F12	F3	=IFERROR(INDEX(tblStaffData[Name],SMALL(IF("Team 1"=tblStaffData[Team Name],ROW(tblStaffData[Team Name])-ROW($A$3)+1),(ROW()-ROW($F$2)))),"")
G3:G12	G3	=IF(F3="","","Team 1")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[t0ny84]

@Brunts you are a BEEPing AWESOME!
Thanks so much!
Your formula has opened SO SO SO many new possibilities for me!

t0ny84