Populate Table Based On Data In Another Table

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Brunts

Board Regular
Joined
May 23, 2008
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
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 :unsure:
 

Brunts

Board Regular
Joined
May 23, 2008
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGH
2NamedobEmployee IDTeam NameNoNameTeam
3Employee 115/01/1980A0202100001Team 11Employee 1Team 1
4Employee 216/02/1981A0202100002Team 62Employee 4Team 1
5Employee 317/03/1982A0202100003Team 43Employee 7Team 1
6Employee 418/04/1983A0202100004Team 14Employee 10Team 1
7Employee 519/05/1984A0202100005Team 35Employee 13Team 1
8Employee 620/06/1985A0202100006Team 66  
9Employee 721/07/1986A0202100007Team 17  
10Employee 822/08/1987A0202100008Team 48  
11Employee 923/09/1988A0202100009Team 39  
12Employee 1024/10/1989A0202100010Team 110  
13Employee 1125/11/1990A0202100011Team 3
14Employee 1226/12/1991A0202100012Team 4
15Employee 1327/01/1992A0202100013Team 1
Sheet5
Cell Formulas
RangeFormula
G3:G12G3=IFERROR(INDEX(tblStaffData[Name],SMALL(IF("Team 1"=tblStaffData[Team Name],ROW(tblStaffData[Team Name])-ROW($A$3)+1),F3)),"")
H3:H12H3=IF(G3="","","Team 1")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Brunts

Board Regular
Joined
May 23, 2008
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
Don't need the "No" column after all - the things you think off when watching Rugby!

Excel Forum.xlsx
ABCDEFG
2NamedobEmployee IDTeam NameNameTeam
3Employee 115/01/1980A0202100001Team 1Employee 1Team 1
4Employee 216/02/1981A0202100002Team 6Employee 4Team 1
5Employee 317/03/1982A0202100003Team 4Employee 7Team 1
6Employee 418/04/1983A0202100004Team 1Employee 8Team 1
7Employee 519/05/1984A0202100005Team 3Employee 10Team 1
8Employee 620/06/1985A0202100006Team 6Employee 13Team 1
9Employee 721/07/1986A0202100007Team 1  
10Employee 822/08/1987A0202100008Team 1  
11Employee 923/09/1988A0202100009Team 3  
12Employee 1024/10/1989A0202100010Team 1  
13Employee 1125/11/1990A0202100011Team 3
14Employee 1226/12/1991A0202100012Team 4
15Employee 1327/01/1992A0202100013Team 1
Sheet5
Cell Formulas
RangeFormula
F3:F12F3=IFERROR(INDEX(tblStaffData[Name],SMALL(IF("Team 1"=tblStaffData[Team Name],ROW(tblStaffData[Team Name])-ROW($A$3)+1),(ROW()-ROW($F$2)))),"")
G3:G12G3=IF(F3="","","Team 1")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Solution

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
@Brunts you are a BEEPing AWESOME!
Thanks so much!
Your formula has opened SO SO SO many new possibilities for me!

t0ny84
 

Watch MrExcel Video

Forum statistics

Threads
1,127,140
Messages
5,622,949
Members
415,942
Latest member
Data Midwife

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top