formulating if; then; enter list

[striker92s]

I am new to excel but have worked with speadsheets (MS works) I need
formula that when I enter a team name into cell A5 it automaticall
inserts the team players into cells H4-19. I know its a combination o
lookup and reference. what should the formula be

 

[Biff]

Hi!

You have to build a table with all the team names and
players. Then, the formula(s) depends on how the table was
constructed.

How many teams are there? The number of teams can also
influence what type of formula(s) to use. If there aren't
too many teams you could use an IF formula along with
named ranges for the players. It could be as easy as:

=IF(A5="Team 1",Team_1,"")

Team_1 being a named range that holds the players names
for Team 1.

Post back with more details.

Biff

 

[Max]

Maybe try this ..

Assume the 16 team players' names are listed in Sheet1 in A1:A16
and this range A1:A16 is named as: MyTeam

In say, Sheet2
-----------------

Select H4:H19

Put in the formula bar:
=IF(A5="","",IF(ISERROR(INDIRECT(A5)),"",INDIRECT(A5)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Now try input in A5: MyTeam
The range H4:H19 will be populated with the 16 team players names