# Extracting data range results to seperate worksheet

#### psu1999

##### New Member
I have a data range with 4 columns: First Name, Last Name, Team, Age.
This range has all the teams combined and is currently 210 rows in size.

What I want to do is place each team on its own worksheet and have it so that if I add a new person to the data range, it will put that person on the correct worksheet based off of the Team column.

Could anyone point me in the right direction? If I need to add more clarification, please let me know.

Bob

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### gardnertoo

##### Well-known Member
This can be done using array formulas. Consider the following set of team data:
Book1
ABCD
1First NameLast NameTeamAge
2BenjaminLestrongeGiants24
3CesarManuelitoGiants24
4LetishaPaethEagles25
5KelvinGettCowboys22
6RoyFranceFalcons21
7DorseyChessonEagles26
8JaquelineMcdivittCowboys21
9NewtonSilbaughEagles22
10JennetteVandervoortCowboys26
11RochelDigerolamoColts21
13AvaBrandewieColts21
14EleonorYokeEagles23
16TiannaShuttlesworthCowboys22
17KerryDominquezGiants26
18BarneySilerEagles23
19HectorFlorekColts26
20PasqualeCrostonFalcons26
21ColinHallettFalcons23
22NishaFoardFalcons22
23RudyLagosFalcons24
24MaxHutchinsonGiants22
25
26
27
28
29
30
Sheet1

The team sheet for the Falcons could then look like this:

The formula in cell B2 simply counts how many Falcons there are on the main page. This number is not used in the array formulas below it, it's for the user's information only. To build the matrix below, select from cell A5 down to as many members you ever expect any given team to have, plus a few. If you expect teams of twenty, I would select thirty rows so there is plenty of room for future expansion. Once you have the cells selected, enter this formula in the formula bar, and confirm it by hitting CONTROL + SHIFT + ENTER instead of the usual ENTER alone. This tells Excel to resolve the formula as an array:

=(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!\$C\$2:\$C\$301=\$B\$1,ROW(Sheet1!\$C\$2:\$C\$301),99),ROW(INDIRECT("1:"&ROWS(Sheet1!\$C\$2:\$C\$301)))))&"")

This formula looks through the "Team" column on Sheet1 (Change that reference if your master page has a different sheet name), looking down through row 301 (farther than your current roster to capture future additions), and returns the Column A data (first names in your case) which correspond to "Falcons" entries only. They are returned in the same order they are listed in the Sheet1 worksheet. You may then use the fill handle at the bottom right corner of the array to drag the formulas to the right to populate columns B, C, and D.
Someday you may want to add more columns to the master sheet, maybe column E will be position, column F runs, column G hits, column H errors, etc.... You can drag the formulas to the right as many columns as you want, and the references will update.

You might notice in the snapshot of my spreadsheet that the formula in column C doesn't exactly match the others. Instead of:
=(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!\$C\$2:\$C\$301=\$B\$1,ROW(Sheet1!\$C\$2:\$C\$301),99),ROW(INDIRECT("1:"&ROWS(Sheet1!\$C\$2:\$C\$301)))))&"")

you find this:
=(INDEX(Sheet1!C:C,SMALL(IF(Team=\$B\$1,ROW(Team),99),ROW(INDIRECT("1:"&ROWS(Team)))))&"")

I've created a named range on Sheet1 that expands and contracts to the exact number of rows for which there is team data. That named range means I will not have to change the array formulas on the team pages if my master roster grows beyond row 301. On Sheet1, select cell C1. Click on Insert, then Name, then Define... The name will default to the value in the selected cell, so it should already be called "Team". Enter the following formula in the "Refers to" block at the bottom of the dialog box, replacing what Excel put there:
=OFFSET(Sheet1!\$C\$1,1,0,COUNTA(Sheet1!\$C:\$C)-1,1)

edit: I see that when I built the formulas, Excel inserted the Sheet2! reference to the same-sheet cell references. In the explanatory text I have removed that, so you can use the formulas as presented in the text on a sheet of any name.

Last edited:

#### psu1999

##### New Member
Thank you so much for the quick response. That did the job perfectly. I will read it over and make sure I understand everything that the formula does..very thorough!

Bob

#### Peter_SSs

##### MrExcel MVP, Moderator
Bob

You might also have a look at my suggestion in post #3 of this thread. It does avoid the need for array formulas, but does use a 'helper' column on the original list sheet. (This helper column could be hidden once you have populated the formulas in it)

Replies
0
Views
300
Replies
7
Views
1K
Replies
0
Views
278
Replies
5
Views
282
Replies
17
Views
602

1,190,786
Messages
5,982,916
Members
439,807
Latest member
WXM86

### 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.

### Which adblocker are you using?

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

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