Conditional return based on cell in text

[chyronop]

I am building an Excel sheet to link football stats to a TV graphics computer and I've hit a road block. There are two workbooks, one for the team and one that contains all the stats for every player in the NFL for last season.

The Team sheet pulls in the roster from Yahoo sports on a web query and then pulls in the all the stats from NFL sheet.

On page 2 of the roster sheet it sorts out the players by uniform number (this is important in recalling the right player to air). Now the roadblock. I need to pull stats from five different for each player but which five depends on the player's position.

For example:
4 JOHN DOE K
17 JOE SMITH QB
16 TOM ROE WR

I need a formula that says if cell A4 says "K" return Cells 24, 25, 26, 27 and 28, if cell A4 says "QB" return Cells 10, 12, 15, 16 and 17, if cell A4 says "WR" return Cells 10, 11, 12, 13 and 16, etc. There are eleven possible positions to sort by.

Any suggestions?

 

[PA HS Teacher]

If I understand you correctly, you have various statistics in different columns across one sheet. In another sheet (in 5 different column?) you are trying to pull 5 statistics out of the many from your first sheet. The 5 statistics you could like to pull depend on the position, so each position pulls from different columns on your first sheet.

If the above is correct, I would use the match function twice, once for player number, once for position, to get the row and column numbers to feed a match function. I don't know the exact layout of your spreadsheets so I will assume that player numbers are in Column A of sheet called Data. I will also assume that in a 'Report' spreadsheet you are pulling the 5 statistics into columns C,D,E,F,G. And Column A has the player position, and Column B the player Number.

e.g.
On the 'Report' sheet in C4
=Index(Data!A1:IV1000,Match(B1,Data!A:A,0),Index({1;2;3;4;5;6;7;8;9;10;11},Match(A1,{"K","QB","WR","TE","LT","RT"},0)))

Replace the numbers above with the column numbers of the 1st statistic to be called for the corresponding position.

You would need to make a similar formula in D4,E4,F4,G4, changing the numbers to match the desired columns of the statistic.

By the way, it is also possible to use index/match to look for the statistic name on the data sheet an pull in the columnn number this way. This is the way I would actually do it, in case the column numbers change.

The above is untested.

 

[chyronop]

Right now it's reporting lines on the roster sheet.

For example:
4 JOHN DOE K 5 00 00 00 00 00 00 00 00 00 00 1-1 7-10 4-4 0-1 00 00
Here the formula needs to say, "K means I pull the 5 sets of stats starting with 1-1."

17 JOE SMITH QB 4 16 32 .500 175 1 28 4 1 00 00 00 00 00 00 00 00 00 00
Here it needs grab 16 (Comp) 32 (Att) 175 (yds) 4 (TD) and the 2nd 1 (INT)

16 TOM ROE WR 5 15 100 1.5 2 10 200 20.0 3 00 00 00 00 00 00 00 00 00 00
Here it needs to grab 15 (Recpt), 100 (yds), 1.5 (Avg), and 2 (TD)

If I didn't explain this clearly, I'd be happy to send the sheets.

The idea is that as trades are made, signings happen in camp, the rosters will update on the web query and stats will automatically pull and follow the player to the sheet db linked to the graphics machine.

 

[Philosophaie]

You could separate the data into columns using VBA. You would use the Left, Right and Mid commands. It may be a little tricky if the data is not uniform. If the data has spaces where there is potential data and each data columns are the same it will be easy to separate the data into columns.

 

[chyronop]

Each of those stats is populated into it's own cell.

17 JOE SMITH QB 4 16 32 .500 175 1 28 4 1 00 00 00 00 00 00 00 00 00 00
Here it needs grab 16 (Comp, Column P) 32 (Att, Column Q) 175 (yds, Column S) 4 (TD, Column V) and the 2nd 1 (INT, Column W)