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 Video Tutorials
Best Value Per 1 Unit Of Stats Value
 Excel Magic Trick #178: Best Value Per 1 Unit Of Stats Value.
See how to calculate the best value from two categories of values. Find the Best Do ...
See how to calculate the best value from two categories of values. Find the Best Do ...
Access One To Many Relationship Between Tables
 Download files at: https://people.highline.edu/mgirvin/AllClasses/216_2003/216/busn216.htm
This video goes with the Highline Community College Bu ...
This video goes with the Highline Community College Bu ...
Creating Queries In Access (16 Examples)
 Download files at: https://people.highline.edu/mgirvin/AllClasses/216_2003/216/busn216.htm
This video goes with the Highline Community College Bu ...
This video goes with the Highline Community College Bu ...
Consolidation
 See how to use the Consolidating feature in Excel. Summarize data from a number of different tables quickly using the Consolidation Feature.
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
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
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
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
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.
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 have a line of baseball stats, and I want to have the ability to copy those stats to another page. However, there are formulas for those stats, and I only want the text not the formulas. Is there a way to do that with a macro?
Also, after copying one row of stats, if I refresh the data for another player, how do I copy the new player's stats on the next line of the other other worksheet?
Also, after copying one row of stats, if I refresh the data for another player, how do I copy the new player's stats on the next line of the other other worksheet?
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
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.
Hi. The title might not be clear, but I wasn't quite sure how to describe what I'm trying to accomplish; if it's even possible.
I participate in a fantasy baseball league and we use Excel 2007 to keep track of the statistics. Basically, we select players in 8 statistical categories. The rosters we draft are stored in a sheet named "Rosters" and the official player stats are kept on a separate sheet named "Stats" (ie. BA is Batting Average, HR is Home Runs, etc.).
I've attached a sample XLSX file that shows our current setup. We update the statistics monthly by copying and pasting the actual MLB stats from the CBS Sportsline website into the "Stats" sheet. Then, we'll manually find the BA for Joe Mauer, for example, then transfer that number to his spot on the Rosters sheet. So, in the test.xlsx file, it would require copying cell C2 from the "Stats" and pasting it into the E2 cell in "Rosters." We do that manually which takes about 34 hours.
I'm hoping there's a way to make this more of an automatic process. If anyone has any ideas or suggestions, I would greatly appreciate it. Thank you!
test.xlsx
Ted
I participate in a fantasy baseball league and we use Excel 2007 to keep track of the statistics. Basically, we select players in 8 statistical categories. The rosters we draft are stored in a sheet named "Rosters" and the official player stats are kept on a separate sheet named "Stats" (ie. BA is Batting Average, HR is Home Runs, etc.).
I've attached a sample XLSX file that shows our current setup. We update the statistics monthly by copying and pasting the actual MLB stats from the CBS Sportsline website into the "Stats" sheet. Then, we'll manually find the BA for Joe Mauer, for example, then transfer that number to his spot on the Rosters sheet. So, in the test.xlsx file, it would require copying cell C2 from the "Stats" and pasting it into the E2 cell in "Rosters." We do that manually which takes about 34 hours.
I'm hoping there's a way to make this more of an automatic process. If anyone has any ideas or suggestions, I would greatly appreciate it. Thank you!
test.xlsx
Ted
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
... 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 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.
Hi All
I am putting together a spreadsheet to track stats in our baseball league. I have each players stats on sheet 1. I want to rank the players based on their batting average which is in row I. I would like this ranking to show up on a separate worksheet, sheet 2, ranked in descending order. Pretty simple, but I would like it to rerank every time I change a players stats on Sheet 1. Basically, I want sheet 2 to update and rerank every time I make a change on sheet 1.
Seems pretty simple, and there are probably a number of ways to do it, would a macro work?
I am putting together a spreadsheet to track stats in our baseball league. I have each players stats on sheet 1. I want to rank the players based on their batting average which is in row I. I would like this ranking to show up on a separate worksheet, sheet 2, ranked in descending order. Pretty simple, but I would like it to rerank every time I change a players stats on Sheet 1. Basically, I want sheet 2 to update and rerank every time I make a change on sheet 1.
Seems pretty simple, and there are probably a number of ways to do it, would a macro work?
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 two Excel files. On one, there is a long list of baseball player names. On the other, there are also baseball player names, but there are stats next to the respective names. The goal is to take the information from the second file, and put it next to the names in the first file. So far, I have been searching the name on the second file, and copying the stats to the other file next to the respective name.
How can I automate this tedious and long process?
I have a background in programming with C++, C#, Java, etc. I could probably write this in C# if I really tried, but I have a feeling that's not the best approach, or is it? (I'd have to look up on how to handle Excel files)
How can I automate this tedious and long process?
I have a background in programming with C++, C#, Java, etc. I could probably write this in C# if I really tried, but I have a feeling that's not the best approach, or is it? (I'd have to look up on how to handle Excel files)
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
Hi
I had previously received some great advice about how to properly parse my data to get it from the interwebs into the excel.
I now need help with my sorting of the data. The story is this:
I am in a fantasy baseball league where every week, we accumulate our total stats and go to head to head with another team. At the end of the week, in each matchup, whichever team has the higher total gets a point. There are 12 categories, so you could win a weekly matchup 1200 (wins, losses, ties) if you do really well.
The problem that I have with this method of scoring is that you only go up against one opponent, not the entire league each week. So I am trying to get the raw stats for each team for each week and say where they finish in each category (110).
I have so far accomplished getting the stats from the internet to the excel, getting each team's weekly totals all in a nice readable order, getting all the stats for week 1 into a nice little box (the blue box on the analysis tab), and I am starting to get the stats sorted to the point that for each category, it spits out the team with the highest value and next highest and all that (the pink box).
The problem that I am having is that I sometimes get duplicate values: in week 1, both the rockford peachz and the vaya con bernabe teams hit 12 homers, so when I do a vlookup on the "large" function, it gives the same team for the second highest homers. Is there a way to get around this? Do I need some insane complicated if function?
I have attached the spreadsheet, all of the real action is in the analysis tab. Also, FYI, we are currently in week 8, so the only stats that I have are before that.
thanks!
I had previously received some great advice about how to properly parse my data to get it from the interwebs into the excel.
I now need help with my sorting of the data. The story is this:
I am in a fantasy baseball league where every week, we accumulate our total stats and go to head to head with another team. At the end of the week, in each matchup, whichever team has the higher total gets a point. There are 12 categories, so you could win a weekly matchup 1200 (wins, losses, ties) if you do really well.
The problem that I have with this method of scoring is that you only go up against one opponent, not the entire league each week. So I am trying to get the raw stats for each team for each week and say where they finish in each category (110).
I have so far accomplished getting the stats from the internet to the excel, getting each team's weekly totals all in a nice readable order, getting all the stats for week 1 into a nice little box (the blue box on the analysis tab), and I am starting to get the stats sorted to the point that for each category, it spits out the team with the highest value and next highest and all that (the pink box).
The problem that I am having is that I sometimes get duplicate values: in week 1, both the rockford peachz and the vaya con bernabe teams hit 12 homers, so when I do a vlookup on the "large" function, it gives the same team for the second highest homers. Is there a way to get around this? Do I need some insane complicated if function?
I have attached the spreadsheet, all of the real action is in the analysis tab. Also, FYI, we are currently in week 8, so the only stats that I have are before that.
thanks!
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