Complicated Formula Needed???

coliervile

Well-known Member
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
11Cantu,Alex
13Herson,Jake
1

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

coliervile

Well-known Member
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
2GameBoxScore
3Game#1
4Date6/14/2008
5LocationJackson,MI
6OpponentEatonRapidsGreyhounds
1
20080614 Tournament.xls
CDEF
8Player'sNameHitting
9Hitter/RunnerGPAAB
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
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

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
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
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
1
20080614 Tournament.xls
CDEF
9NO.PlayerBattingOrderPOS
101RB
122B
GLB1
20080614 Tournament.xls
CLCMCNCO
9Hitting
10RBI1B2B3B
110100
12
GLB1
20080614 Tournament.xls
HIJK
9RBI1B2B3B
10000
11
1

Replies
0
Views
732
Replies
2
Views
1K
Replies
2
Views
669
Replies
0
Views
665
Replies
2
Views
333

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,652
Messages
5,832,928
Members
430,175
Latest member
Sheenamarie

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.

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

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