Have a few questions for an excel guru

[diogee]

I'm looking to work on some sheets over the winter during some downtime at work and wondering if it is possible to program this macro or use some other method to perform the task.

I am looking for a site where I can import the match-ups with teams and lines only which I will have in sheet 1.
In sheet 2 I am looking to import stats for every team via statfox.

Now the tricky part that I am wonder about. How difficult/possible is it to have sheet 2 sort for certain stats for each team listed in sheet 1 and place them in the appropriate column for that team in sheet 1.

ex for Buffalo/Ball st:
Sheet 1 would have the team names in A2/A3 with the line for that team in B2/B3 and then have a column for say total offensive yards per game (C2/C3) and total yards allowed per game for each team (D2/D3).

For sheet 2 I would like to auto import the updated stats from statfox upon opening and have it search for each of the listed teams on sheet 1 and then take the data out of (offensive yards per game, defensive yards per game etc) and automatically put that data into the appropriate space next to each team on sheet 1.

I'm sure this will consume a ton of my time but I should have plenty of time in the upcoming months. Just wanted to make sure this can be done before starting the journey. Any help/guiding to some reading on the subject would be greatly appreciated.

Thanks in advance.

[diogee]

Thinking a formula in the A column of sheet 1 something with IF(a1=arizona then I would have a formula to pull the stats from the appropriate columns of sheet 2 which would remain constant and in alphabetical order (if A1 was indeed arizona). I would have every team in that initial formula (with their specific column/row for the needed stats) and insert it into each row of column A. Would that work if I had each team in that formula for every row in that column so it would pull data from sheet 2 corresponding with the team name on sheet 1?

[dcbt]

it sounds like you want to make use of the 'vlookup' feature...

say you have arizona in A2 of sheet1 and you want to bring in yards into B2 of sheet1 FROM sheet2. and set up sheet2 so that "arizona" is in column A and yards is in another column (assume B). use the vlookup in B2 of sheet1 - it will 'look' to A2 and find that exact text on sheet2 ("arizona") and bring in the corresponding column you tell it to from sheet2 (in this case, column 2, since that is B.) Use the formula wizard thingee to get the syntax right.

edit to add that the great thing about vlookups is that the order doesn't matter - you don't need to keep all your records in any order since it looks to the text in one cell and finds it anywhere in a range somewhere else that you define. (you just have to be careful of typos since the text has to be exact for what it's looking for - but you'll know it b/c it will return #N/A if there are any errors.)

[diogee]

it sounds like you want to make use of the 'vlookup' feature...

say you have arizona in A2 of sheet1 and you want to bring in yards into B2 of sheet1 FROM sheet2. and set up sheet2 so that "arizona" is in column A and yards is in another column (assume B). use the vlookup in B2 of sheet1 - it will 'look' to A2 and find that exact text on sheet2 ("arizona") and bring in the corresponding column you tell it to from sheet2 (in this case, column 2, since that is B.) Use the formula wizard thingee to get the syntax right.

edit to add that the great thing about vlookups is that the order doesn't matter - you don't need to keep all your records in any order since it looks to the text in one cell and finds it anywhere in a range somewhere else that you define. (you just have to be careful of typos since the text has to be exact for what it's looking for - but you'll know it b/c it will return #N/A if there are any errors.)

Awesome...thank you dcbt. I'll give it a go.