Complicated Formula Needed???

[coliervile]

I have some worksheets (named 1-8) that keep baseball statistics for each game played. I have other worksheets (named BLG1-GLB8) that are used as a baseball scorecard to keep score and statistics during the game. I want to link or transfer the statistics from the scorecard worksheet to the baseball statistic worksheet. Here are a few examples of the worksheets. It may take several messages to copy the worksheets so that you can get a clear picture of what I'm trying to do.

Best regards,

Charlie

Baseball Statistic Worksheet:

20080614 Tournament.xls
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
7
8	Player'sName	Hitting														BaseRunning
9	Hitter/Runner	G	PA	AB	H	RBI	1B	2B	3B	HR	BB	HBP	SaF	SaB	K	R	SB	CS	LOB
10	Bradley,Colton
11	Cantu,Alex
12	Hamelink,Lucas
13	Herson,Jake
1

Baseball Scorecard Worksheet:

20080614 Tournament.xls
	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC
3		GameTotals				Game#			1							6			4
4		R	H	E		Date			6/14/2008								1
5		0	0	0		Location			Jackson,MI						5					3
6		0	0	0		Opponent			EatonRapidsGreyhounds
7																	2
8									IndividualGameTotals
9						Hitting								BaseRunning				Fielding
10	PA	H	RBI	1B	2B	3B	HR	BB	K	HBP	SaB	SaF	DI	R	SB	CS	LOB	Asst	PO	E
11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12
13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14
15	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16
17	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18
GLB1

 

[coliervile]

What I would like to do; is if the game number, in the worksheets named GLB-GLB8 cell "CR3", matches the game number on the statistic worksheet, named 1-8 cell "AD3", and then if the players name on the baseball statistic worksheet, named 1-8 cell "C10", matches the name found in the range, "D11:D117" on the baseball scorecard worksheet, named GLB1-GLB8, then copy the cells value for example baseball scorecard GLB1- 1B (cell "CM11") and putthe cells value in the statistic worksheet (cell "I10"). Is this clear????

Best regards,

Charlie

20080614 Tournament.xls
	CO	CP	CQ	CR	CS	CT	CU	CV
3	Game#			1
4	Date			6/14/2008
5	Location			Jackson,MI
6	Opponent			EatonRapidsGreyhounds
GLB1

20080614 Tournament.xls
	AB	AC	AD	AE	AF	AG
2	GameBoxScore
3	Game#		1
4	Date		6/14/2008
5	Location		Jackson,MI
6	Opponent		EatonRapidsGreyhounds
1

20080614 Tournament.xls
	C	D	E	F
8	Player'sName	Hitting
9	Hitter/Runner	G	PA	AB
10	Bradley,Colton
1

20080614 Tournament.xls
	C	D	E	F
9	NO.	PlayerBattingOrder	POS
10		1		RB
11				1B
12				2B
13				3B
14				HR
15				BB
16				K
17				HP
18				SH
19		2		RB
20				1B
21				2B
22				3B
23				HR
24				BB
25				K
26				HP
27				SH
GLB1

 

[coliervile]

Here's the other worksheet information; I want the formula to go into worksheet "1" cell I10 for "1B". Hope that all of this make sense???

Best regards,

Charlie

20080614 Tournament.xls
	CL	CM	CN	CO
10	RBI	1B	2B	3B
11	0	0	0	0
12
13	0	0	0	0
14
15	0	0	0	0
16
17	0	0	0	0
18
GLB1

20080614 Tournament.xls
	H	I	J	K
9	RBI	1B	2B	3B
10
11
12
1

 

[coliervile]

Lets go about this another way. Here's what I would like to do:

1) If the players name on worksheet named "1" cell "C10" is found in worksheet named "GLB1" range "D11:D117"

2) then copy cell value from worksheet named "GLB1" column "CM" same row as in range "D11:D117" to worksheet named "1" cell "I10"

example:

If player's name "Smith, Tom" is in worksheet named "1" cell "C10" and is found in worksheet named "GLB1" cell "D29" (in the range "D11:D117") then copy cell value in worksheet named "GLB1" cell "CM29" to worksheet named "1" cell "I10"

Is this a bit more clear???

Best regards,

Charlie

 

[coliervile]

I'm trying to use the follwing formula to return the cells value on worksheet named "GLB1" cell "CM11" to worksheet named "1" cell "I10"

the following formula is in cell "I10" on worksheet named "1"-

=IF(ISNA(VLOOKUP(C10,GLB1!D11:D117,91,FALSE)),"",IF(VLOOKUP(C10,GLB1!D11:D117,91,FALSE)="","",VLOOKUP(C10,GLB1!D11:D117,91,FALSE)))

The formula looks in cell "C10" (players name) and then does a VLOOKUP of the player's name from cell "C10" on worksheet "GLB1" range D11:D117 (there are empty cells in this range) and if there is a match then copy the cell's value from cell from column 94- "CM" to workshet named "1"

I'm getting the following error: #REF!

Any ideas???

Best regards,

Charles Llamas

 

[coliervile]

I corrected the range on worksheet named "GLB1" to include column 91 or "CM", but I'm not getting the correct result. The player's name that is in cell "C10" on worksheet named "1" is in cell "D11" on worksheet named "GLB1" and in cell "CM11" there is a one, "1", and the one, "1", should show up in cell "I10" (the formula is in this cell) on worksheet named "1".

Any ideas???

Best regards,

Charlie

=IF(ISNA(VLOOKUP(C10,GLB1!D11:DC117,91,FALSE)),"",IF(VLOOKUP(C10,GLB1!D11:DC117,91,FALSE)="","",VLOOKUP(C10,GLB1!D11:DC117,91,FALSE)))

20080614 Tournament.xls
	C	D	E	F	G	H	I
9	Hitter/Runner	G	PA	AB	H	RBI	1B
10	Bradley,Colton						0
1

20080614 Tournament.xls
	C	D	E	F
9	NO.	PlayerBattingOrder	POS
10		1		RB
11	7	Bradley,Colton		1B
12				2B
GLB1

20080614 Tournament.xls
	CL	CM	CN	CO
9				Hitting
10	RBI	1B	2B	3B
11	0	1	0	0
12
GLB1

20080614 Tournament.xls
	H	I	J	K
9	RBI	1B	2B	3B
10		0	0	0
11
1