Baseball.....Connecting multiple drop down lists and appropriate stats from web queries

M

morbe290

New Member
Joined
May 20, 2008
Messages
7
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 up-to-date 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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
<table style="border-collapse: collapse; width: 495pt;" border="0" cellpadding="0" cellspacing="0" width="660"><col style="width: 20pt;" width="26"> <col style="width: 108pt;" width="144"> <col style="width: 94pt;" width="125"> <col style="width: 48pt;" width="64"> <col style="width: 59pt;" width="79"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" width="64"> <col style="width: 62pt;" width="83"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 20pt;" height="20" width="26">
</td> <td style="width: 108pt;" width="144">MATCHUP</td> <td style="width: 94pt;" width="125">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 59pt;" width="79">
</td> <td style="width: 56pt;" width="75">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 62pt;" width="83">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl70"> </td> <td class="xl70"> </td> <td class="xl70"> </td> <td class="xl70"> </td> <td class="xl70"> </td> <td class="xl70"> </td> <td class="xl70"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl75">Standings</td> <td class="xl74">Team</td> <td class="xl74">Wins</td> <td class="xl74">Loss</td> <td class="xl74">Pct.</td> <td class="xl77">Units</td> <td class="xl78">Ov.Un.P</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl66">Away</td> <td class="xl73">SEATTLE</td> <td class="xl79">16</td> <td class="xl73">26</td> <td class="xl79">0.381</td> <td class="xl73">-13.65</td> <td class="xl79">18-22</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl67">Home</td> <td class="xl73">COLORADO</td> <td class="xl79">15</td> <td class="xl73">26</td> <td class="xl79">0.366</td> <td class="xl73">-11.25</td> <td class="xl79">20-21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td>
</td> <td class="xl73">
</td> <td class="xl80">
</td> <td class="xl73">
</td> <td class="xl73">
</td> <td class="xl73">
</td> <td class="xl73">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl83">OFFENSE</td> <td class="xl74">Runs</td> <td class="xl74">AB</td> <td class="xl74">Hits</td> <td class="xl74">EB</td> <td class="xl74">HR</td> <td class="xl74">RBI</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl71">SEATTLE</td> <td class="xl73">3.9</td> <td class="xl79">35.2</td> <td class="xl73">8.4</td> <td class="xl79">2.5</td> <td class="xl73">1</td> <td class="xl79">3.7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td>
</td> <td class="xl81">Runs</td> <td class="xl81">AB</td> <td class="xl81">Hits</td> <td class="xl81">EB</td> <td class="xl81">HR</td> <td class="xl81">RBI</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl72">COLORADO</td> <td class="xl73">5.1</td> <td class="xl79">33.9</td> <td class="xl73">9.4</td> <td class="xl79">4</td> <td class="xl73">1.1</td> <td class="xl79">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td>
</td> <td class="xl82">
</td> <td class="xl82">
</td> <td class="xl82">
</td> <td class="xl82">
</td> <td class="xl82">
</td> <td class="xl82">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl76">Vs Pitcher Arm (L/R)</td> <td class="xl74">GP</td> <td class="xl74">AB</td> <td class="xl74">Runs</td> <td class="xl74">Hits</td> <td class="xl74">RBI</td> <td class="xl74">BA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl68">RIGHT</td> <td class="xl73">26</td> <td class="xl79">286</td> <td class="xl73">48</td> <td class="xl79">84</td> <td class="xl73">48</td> <td class="xl79">0.294</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl69">LEFT</td> <td class="xl73">41</td> <td class="xl79">1213</td> <td class="xl73">146</td> <td class="xl79">319</td> <td class="xl73">137</td> <td class="xl79">0.263</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td>
</td> <td class="xl73">
</td> <td class="xl73">
</td> <td class="xl73">
</td> <td class="xl73">
</td> <td class="xl73">
</td> <td class="xl73">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl65">Pitchers</td> <td class="xl74">Games</td> <td class="xl74">W.L</td> <td class="xl74">Team_W.L</td> <td class="xl74">Win_Pct</td> <td class="xl74">Units</td> <td class="xl65">Ov_Un_P</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl68">A COOK</td> <td class="xl73">9</td> <td class="xl79">5-2</td> <td class="xl73">7-2</td> <td class="xl79">0.778</td> <td class="xl73">5.4</td> <td class="xl79">3-6-0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl69">J SANCHEZ</td> <td class="xl73">8</td> <td class="xl79">2-2</td> <td class="xl73">6-2</td> <td class="xl79">0.75</td> <td class="xl73">5.2</td> <td class="xl79">4-4-0</td> </tr> </tbody></table>
 
Upvote 0
Example of web query from statfox where i want to create my main drop down list from.

