Using Validation I think???

[Riptide]

I am trying to create a workbook for a fantasy Hockey league I am in. Here
is what I am trying to do. I have a validation on Sheet 1 in cell A6 which
indicates the players position (LW, RW, C, D, G). My LW's are located on
sheet 2 and Goalies on sheet 3. Once I choose a players position in cell A6
on sheet 1, I want to then have a validation or list in B6 which tells excel,
Look for all players on Sheet 2 from cell A1 to A100. The idea is that once
I select a players postion I can then go to cell B6 and see an entire list of
players that play LW. Likewise, if I were to select the G position it will
give me a list of all the goalies on sheet 3 and so on. Is this possible? It
just reduces the time it takes to insert each name at a time.

Thank you,

 

[T. Valko]

One way:

Give each of the player lists a defined name that refers to their positions.
For example, if the LW's are listed on Sheet2 in the range A1:A100. Name
that range LW. Do the same for the other lists. Note however, C is a
reserved name in Excel so you'd have to come up with a different way of
designating Centers.

Then, as the source for the drop down list in B6 use a formula like this:

=INDIRECT(A6)


Go Pens!

 

[Riptide]

It worked, thanks a lot, can't believe how simple that formula was. Good
luck in the playoffs, I hope the devils can squeeze past the rangers.

 

[Riptide]

One more question, but based on previouse posts that you replied to, it
appears as though this is a vlookup issue. Running on the same sheet as my
previous question. cell B6 on sheet 1 now shows the list of players names.
Now I want cell C6 on sheet 1 to add that players salary from whatever sheet
that player is on. I named the range of cells which includes the players
cost as "cost". I was using this formula given to me by a fellow fantasy
player, however, I think the column index, and range look-up is creating the
issue for me, which means that he probably just copied and paste the formula
from some one else:

=IF(B6="","",VLOOKUP(B6,INDIRECT(CONCATENATE("_",A6,"Cost")),2,0))

Thanks,

 

[T. Valko]

Assume:

A6 = LW

LW is also a named range that refers to Sheet2 A1:A100
The players salary is B1:B100

B6 = players name

To lookup the players salary:

=OFFSET(INDEX(INDIRECT(A6),1),MATCH(B6,INDIRECT(A6),0)-1,1)

 

[Riptide]

Once again, thank you for your help, Don't be surprised if I am asking you
another question about this fantasy workbook.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Once again, thank you for your help, Don't be surprised if I am asking you
another question about this fantasy workbook.