Complicated Formula Needed???

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
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
CDEFGHIJKLMNOPQRSTU
7
8Player'sNameHittingBaseRunning
9Hitter/RunnerGPAABHRBI1B2B3BHRBBHBPSaFSaBKRSBCSLOB
10Bradley,Colton  
11Cantu,Alex  
12Hamelink,Lucas  
13Herson,Jake  
1


Baseball Scorecard Worksheet:
20080614 Tournament.xls
CJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDC
3GameTotalsGame#164
4RHEDate6/14/20081
5000LocationJackson,MI53
6000OpponentEatonRapidsGreyhounds
72
8IndividualGameTotals
9HittingBaseRunningFielding
10PAHRBI1B2B3BHRBBKHBPSaBSaFDIRSBCSLOBAsstPOE
1100000000000000000
12
1300000000000000000
14
1500000000000000000
16
1700000000000000000
18
GLB1
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
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
COCPCQCRCSCTCUCV
3Game#1
4Date6/14/2008
5LocationJackson,MI
6OpponentEatonRapidsGreyhounds
GLB1
20080614 Tournament.xls
ABACADAEAFAG
2GameBoxScore
3Game#1
4Date6/14/2008
5LocationJackson,MI
6OpponentEatonRapidsGreyhounds
1
20080614 Tournament.xls
CDEF
8Player'sNameHitting
9Hitter/RunnerGPAAB
10Bradley,Colton 
1
20080614 Tournament.xls
CDEF
9NO.PlayerBattingOrderPOS
101RB
11 1B
122B
13 3B
14HR
15 BB
16K
17 HP
18SH
192RB
20 1B
212B
22 3B
23HR
24 BB
25K
26 HP
27SH
GLB1
 

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
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
CLCMCNCO
10RBI1B2B3B
110000
12
130000
14
150000
16
170000
18
GLB1
20080614 Tournament.xls
HIJK
9RBI1B2B3B
10
11
12
1
 

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
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

Well-known Member
Joined
May 19, 2006
Messages
724
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

Well-known Member
Joined
May 19, 2006
Messages
724
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
CDEFGHI
9Hitter/RunnerGPAABHRBI1B
10Bradley,Colton  0
1
20080614 Tournament.xls
CDEF
9NO.PlayerBattingOrderPOS
101RB
117Bradley,Colton1B
122B
GLB1
20080614 Tournament.xls
CLCMCNCO
9Hitting
10RBI1B2B3B
110100
12
GLB1
20080614 Tournament.xls
HIJK
9RBI1B2B3B
10000
11
1
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top