Trying To Access Tables And Stats From Baseball Reference 


Trying To Access Tables And Stats From Baseball Reference  Excel 
View Answers 
I currently have an Excel worksheet set up to take reallife baseball stats and convert them to ratings for MLB 10: The Show. Right now, I have to manually insert all of the stats I need by "hand" into the spreadsheet. However, I would like to change the way I insert stats. I want to have Excel read the name I type in, search the name on BaseballReference, and then pull the specific tables I need from the site and import them onto my spreadsheet. Anyway I can do this? I have Excel 2007. Thanks a bunch!
Similar Excel Tutorials
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Completely Hide the Quick Access Toolbar in Excel
I'll show you how to completely remove the entire Quick Access Toolbar from Excel. This will allow you to have a s ...
I'll show you how to completely remove the entire Quick Access Toolbar from Excel. This will allow you to have a s ...
Get the Name of a Worksheet in Macros VBA in Excel
How to get the name of a worksheet in Excel using VBA and Macros and also how to store that name in a variable for ...
How to get the name of a worksheet in Excel using VBA and Macros and also how to store that name in a variable for ...
Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
Helpful Excel Macros
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Hide Specific Comments in Excel  Comments Will Still Display on Hover
 Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
 Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
Excel Macro to Save a Specific Worksheet as a New File
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Excel Macro that Searches Entire Workbook and Returns All Matches
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Save the Current Worksheet as a New Excel Workbook File
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Similar Topics
I downloaded a MS Excel spreadsheet for keeping track of our 8 year old Baseball Team's game and individual stats.
I question is how do I make a seperate area in the sheet that automatically keeps a running total of each kids, batting average, hits, runs, etc.?
I have the spreadsheet, but don't know how to attach it to this for anyone to see.
Any help would be appreciated.
Thanks!
Keith
I question is how do I make a seperate area in the sheet that automatically keeps a running total of each kids, batting average, hits, runs, etc.?
I have the spreadsheet, but don't know how to attach it to this for anyone to see.
Any help would be appreciated.
Thanks!
Keith
I coach a baseball team, and want to create a "sortable stats" page in excel so everyone can see where they stand in each statistical category. I have tabs for each games' stats  they are pulled into a cumulative tab where all the stats are totaled.
Basically, i want to do a vlookup and autosort based on each category on a single page.
So the formula would have to pull the stat leader in each category and grab the players' names in descending order
The following is a rough representation of how all the stats are set up. What formula should i use to sort runs (and then replicate for each additional category)? Thanks!!!! (columns AD, Rows 14)
.....A.........B.........C..........D
1 Name...Runs
2 Smith.....5
3 Jones.....6
4 Burns.....9
5 Steel......2
Basically, i want to do a vlookup and autosort based on each category on a single page.
So the formula would have to pull the stat leader in each category and grab the players' names in descending order
The following is a rough representation of how all the stats are set up. What formula should i use to sort runs (and then replicate for each additional category)? Thanks!!!! (columns AD, Rows 14)
.....A.........B.........C..........D
1 Name...Runs
2 Smith.....5
3 Jones.....6
4 Burns.....9
5 Steel......2
So thanks to a user on this forum, I was able to develop code to pull data that was always on the same sheet and in the same cell from about 100 workbooks. It takes about 30 seconds per workbook (about an hour total) to get 98 values from each. I was initially using 2007 but now use 2003.
Is this as good as it can get? Suggestions for speeding it up? i tried to deactivate the screen refreshing, but I always got an error or it would freeze while running the code.
A bit of background on what the code does just in case: Each Client of my company has its own spreadsheet where cases (like a law firm) are entered to keep track of cases opened, closed, duration, etc. Each column tracks a different stat and there are 7 rows (2531) for 7 years over which I am tracking data. The Master spreadsheet has this macro attached to it so that it can pull all the stats from all of our clients to combine them all together. Currently we have about 100 clients.
Sub PullStats()
Dim z As Long, e As Long
Dim f As String
d = 2
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)1)"
On Error Resume Next
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) ActiveWorkbook.Name Then
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C25"
Cells(e, 2) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C26"
Cells(e, 3) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C27"
Cells(e, 4) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C28"
Cells(e, 5) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C29"
Cells(e, 6) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C30"
Cells(e, 7) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C31"
Cells(e, 8) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D25"
Cells(e, 9) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D26"
Cells(e, 10) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D27"
Cells(e, 11) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D28"
Cells(e, 12) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D29"
Cells(e, 13) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D30"
Cells(e, 14) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D31"
Cells(e, 15) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N25"
Cells(e, 16) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N26"
Cells(e, 17) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N27"
Cells(e, 18) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N28"
Cells(e, 19) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N29"
Cells(e, 20) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N30"
Cells(e, 21) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N31"
Cells(e, 22) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F25"
Cells(e, 23) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F26"
Cells(e, 24) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F27"
Cells(e, 25) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F28"
Cells(e, 26) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F29"
Cells(e, 27) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F30"
Cells(e, 28) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F31"
Cells(e, 29) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G25"
Cells(e, 30) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G26"
Cells(e, 31) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G27"
Cells(e, 32) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G28"
Cells(e, 33) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G29"
Cells(e, 34) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G30"
Cells(e, 35) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G31"
Cells(e, 36) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P25"
Cells(e, 37) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P26"
Cells(e, 38) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P27"
Cells(e, 39) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P28"
Cells(e, 40) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P29"
Cells(e, 41) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P30"
Cells(e, 42) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P31"
Cells(e, 43) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M25"
Cells(e, 44) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M26"
Cells(e, 45) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M27"
Cells(e, 46) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M28"
Cells(e, 47) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M29"
Cells(e, 48) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M30"
Cells(e, 49) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M31"
Cells(e, 50) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O25"
Cells(e, 51) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O26"
Cells(e, 52) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O27"
Cells(e, 53) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O28"
Cells(e, 54) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O29"
Cells(e, 55) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O30"
Cells(e, 56) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O31"
Cells(e, 57) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I25"
Cells(e, 58) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I26"
Cells(e, 59) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I27"
Cells(e, 60) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I28"
Cells(e, 61) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I29"
Cells(e, 62) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I30"
Cells(e, 63) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I31"
Cells(e, 64) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J25"
Cells(e, 65) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J26"
Cells(e, 66) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J27"
Cells(e, 67) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J28"
Cells(e, 68) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J29"
Cells(e, 69) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J30"
Cells(e, 70) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J31"
Cells(e, 71) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K25"
Cells(e, 72) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K26"
Cells(e, 73) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K27"
Cells(e, 74) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K28"
Cells(e, 75) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K29"
Cells(e, 76) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K30"
Cells(e, 77) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K31"
Cells(e, 78) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L25"
Cells(e, 79) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L26"
Cells(e, 80) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L27"
Cells(e, 81) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L28"
Cells(e, 82) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L29"
Cells(e, 83) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L30"
Cells(e, 84) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L31"
Cells(e, 85) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q25"
Cells(e, 86) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q26"
Cells(e, 87) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q27"
Cells(e, 88) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q28"
Cells(e, 89) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q29"
Cells(e, 90) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q30"
Cells(e, 91) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q31"
Cells(e, 92) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R25"
Cells(e, 93) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R26"
Cells(e, 94) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R27"
Cells(e, 95) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R28"
Cells(e, 96) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R29"
Cells(e, 97) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R30"
Cells(e, 98) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R31"
Cells(e, 99) = Cells(1, 98)
End If
Next e
MsgBox "Compilation is complete."
End Sub
Is this as good as it can get? Suggestions for speeding it up? i tried to deactivate the screen refreshing, but I always got an error or it would freeze while running the code.
A bit of background on what the code does just in case: Each Client of my company has its own spreadsheet where cases (like a law firm) are entered to keep track of cases opened, closed, duration, etc. Each column tracks a different stat and there are 7 rows (2531) for 7 years over which I am tracking data. The Master spreadsheet has this macro attached to it so that it can pull all the stats from all of our clients to combine them all together. Currently we have about 100 clients.
Sub PullStats()
Dim z As Long, e As Long
Dim f As String
d = 2
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)1)"
On Error Resume Next
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) ActiveWorkbook.Name Then
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C25"
Cells(e, 2) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C26"
Cells(e, 3) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C27"
Cells(e, 4) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C28"
Cells(e, 5) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C29"
Cells(e, 6) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C30"
Cells(e, 7) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!C31"
Cells(e, 8) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D25"
Cells(e, 9) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D26"
Cells(e, 10) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D27"
Cells(e, 11) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D28"
Cells(e, 12) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D29"
Cells(e, 13) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D30"
Cells(e, 14) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!D31"
Cells(e, 15) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N25"
Cells(e, 16) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N26"
Cells(e, 17) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N27"
Cells(e, 18) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N28"
Cells(e, 19) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N29"
Cells(e, 20) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N30"
Cells(e, 21) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!N31"
Cells(e, 22) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F25"
Cells(e, 23) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F26"
Cells(e, 24) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F27"
Cells(e, 25) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F28"
Cells(e, 26) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F29"
Cells(e, 27) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F30"
Cells(e, 28) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!F31"
Cells(e, 29) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G25"
Cells(e, 30) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G26"
Cells(e, 31) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G27"
Cells(e, 32) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G28"
Cells(e, 33) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G29"
Cells(e, 34) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G30"
Cells(e, 35) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!G31"
Cells(e, 36) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P25"
Cells(e, 37) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P26"
Cells(e, 38) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P27"
Cells(e, 39) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P28"
Cells(e, 40) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P29"
Cells(e, 41) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P30"
Cells(e, 42) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!P31"
Cells(e, 43) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M25"
Cells(e, 44) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M26"
Cells(e, 45) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M27"
Cells(e, 46) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M28"
Cells(e, 47) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M29"
Cells(e, 48) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M30"
Cells(e, 49) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!M31"
Cells(e, 50) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O25"
Cells(e, 51) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O26"
Cells(e, 52) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O27"
Cells(e, 53) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O28"
Cells(e, 54) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O29"
Cells(e, 55) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O30"
Cells(e, 56) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!O31"
Cells(e, 57) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I25"
Cells(e, 58) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I26"
Cells(e, 59) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I27"
Cells(e, 60) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I28"
Cells(e, 61) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I29"
Cells(e, 62) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I30"
Cells(e, 63) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!I31"
Cells(e, 64) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J25"
Cells(e, 65) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J26"
Cells(e, 66) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J27"
Cells(e, 67) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J28"
Cells(e, 68) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J29"
Cells(e, 69) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J30"
Cells(e, 70) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!J31"
Cells(e, 71) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K25"
Cells(e, 72) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K26"
Cells(e, 73) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K27"
Cells(e, 74) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K28"
Cells(e, 75) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K29"
Cells(e, 76) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K30"
Cells(e, 77) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!K31"
Cells(e, 78) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L25"
Cells(e, 79) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L26"
Cells(e, 80) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L27"
Cells(e, 81) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L28"
Cells(e, 82) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L29"
Cells(e, 83) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L30"
Cells(e, 84) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!L31"
Cells(e, 85) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q25"
Cells(e, 86) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q26"
Cells(e, 87) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q27"
Cells(e, 88) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q28"
Cells(e, 89) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q29"
Cells(e, 90) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q30"
Cells(e, 91) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!Q31"
Cells(e, 92) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R25"
Cells(e, 93) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R26"
Cells(e, 94) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R27"
Cells(e, 95) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R28"
Cells(e, 96) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R29"
Cells(e, 97) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R30"
Cells(e, 98) = Cells(1, 98)
Cells(1, 98) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Location Stats'!R31"
Cells(e, 99) = Cells(1, 98)
End If
Next e
MsgBox "Compilation is complete."
End Sub
im trying to do baseball stats in excel :
1B 2B 3B HR BB K
Players Name 1B 1B K PO GO
Players Name K PO K PO GO
Players Name PO K K PO GO
Players Name GO 1B K PO GO
Players Name PO GO K PO GO
Players Name K PO K PO GO
Players Name K 1B K PO GO
Players Name PO 1B K PO GO
Players Name GO 1B K PO GO
what im trying to do, is to have the stats compute for me for each player so for the 1st person it would show me he had 2 (1B) , 1 (K) , 1 (PO) , and 1(GO) then for the next player , it would show 2 (K) , 2 (PO) , and 1 (GO) , and so forth, and when i changed the stats around it would change with in the boxscore as well
1B 2B 3B HR BB K
Players Name 1B 1B K PO GO
Players Name K PO K PO GO
Players Name PO K K PO GO
Players Name GO 1B K PO GO
Players Name PO GO K PO GO
Players Name K PO K PO GO
Players Name K 1B K PO GO
Players Name PO 1B K PO GO
Players Name GO 1B K PO GO
what im trying to do, is to have the stats compute for me for each player so for the 1st person it would show me he had 2 (1B) , 1 (K) , 1 (PO) , and 1(GO) then for the next player , it would show 2 (K) , 2 (PO) , and 1 (GO) , and so forth, and when i changed the stats around it would change with in the boxscore as well
Hey guys I am new here, but I need some help with VBA.
I'm not very good at VBA, but I'm trying to learn it on the go.
I've created an excel spreadsheet with players projections. I took MLB players stats from the past 2 years and ive created a formula to come up with the projections. (I'm a baseball geek, and I play fantasy baseball, so im trying to create this for fun.)
Now, I've created a button to begin, then the user follows a couple message prompts. Then Once the user selects a player, the players projected stats should appear.
This is the problem, I cannot get this to work at all. I don't know how to get VBA to to search a sheet for a player that someone selected, and output its stats on a message box.
Can anyone help me?? I'd greatly appreciated it. Thanks everyone in advance.
I'm not very good at VBA, but I'm trying to learn it on the go.
I've created an excel spreadsheet with players projections. I took MLB players stats from the past 2 years and ive created a formula to come up with the projections. (I'm a baseball geek, and I play fantasy baseball, so im trying to create this for fun.)
Now, I've created a button to begin, then the user follows a couple message prompts. Then Once the user selects a player, the players projected stats should appear.
This is the problem, I cannot get this to work at all. I don't know how to get VBA to to search a sheet for a player that someone selected, and output its stats on a message box.
Can anyone help me?? I'd greatly appreciated it. Thanks everyone in advance.
I am looking to link updateable statistics from a baseball website onto my excel spreadsheet. It's very important that when the data is updated on the site that it reflects in my spreadsheet automatically as well.
Here is the url for the page I am referencing:
http://www.baseballreference.com/pl...=1&submitter=1
You will see that there is a table with a red header entitled : "Stats Summary for matching Plate Appearances"
I am looking to have the associated numbers in the table imported and linked to my spreadsheet. I tried using the "Get External Data" tool in excel but the table does not seem to be compatible, so maybe I have to import the html somehow? I am not that familiar with importing data from sites but I believe it is possible.
Please, any help would be appreciated.
Anyone? Just want to know if it is possible to import the data. Just a beginner when it comes to using Excel in this way.
OK, I'm trying to reference text from another cell and it's not working. I assume it's either the parentheses or brackets that throwing it of. I am making a number of reports that have 10 formulas in them and would like to use excel to change the formulas when I change the group name. I have a formulas cell and a group 1 and group 2 cell.
Here is my formula...
(Sum ({Stats.H}, {Stats.Opponent})+Sum ({Stats.BB}, {Stats.Opponent}))/(Sum ({Stats.AB}, {Stats.Opponent})+Sum ({Stats.BB}, {Stats.Opponent})+Sum ({Stats.SAC}, {Stats.Opponent}))
In my group2 field I have {Stats.Opponent}
What I would like to do is have my formula like this
(Sum ({Stats.H}, d2)+Sum ({Stats.BB}, d2))/(Sum ({Stats.AB}, d2)+Sum ({Stats.BB}, d2)+Sum ({Stats.SAC}, d2))
Then whenever I change the report to use another group, I only have to change the group2 field and excel would generate the new formula and I'd just copy and paste that into the new report fields.
Thanks for any ideas...
BTW, I tried using search and kept coming up with and error about exceeding the maximum execution time or a blank page...
Here is my formula...
(Sum ({Stats.H}, {Stats.Opponent})+Sum ({Stats.BB}, {Stats.Opponent}))/(Sum ({Stats.AB}, {Stats.Opponent})+Sum ({Stats.BB}, {Stats.Opponent})+Sum ({Stats.SAC}, {Stats.Opponent}))
In my group2 field I have {Stats.Opponent}
What I would like to do is have my formula like this
(Sum ({Stats.H}, d2)+Sum ({Stats.BB}, d2))/(Sum ({Stats.AB}, d2)+Sum ({Stats.BB}, d2)+Sum ({Stats.SAC}, d2))
Then whenever I change the report to use another group, I only have to change the group2 field and excel would generate the new formula and I'd just copy and paste that into the new report fields.
Thanks for any ideas...
BTW, I tried using search and kept coming up with and error about exceeding the maximum execution time or a blank page...
I have a worksheet that pulls stats from baseball reference, and on one .iqy file, it takes tables 1,2 and 12 from the players 2010 splits. However, that page doesn't have fielding, so I need to create another .iqy that takes stats from the fielding page. How do I open up the second .iqy file and add it to the other I already have?
I'm keeping track of stats for my local baseball team. I use this formula to search the input tab for cumulative stats.
{=IF($Y$3="Both",SUMIF('Game Data'!$B$3:$B$193,$B5,'Game Data'!D$3:D$193),SUM(IF('Game Data'!$B$3:$B$193=$B5,IF('Game Data'!$X$3:$X$193=$Y$3,'Game Data'!D$3:D$193,0),0)))}
Y3 is a combo box that tallys between Both, Regular Season, and Playoffs
Game Data B represents the player name
B5 is the player name on the stats tab
Game Data D is the particular stat
Game Data X designates if the entered stats are during the regular season or playoffs
QUESTION 1:Can i change the search range (currently 3:193) to include the entire column? The below formula does not work when I change Y3 to playoffs or regular season
={IF($Y$3="Both",SUMIF('Game Data'!B:B,'Season Stats'!$B4,'Game Data'!D:D),SUM(IF('Game Data'!B:B='Season Stats'!$B4,IF('Game Data'!X:X='Season Stats'!$Y$3,'Game Data'!D:D,0),0)))}
QUESTION 2: Is there a way I could add additional Combo Boxes to search by Playoffs/Regular/Both AND Year AND Season.
Oh, just building an Access database isn't an available option.
THANKS!
{=IF($Y$3="Both",SUMIF('Game Data'!$B$3:$B$193,$B5,'Game Data'!D$3:D$193),SUM(IF('Game Data'!$B$3:$B$193=$B5,IF('Game Data'!$X$3:$X$193=$Y$3,'Game Data'!D$3:D$193,0),0)))}
Y3 is a combo box that tallys between Both, Regular Season, and Playoffs
Game Data B represents the player name
B5 is the player name on the stats tab
Game Data D is the particular stat
Game Data X designates if the entered stats are during the regular season or playoffs
QUESTION 1:Can i change the search range (currently 3:193) to include the entire column? The below formula does not work when I change Y3 to playoffs or regular season
={IF($Y$3="Both",SUMIF('Game Data'!B:B,'Season Stats'!$B4,'Game Data'!D:D),SUM(IF('Game Data'!B:B='Season Stats'!$B4,IF('Game Data'!X:X='Season Stats'!$Y$3,'Game Data'!D:D,0),0)))}
QUESTION 2: Is there a way I could add additional Combo Boxes to search by Playoffs/Regular/Both AND Year AND Season.
Oh, just building an Access database isn't an available option.
THANKS!
Hello Helpful People,
I want to combine several years worth of baseball stat sheets for analyis. Stat sheets are done by year, so I can get an Excel sheet with 2009 stats for every player, another with the 2008 stats, and so on. I want this all in one sheet with all the data kept separate, and it would look something like this: Column A is the player's name. Column B is the player's position. Columns C through H would be 2009 stats in various categories. Columns I through N would be the same categories but for 2008, and so on.
The problem for me is that each year there are different players playing baseball. There's a lot of overlap, but it isn't anywhere near perfect. I'd want blanks to appear in cells that correspond to stats from years a player did not play.
Also, I've found a site that assigns unique codes to each player, which avoids the problem of having multiple players with the same name.
Is there any way to do what I'm trying to do? I'm fairly handy with a lot of Excel functions, but this one has me stumped.
Thanks for any help you provide.
I want to combine several years worth of baseball stat sheets for analyis. Stat sheets are done by year, so I can get an Excel sheet with 2009 stats for every player, another with the 2008 stats, and so on. I want this all in one sheet with all the data kept separate, and it would look something like this: Column A is the player's name. Column B is the player's position. Columns C through H would be 2009 stats in various categories. Columns I through N would be the same categories but for 2008, and so on.
The problem for me is that each year there are different players playing baseball. There's a lot of overlap, but it isn't anywhere near perfect. I'd want blanks to appear in cells that correspond to stats from years a player did not play.
Also, I've found a site that assigns unique codes to each player, which avoids the problem of having multiple players with the same name.
Is there any way to do what I'm trying to do? I'm fairly handy with a lot of Excel functions, but this one has me stumped.
Thanks for any help you provide.
I have an excel spreadsheet with with 17 numeric columns that represent baseball stats. I would like to multiple all of the data in each of those columns and rows (20) by 1.434. Rather than go in and take each individual # in each column and manually key in =#*1.434 I would like to know how to globally apply that formula across the spread sheet. Manually keying would take me 4560 minutes per sheet.
PURPOSE OF FORMULA: I am attempting to project baseball stats from the 1994 strike season for each team and each player over the 162 games that should have been played. Since each team played a uneven amount of games I am applying different amounts in the formula to bring them close to 162. I can send the spreadsheet to anyone who is interested if they'd like to tinker with it. I think I uploaded it here as well94Chisox.xls
PURPOSE OF FORMULA: I am attempting to project baseball stats from the 1994 strike season for each team and each player over the 162 games that should have been played. Since each team played a uneven amount of games I am applying different amounts in the formula to bring them close to 162. I can send the spreadsheet to anyone who is interested if they'd like to tinker with it. I think I uploaded it here as well94Chisox.xls
I've searched here (and elsewhere) for something that will take baseball stat inputs and return an ordered list based on ordinal criteria (i.e. return max OBP unless also max OPS, then return 2nd best OBP). I found a website that will make a lineup, but does not have adjustable criteria and their criteria don't match mine. I did find one spreadsheet here, but I couldn't modify it (don't know enough Excel to do so), and it is not scalable.
So, I am looking for a way to generate a lineup based on these stats: On Base % (OBP), Slugging (SLG), and OPS (OBP+SLG) and Batting Avg (BA)
The rules I need to follow:
#1 = best OBP unless is also best OPS or best SLG, then 2nd best OBP
#2 = best OPS
#3 = 2nd best OBP unless used for #1, then 3rd best
#4 = best SLG unless same as #2, then 2nd best SLG
Obviously, none can repeat, but would need to repeat the ordering for 20>n>7 players. Also, there will likely be duplicate numbers for each stat, so BA becomes the tiebreaker.
I'd attach what I have, but I have nothing at this point, sorry. Any help would be appreciated! I hope I've been specific enough and I put this in the right place?
Ray
Hello and thanks for taking the time to read this request for help. I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.
Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx
I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.
How can I collect the data from these other pages? Is there more information needed to assist me? Thanks in advance
Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx
I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.
How can I collect the data from these other pages? Is there more information needed to assist me? Thanks in advance
... to find the error in this formula
=IF(LEFT(K9,1)="D",IF(F19="NR","NR",IF(F19="Reline","NRReline",IF(F19="Changeover","NAChange",IF(T24="HP4",Stats!J1,IF(T24="HP2",Stats!J1,IF(T24="RP3",Stats!J1,IF(T24="RP5",Stats!J1,IF(AI12="HP",Stats!J7,IF(AI12="RP",Stats!J10,Stats!J1))))))))),"NA  Field")
Jenn
=IF(LEFT(K9,1)="D",IF(F19="NR","NR",IF(F19="Reline","NRReline",IF(F19="Changeover","NAChange",IF(T24="HP4",Stats!J1,IF(T24="HP2",Stats!J1,IF(T24="RP3",Stats!J1,IF(T24="RP5",Stats!J1,IF(AI12="HP",Stats!J7,IF(AI12="RP",Stats!J10,Stats!J1))))))))),"NA  Field")
Jenn
I am using Excel to analyze several columns of baseball stats. I want to be able to sort through the list and find out how many particluar players from a particular team achieved something in the same year.
My data is in columns such as player, team, year, HR, Hits, RBI, etc......
For example, I want to sort through the data and count the number of Cub players who hit 20 homers in the same season. How do I get the COUNTIF function to do this? I've tried using COUNTIF in formulas with IF and AND statements but get incorrect results.
Thanks,
madmanmac
My data is in columns such as player, team, year, HR, Hits, RBI, etc......
For example, I want to sort through the data and count the number of Cub players who hit 20 homers in the same season. How do I get the COUNTIF function to do this? I've tried using COUNTIF in formulas with IF and AND statements but get incorrect results.
Thanks,
madmanmac
Hello and thanks for taking the time to read this request for help. I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.
Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx
I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.
How can I collect the data from these other pages? Is there more information needed to assist me? Thanks in advance
I've cross posted this same question on another board. http://www.mrexcel.com/forum/showthr...46#post2164946, there has been no answer yet but I will update if there is.
Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx
I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.
How can I collect the data from these other pages? Is there more information needed to assist me? Thanks in advance
I've cross posted this same question on another board. http://www.mrexcel.com/forum/showthr...46#post2164946, there has been no answer yet but I will update if there is.
I went through the topics, and nobody has asked my exact question, so I gotta post a new thread.
I am my team's statistician, and as such, I have people asking for the goofball stuff OBP, SLG, and OPS.
I'm using =IF's to cover the #DIV/0 error, and have the cells formatted as #.000 to cover the way those stats are shown. However, I'm noticing numbers errors due to rounding up or down.
ie: Guy has a .714 OPB, and a .714 SLG making his OPS 1.428 however, 10/14 = .71428 so .71428+.71428 = 1.42852 thus 1.429 is showing up in the cell.
What sort of formula should I use to make my OPS add ONLY the first three digits of the previous two stats. I want excel to add .714 + .714 and that's it.
I am my team's statistician, and as such, I have people asking for the goofball stuff OBP, SLG, and OPS.
I'm using =IF's to cover the #DIV/0 error, and have the cells formatted as #.000 to cover the way those stats are shown. However, I'm noticing numbers errors due to rounding up or down.
ie: Guy has a .714 OPB, and a .714 SLG making his OPS 1.428 however, 10/14 = .71428 so .71428+.71428 = 1.42852 thus 1.429 is showing up in the cell.
What sort of formula should I use to make my OPS add ONLY the first three digits of the previous two stats. I want excel to add .714 + .714 and that's it.
I have many spreadsheets with sports stats. I pulled the information from the web. So on one page I have the stats. The next page I want to be able to type the teams name and have excel pull out all the stats I want. Makes it easy to compare teams stats that are playing each other. Is this possible I tried to use most of the lookup functions but I just cant figure this out.
I will attach the sheets.
I will attach the sheets.
Sorry for the bad thread title but I'm not even sure what formula I would use to do this. I'm working with some baseball stats and I have a spreadsheet with all the players, what team they play for, and their various stats.
I would like to have a formula that allows me to input a team, and return all the players who play for that team along with their stats. Is this easy?
I would like to have a formula that allows me to input a team, and return all the players who play for that team along with their stats. Is this easy?
I have spent much of the day learning the multiple ways of importing tables from webpages to Excel, but I am having trouble with the following task:
I want to import the lineups for every game played by every MLB team from 19902013. The best source of this data seems to be baseballreference.com. Here it the format for the URL for the Yankees in 2012:
http://www.baseballreference.com/te...lineups.shtml
It seems to import pretty well manually, and I usually delete everything above Game 1 and after Game 162. I suppose I would need a script that runs 2 loops, one for the 30 teams and one for the years, with each team being a separate sheet. Can someone help me with this? Thank you.
I'm in a baseball league that replays games and we keep stats. What I'm trying to do is to have a list of my players with their lifetime stats on one page. I will be adding to this on a yearly basis. I've already used countif to only list the players once on one sheet, but having trouble trying to add their stats for each year. I just need something to lookup, each year they played and then add up all their games they played. I can do the rest once I have one of the stats.
Here is an example starting with column A as the year and moving across:
YEAR NAME GAMES
2005 Alfonzo 120
2006 Alfonzo 45
What I need is something that will add up 120 and 45 onto another sheet, but also let me add 2007 and so on and will contiune to add them up.
Thanks,
Quicksand
Here is an example starting with column A as the year and moving across:
YEAR NAME GAMES
2005 Alfonzo 120
2006 Alfonzo 45
What I need is something that will add up 120 and 45 onto another sheet, but also let me add 2007 and so on and will contiune to add them up.
Thanks,
Quicksand
Hey all.
First time poster and very much a newbee at excel.........I'll add a few pitchers of current sheet below..
So first of all i'm creating an uptodate baseball spreadsheet, that gives pitcher + team mathups and reference to stats such as road/home, day/night, Vs Right/Left handed Pitcher etc.....
so far i have all the stats in worksheets and have created a couple of drop downlists through data validation of all pitchers available and all teams available, these drop down lists are matched to the stats by way of
e.g In C4 there is drop down list of teams i can choose (Seattle in example)
in D5 to give me wins for Seattle
=VLOOKUP($C$4,Standings,MATCH(Wins,Standings_cols,0),0)
Then in B17 drop down list of all possible pitchers (A COOK in example)
In B18 to give me # games that A COOK has pitched
=VLOOKUP($B$17,Pitchers_All_All,MATCH(Games,Pitchers_All_All_cols,0),0)
This works fine but i have to go through the trouble of manually selecting which team is playing and which pitcher is pitching.
Ideally i would like to have A main drop downlist from web querry data from
http://www.statfox.com/mlb/gamematchup.asp
at the top of the spreadsheet that has the current teams playing for the current day and ultimately changes the names in C4,C5 and B17,B18.
i.e
DETROIT at ARIZONA
LA DODGERS at LA ANGELS
Then for this drop down list to connect with C4 and choose the particular team that is selected i.e choose Detroit, with c5 being chosen as ARIZONA
Also i want the Pitchers to be taken from the web query as i change the Teams PLaying. So if i select "DETROIT at ARIZONA" from list then in the Pitchers box in B17 it would automatically choose J BONDERMAN likewise choosse D HAREN in B18.
Is this at all possible?
Any help would be greatly appreciated. I would also like to add the day/ night variable from a drop down list that will change the stats shown, but baby steps for now.
Excel 07, Vista home
Thanks for taking the time
First time poster and very much a newbee at excel.........I'll add a few pitchers of current sheet below..
So first of all i'm creating an uptodate baseball spreadsheet, that gives pitcher + team mathups and reference to stats such as road/home, day/night, Vs Right/Left handed Pitcher etc.....
so far i have all the stats in worksheets and have created a couple of drop downlists through data validation of all pitchers available and all teams available, these drop down lists are matched to the stats by way of
e.g In C4 there is drop down list of teams i can choose (Seattle in example)
in D5 to give me wins for Seattle
=VLOOKUP($C$4,Standings,MATCH(Wins,Standings_cols,0),0)
Then in B17 drop down list of all possible pitchers (A COOK in example)
In B18 to give me # games that A COOK has pitched
=VLOOKUP($B$17,Pitchers_All_All,MATCH(Games,Pitchers_All_All_cols,0),0)
This works fine but i have to go through the trouble of manually selecting which team is playing and which pitcher is pitching.
Ideally i would like to have A main drop downlist from web querry data from
http://www.statfox.com/mlb/gamematchup.asp
at the top of the spreadsheet that has the current teams playing for the current day and ultimately changes the names in C4,C5 and B17,B18.
i.e
DETROIT at ARIZONA
LA DODGERS at LA ANGELS
Then for this drop down list to connect with C4 and choose the particular team that is selected i.e choose Detroit, with c5 being chosen as ARIZONA
Also i want the Pitchers to be taken from the web query as i change the Teams PLaying. So if i select "DETROIT at ARIZONA" from list then in the Pitchers box in B17 it would automatically choose J BONDERMAN likewise choosse D HAREN in B18.
Is this at all possible?
Any help would be greatly appreciated. I would also like to add the day/ night variable from a drop down list that will change the stats shown, but baby steps for now.
Excel 07, Vista home
Thanks for taking the time
I posted yesterday and people here were really helpful, so I will try again!
I am working on a backend database for a TV graphics package for live baseball coverage. My current sticking point is getting the players to match up with their stats. Before each game, I go online to the team's websites and copy/paste their statistics and personal info into my database. The problem is, they store the personal info (height/weight, etc) in a different table from their batting stats and there's a third for pitching stats.
I need to get a single table with all the stats for a single player on one line (this seems reasonable, right?). But for some reason, my vlookup's either give me #N/A for the players it can't find, or all 0's when I try to add in a isna() into the mix. I've tried a bunch of different ways, and I use a LOT of vlookup's in this workbook, so I (thought) I understand how to do it!
I think part of my problem might be that I'm doing a vlookup on the player NAME as the lookup value (so a text string instead of a number), but I'm not sure. I have double checked that the names do match.
I have attached the two relevant worksheet's from my workbook. Thanks for looking!
TeamConcatenate.xlsx
Hi!
I'm part of a Simulation Football League and we are using Excel to record our stats.
I created the basic workbook, but I still need some help.
I have attached the file to this post.
My problem
You will see that in the column "Player" Where it list the "Player Names" it will say "Tom Brady" in Week 1.
How do I fix the workbook so that no matter what name I put the "Player" Column of Week 1,2, 3, 4,...etc. he will automatically show up in the Season Stats worksheet? Which is also attached. Same goes for the "Position" How do I do that?
Also, I want the Season Stats worksheet to get all accumulated Stats from Week 1, 2, 3, 4...etc. How can that be done? But each week will be separate week for the stats of that week. But Season Stats will combine all of them with the averages.
I don't know how to do it. So I attached the workbook and hopefully someone who is really skilled with Excel can do it.
The Season Stats worksheet is at the end.
Thanks in advance!
I'm part of a Simulation Football League and we are using Excel to record our stats.
I created the basic workbook, but I still need some help.
I have attached the file to this post.
My problem
You will see that in the column "Player" Where it list the "Player Names" it will say "Tom Brady" in Week 1.
How do I fix the workbook so that no matter what name I put the "Player" Column of Week 1,2, 3, 4,...etc. he will automatically show up in the Season Stats worksheet? Which is also attached. Same goes for the "Position" How do I do that?
Also, I want the Season Stats worksheet to get all accumulated Stats from Week 1, 2, 3, 4...etc. How can that be done? But each week will be separate week for the stats of that week. But Season Stats will combine all of them with the averages.
I don't know how to do it. So I attached the workbook and hopefully someone who is really skilled with Excel can do it.
The Season Stats worksheet is at the end.
Thanks in advance!
What I'm trying to do is create a stats spreadsheet for my Beersbee league. I made the "single game" stats table rather easily but I'm having trouble with sending the data from a the single game to another table that will be called "season stats".
I want to be able to add all of the numerical stats (poles, bottles, catches, etc.) to the to the season stats table. But also there are some percentage stats (Hit%, Catch%, etc.) that need to be averaged with the season percentages at the conclusion of each game.
I have a button called "new game" that is linked to a macro that deletes all of the data from the previous game and it seems like everytime I click this it will delete all the data from season stats!
Please Help! This would be awesome to have this going for the summer!
Thanks
I want to be able to add all of the numerical stats (poles, bottles, catches, etc.) to the to the season stats table. But also there are some percentage stats (Hit%, Catch%, etc.) that need to be averaged with the season percentages at the conclusion of each game.
I have a button called "new game" that is linked to a macro that deletes all of the data from the previous game and it seems like everytime I click this it will delete all the data from season stats!
Please Help! This would be awesome to have this going for the summer!
Thanks