Table Look Up from Multiple Tabs

Thank you for the welcoming.

I have attached the workbook with only one of the examples that I am going to use.

So far, from reading on here, I have done the first 2 columns in the "FINAL" the way that people have explained to do so. '

I am able to pull down on the league and select the appropriate team from the first list. I am, however, not sure on how to select a player and his attributes that go along with it. Is it as simple as copying the prior formula?

Thanks for the quick response!

Originally Posted by FDibbins

Thanks for the file

1. you need to put the DD back for League (it's missing)
2. You have trailing spaces in Teams A2 - remove them
3. you need to create a named range for each teams players, but I think I have a shortcut for that.

a. Go into Name Manager (in the Formula tab) and create a new name, called Player
b. under Refers to, copy this...=OFFSET(Sheet1!$C$1,MATCH(Sheet1!$I$2&"*",Sheet1!$C$2:$C$500,0),-1,COUNTIF(Sheet1!$C$2:$C$500,Sheet1!$I$2&"*"),-1)
This will create a dynamic name list based on the Team you select

4. under Data Validation, select List and in Refers To enter =Player

Forgive my ignorance, as I am still pretty new to all of this. I know it must get frustrating to have people like me, come in here and ask for this much.

1. In terms of DD, do you mean the date?

2. I have re-defined the spaces for the teams. Thanks for that.

3. When I put the formula in, the list was for every person on the sheet. I'm sure I didn't set-up up the area right.

I have attached the file again with the 2nd fix.

Thanks for the help so far FDibbins!

Great, everything has worked out so far from what I've asked! That's a huge help. Thank you for that.

I have run into a problem though. As soon as I try and drag down the data (attached) in the "Final" sheet, it continues to look for the 1st row. I don't want to start messing around with formulas because I don't have enough knowledge to back track.

Also, Happy 4th of July to all of the Americans out there. Hopefully you are all enjoying responsibly(ish).

Great, everything has worked out so far from what I've asked! That's a huge help. Thank you for that.

I have run into a problem though. As soon as I try and drag down the data (attached) in the "Final" sheet, it continues to look for the 1st row. I don't want to start messing around with formulas because I don't have enough knowledge to back track.

Also, Happy 4th of July to all of the Americans out there. Hopefully you are all enjoying responsibly(ish).

Originally Posted by FDibbins

Again, my humble apologies I total absoluted a refence I should not have. Change the Player "refers To" to this

=OFFSET(Sheet1!$C$1,MATCH(FINAL!$C3&"*",Sheet1!$C$2:$C$500,0),-1,COUNTIF(Sheet1!$C$2:$C$500,FINAL!$C3&"*"),-1)

If you are taking the time to help out, it is no problem at all.

Thanks for everything!

So after trying all of this, when I copy down the formula into the rest of the cells, it continues to look for the data from B3. Do I need to change the formula in C3 from =indirect(B3)?

Alright, so I've changed both of the lists in the "Final" table and the first drop downs are working fine.

I have moved the rows up by 1 and now the name function isn't working.

Amazing work FDibbins.

Thank you!