If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Searching based on text
I am building an Excel to recall stats for TV sports. There are three
workbooks, Home, Away and League. The league sheet has all of last season's stats. In all workbooks the player's name is in column A. Because each player has different stats based on his position, I want to select the columns returned based on a text string. Example: In cell A4 of the home sheet is the name JOE SMITH. In A4 is the text CB. In cell B5 is the name JOHN DOE and in B4 the text QB. For the CB it needs to return stats from column G, H, J and N based on the player's name. For the QB it needs H, I, J, K, L and M. Is this possible? |
#3
|
|||
|
|||
Searching based on text
I think you have a typo in your example (for row 4).
This may get you closer: =vlookup(B5,if(b4="QB",league!H:M,if(b4="CB",leagu e!G:N,lots more here),2,false) That if statement in the =vlookup() just points at the other ranges. If the formula gets too complex, you could create a new sheet with a table on it: ColA Cols QB H:M CB G:N ....(lots more) Then you'd have to use =indirect() to change that string to a real range reference. =vlookup(b5,indirect("'league'!"&vlookup(b4,Table! a:b,2,false)),2,false) =indirect() is a volatile function. It'll recalc whenever excel recalculates. So if you use lots of them, it may slow down calculations. But it makes the formula easier to use (I think). And when I'm doing this kind of stuff, I'm retrieving the values and as soon as I get it right, I convert the formulas to values. You may want to consider that, too. EJ Hill wrote: I am building an Excel to recall stats for TV sports. There are three workbooks, Home, Away and League. The league sheet has all of last season's stats. In all workbooks the player's name is in column A. Because each player has different stats based on his position, I want to select the columns returned based on a text string. Example: In cell A4 of the home sheet is the name JOE SMITH. In A4 is the text CB. In cell B5 is the name JOHN DOE and in B4 the text QB. For the CB it needs to return stats from column G, H, J and N based on the player's name. For the QB it needs H, I, J, K, L and M. Is this possible? -- Dave Peterson |
#4
|
|||
|
|||
Searching based on text
Yes, there's a typo. I shouldn't post when I'm exhausted.
I'll try your suggestions. The formulas are going to get complex because there are 11 different player positions that need to sorted. The good news is that football teams only play once a week so after the formula's run once, I'm golden. I can't do much with the way the data is entered as it is done on a web query. In the past I manually entered the all the data into the graphics machine on game day. |
#5
|
|||
|
|||
Searching based on text
Dave -
Tried your suggestions. I kept getting a "too many arguments" error. Could I be searching through too many records? There are almost 1500 players in that registered stats in the league last season... |
#6
|
|||
|
|||
Searching based on text
I'd say there was a mistake in either the formula I posted and you modified --
or a mistake in the way you modified the suggested formula. I think it's time to post the formula you used. EJ Hill wrote: Dave - Tried your suggestions. I kept getting a "too many arguments" error. Could I be searching through too many records? There are almost 1500 players in that registered stats in the league last season... -- Dave Peterson |
#7
|
|||
|
|||
Searching based on text
=vlookup(B4,if(b4="QB",'Roster!'N4:AM110league!,'R oster'!17,False))
if(b4="K",Roster!N4:AM110league!,'Roster'!25,False ) I modified the sheet so that the stats all report to the end of the "Roster" worksheet. But this says it doesn't like the pathing name 'Roster' |
#8
|
|||
|
|||
Searching based on text
You're going to end up with the =indirect() version. You have too many criteria
for the =if() statement to handle. (Unless you're using xl2007, you can't nest those 11 conditions in the =if() portion of the formula.) And I'm confused about what you want to lookup. I thought B5 was going to contain the name of the player and B4 would contain his position. If that's true, then you're going to have to use something like: =vlookup(b5,if(b4="QB",'Roster'!N:AM, if(b4="K", 'Roster'!??:??, if(b4="CB",'Roster'!??:??, 'Roster'!??:??))),25,false) The =if() in the middle is gonna evaluate to a range on that roster worksheet. You'll have to change the ??:?? to the correct columns. Again, I think that you're gonna find that this is a non-starter. You're next attempt should be the worksheet with the table of positions and columns to use. EJ Hill wrote: =vlookup(B4,if(b4="QB",'Roster!'N4:AM110league!,'R oster'!17,False)) if(b4="K",Roster!N4:AM110league!,'Roster'!25,False ) I modified the sheet so that the stats all report to the end of the "Roster" worksheet. But this says it doesn't like the pathing name 'Roster' -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|