<table style="border-collapse: collapse; width: 5px; height: 188px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 141pt;" height="20" width="188">
</td><td style="width: 25pt;" width="33">
</td><td style="width: 26pt;" width="35">
</td><td style="width: 86pt;" width="115">
</td><td style="width: 46pt;" width="61">
</td><td style="width: 41pt;" width="55">
</td><td style="width: 239pt;" width="318">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td> </tr> </tbody></table> <table style="border-collapse: collapse; width: 816pt;" border="0" cellpadding="0" cellspacing="0" width="1087"><col style="width: 141pt;" width="188"> <col style="width: 25pt;" width="33"> <col style="width: 26pt;" width="35"> <col style="width: 86pt;" width="115"> <col style="width: 46pt;" width="61"> <col style="width: 41pt;" width="55"> <col style="width: 239pt;" width="318"> <col style="width: 23pt;" width="31"> <col style="width: 50pt;" width="66"> <col style="width: 26pt;" width="35"> <col style="width: 32pt;" width="42"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 141pt;" height="20" width="188">Friday, 5/16/2008 (9:40 PM)</td> <td style="width: 25pt;" width="33">
</td> <td style="width: 26pt;" width="35">
</td> <td style="width: 86pt;" width="115">DETROIT at</td> <td style="width: 46pt;" width="61">
</td> <td style="width: 41pt;" width="55">
</td> <td style="width: 239pt;" width="318">Conditions: Partly Cloudy Temperature: 74</td> <td style="width: 23pt;" width="31">
</td> <td style="width: 50pt;" width="66">
</td> <td style="width: 26pt;" width="35">
</td> <td style="width: 32pt;" width="42">
</td> <td style="width: 81pt;" width="108">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Matchup Report | FoxSheet</td> <td>
</td> <td>
</td> <td>ARIZONA</td> <td>
</td> <td>
</td> <td>Wind: From the Northeast at 14 MPH</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Teams</td> <td>Line</td> <td>OU</td> <td>W-L</td> <td>Ov/Un/P</td> <td>RF/RA</td> <td>Starters</td> <td>W-L</td> <td>Team Rec</td> <td>ERA</td> <td>WHIP</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">923: DETROIT</td> <td align="right">115</td> <td align="right">9</td> <td>16-25 (L4)</td> <td>18-19-4</td> <td>4.5 / 5.4</td> <td>(R) J BONDERMAN</td> <td>2-4</td> <td>4-4 (L2)</td> <td align="right">4.8</td> <td align="right">1.667</td> <td>DETROIT</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">924: ARIZONA</td> <td align="right">-125</td> <td>
</td> <td>26-15 (W3)</td> <td>23-15-3</td> <td>5.6 / 4.3</td> <td>(R) D HAREN</td> <td>4-2</td> <td>4-4 (L2)</td> <td align="right">3.04</td> <td align="right">0.993</td> <td>ARIZONA</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Friday, 5/16/2008 (10:05 PM)</td> <td>
</td> <td>
</td> <td>LA DODGERS at</td> <td>
</td> <td>
</td> <td>Conditions: A Few Clouds Temperature: 63</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Matchup Report | FoxSheet</td> <td>
</td> <td>
</td> <td>LA ANGELS</td> <td>
</td> <td>
</td> <td>Wind: Calm</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Teams</td> <td>Line</td> <td>OU</td> <td>W-L</td> <td>Ov/Un/P</td> <td>RF/RA</td> <td>Starters</td> <td>W-L</td> <td>Team Rec</td> <td>ERA</td> <td>WHIP</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">925: LA DODGERS</td> <td align="right">110</td> <td align="right">8.5</td> <td>21-19 (W2)</td> <td>22-17-1</td> <td>5.0 / 4.5</td> <td>(R) H KURODA</td> <td>1-2</td> <td>4-4 (L1)</td> <td align="right">3.59</td> <td align="right">1.322</td> <td>LA DODGERS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">926: LA ANGELS</td> <td align="right">-120</td> <td>
</td> <td>24-19 (L2)</td> <td>17-22-4</td> <td>4.5 / 4.5</td> <td>(L) J SAUNDERS</td> <td>6-1</td> <td>7-1 (L1)</td> <td align="right">2.48</td> <td align="right">1.123</td> <td>LA ANGELS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Friday, 5/16/2008 (10:10 PM)</td> <td>
</td> <td>
</td> <td>SAN DIEGO at</td> <td>
</td> <td>
</td> <td>Conditions: Fair Temperature: 54</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Matchup Report | FoxSheet</td> <td>
</td> <td>
</td> <td>SEATTLE</td> <td>
</td> <td>
</td> <td>Wind: From the Northwest at 15 MPH</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Teams</td> <td>Line</td> <td>OU</td> <td>W-L</td> <td>Ov/Un/P</td> <td>RF/RA</td> <td>Starters</td> <td>W-L</td> <td>Team Rec</td> <td>ERA</td> <td>WHIP</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">927: SAN DIEGO</td> <td align="right">-105</td> <td align="right">8.5</td> <td>15-27 (L2)</td> <td>17-24-1</td> <td>3.3 / 4.8</td> <td>(R) C YOUNG</td> <td>3-3</td> <td>3-5 (W1)</td> <td align="right">3.94</td> <td align="right">1.445</td> <td>SAN DIEGO</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">928: SEATTLE</td> <td align="right">-105</td> <td>
</td> <td>16-26 (W1)</td> <td>18-22-2</td> <td>4.1 / 4.7</td> <td>(R) M BATISTA</td> <td>3-4</td> <td>3-5 (W1)</td> <td align="right">5.72</td> <td align="right">1.932</td> <td>SEATTLE</td> </tr> </tbody></table>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
437
bobaol
B
J
  • Solved
Excluding a Cell range from a selection in a drop down list
Replies
3
Views
788
Georgiboy
Georgiboy
C
  • Question Question
How to set up multiple drop down lists that are dependent on the selection in a single drop down list
Replies
1
Views
818
Skybluekid
Skybluekid
S
  • Question Question
VBA question: how to highlight one bar color in pivot chart depending on cell value from drop down list
Replies
1
Views
346
sbadrieva
S
T
  • Solved
Multiple Dependent Drop Down Lists
Replies
5
Views
989
Eric W
Eric W

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

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

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

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

Disable uBlock

Follow these easy steps to disable uBlock

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