Data automatically filling to another sheet?

I have a master list of a team on sheet 1

column a column b column c
NAME JERSEY# TEAM NAME
name 1 #21 red
name 2 #32 white
name 3 #6 blue

On sheet 2 I want to create a list for the "red" team, sheet 3 the "white" team, and sheet 4 the "blue" team without having to copy/paste because there will be over 300 kids listed on the master sheet.

Is there a formula that will look at column c and say "if column c = red, insert name and jersey number in sheet 2, column a, row 1" ??

I hope this question makes sense.

Thanks,
Steve

Originally Posted by sktneer

Try this......

On sheet 2 and in cell A2, place the formula given below. This is an array formula so it needs to be confirmed with Ctrl+Shift+Enter and then drag across and down the cell.

Please Login or Register  to view this content.

Is this what you want?

Please find the attached sheet.

EXACTLY what I needed..thank you so much!! Now, I'm trying to edit your formula by replacing row 15 with row 300 across the formula and it isn't letting me. Do I need to hold ctrl,shift,enter on sheet 1 to make that change?