I have an Office 2007 spreadsheet that I use to scout sports teams. My first worksheet is set up with 120 teams listed from A1 down to A120. In the columns to the right of each team I have their offensive rank (B1 to B120), and points-per-game (C1 to C120), defensive rank (D1 to D120) and points-per-game (E1 to E120).

I use this sheet as a weekly data dump as ranks and points-per-game change (but the order of the teams remains the same - alphabetical). On a separate worksheet within the workbook I compare future matchups among the 120 teams. For instance, if team #1 plays team #120 then I want to compare each team's offensive stats to each team's defensive stats, etc.

What I'd like to do is create a data validation list of the 120 teams and then based on which team I select from the list, I want the columns to the right of the team to autopopulate with the data found on the data dump worksheet for that team.

Is something like that possible?