Results 1 to 15 of 15

Thread: Excel Help

  1. #1
    LawMan's Avatar
    LawMan is offline
    Join Date
    Sep 2011
    Posts
    5,193
    Rep Power
    50

    Dobber Sports Superstar

    Default Excel Help

    Lots of Smart People on here hopefully somebody has the answer. So I've got my Fantasy Hockey spreadsheet and I've learned a ton about shortcuts and formulas etc. but this one I can't figure out.

    So I'm got one master sheet named "Skaters" it lists the top-400 skaters and the projections for each. As a player is drafted I fill their name in Red so I don't look try and draft him and like an idiot. I have another sheet named "Team" this is my team and starts as an empty roster which sums the projections for my team. Each time I draft a player I copy his name and stats from "skaters" and paste his name and projections into the "Team" sheet. It works and isn't terribly inefficient but also isn't perfect.

    I saw a guy one time just start typing "Jack Eichel" into the "Team" excel found the player, and stats, and he hits enter and the stats are automatically transferred, no shifting between sheets, no control+f to find the player in "Skaters" no mis-click on the copy/paste. Super easy.

    Anyone know the process for this? Thanks in Advance.
    12 team H-2-H 1 year league, daily roster changes, 3 goalie start minimum/week
    2xC, 2xRW, 2xLW, 4xD, 3xUtil, 2xG, 5 Bench
    G, A, P, PIM, PPP, SHP, GWG, SOG, Hits, W, SV%, GAA, SVs
    C: C. Keller, C. Mittelstadt, B. Nelson, R. Strome,
    LW: K. Connor, B. Tkachuk, J. Gaudreau, J. Marchessault, E. Rodrigues, A. Lafreniere
    RW: K. Fiala, J. Bratt, T. Jeannot V. Arvidsson
    D: R. Josi, J. Trouba, E. Gustafsson,
    G: L. Thompson, F. Gustavsson, V. Vanecek
    NO IR

  2. #2
    Rep Power
    40

    Dobber Sports Supreme Grand Master

    Default Re: Excel Help

    Ohh I have done this... let me play around and see.

    What you pretty much have to do is create the players into a list... and then in your Team page you Create a Selection list that has a drop down arrow with all 400 players.

    http://www.techrepublic.com/blog/mic...an-excel-cell/

    Give this page a try and see if it helps.

  3. #3
    Location
    Toronto
    Rep Power
    50

    Dobber Sports Star

    Default Re: Excel Help

    It's a vlookup.

    Syntax is as follows: =VLOOKUP(cell with the player name, range of data table to look up, column number within that table to return, false)

    Lots of good tutorials out there, but this is the guts of it.

  4. #4
    Rep Power
    22

    Dobber Sports Apprentice

    Default Re: Excel Help

    Another +1 for vlookup.


    14 Team H2H Cap
    G A DPTS FOW SOG STP H+B | W SV SV% GAA

    C: Scheifele, Stamkos (rw), Tavares
    LW: Gaudreau, Giroux (c/rw), Labanc(rw)
    RW: Atkinson, Thompson (c), Keller (lw), Glendening (c)
    D: Klingberg, Gostisbehere, Dumba, Krug (50%), Trouba, McNabb, Gudas
    G: Holtby, Fleury, Markstrom


    FARM NOTABLES:
    F: Bokk, A Thomas, Kaprizov, Dorofeyev, Mikheyev
    D: Dalhin, Beaudin, Dunn, Kylington, Harley
    G: Hart, Demko

  5. #5
    Location
    Stratford
    Rep Power
    19

    Dobber Sports Apprentice

    Default Re: Excel Help

    If you send me the spreadsheet I could probably set it up for you.
    Yahoo 12 team H2H Keeper (Keep 7 vets; 3 prospects (Skater < 201 GP; Goalie < 101GP)

    Categories: G, A, P, PIM, +/-, PPP, SOG, FW, Hits, Blocks, W, GAA, Sv% Saves

    Start 3LW, 3C, 3RW, 5D, 2G, 8Bn

    C: Crosby, RNH, Stepan, Faksa
    LW: Ovechkin, Benn, Jenner (c), Komarov (c, rw), Dano
    RW: Voracek (lw), Neal, Hornqvist, Silfverberg, Buchnevich (lw), Boeser
    D: Karlsson, Letang, Ellis, Muzzin, Slavin, Boychuk
    G: Price, Bernier, Grubauer, Pickard

  6. #6
    LawMan's Avatar
    LawMan is offline
    Join Date
    Sep 2011
    Posts
    5,193
    Rep Power
    50

    Dobber Sports Superstar

    Default Re: Excel Help

    Quote Originally Posted by fantasyhockeygeek View Post
    It's a vlookup.

    Syntax is as follows: =VLOOKUP(cell with the player name, range of data table to look up, column number within that table to return, false)

    Lots of good tutorials out there, but this is the guts of it.
    This is helpful, I kept getting lost in the tutorials.

    So far I have this =VLOOKUP(Skaters!b2:b401(players names), Skaters!c2: o401(stats), column number within that column, false)
    1. Does this look right
    2. What does "column number within that column" mean? I want the same column number as the player right?
    12 team H-2-H 1 year league, daily roster changes, 3 goalie start minimum/week
    2xC, 2xRW, 2xLW, 4xD, 3xUtil, 2xG, 5 Bench
    G, A, P, PIM, PPP, SHP, GWG, SOG, Hits, W, SV%, GAA, SVs
    C: C. Keller, C. Mittelstadt, B. Nelson, R. Strome,
    LW: K. Connor, B. Tkachuk, J. Gaudreau, J. Marchessault, E. Rodrigues, A. Lafreniere
    RW: K. Fiala, J. Bratt, T. Jeannot V. Arvidsson
    D: R. Josi, J. Trouba, E. Gustafsson,
    G: L. Thompson, F. Gustavsson, V. Vanecek
    NO IR

  7. #7
    Rep Power
    22

    Dobber Sports Apprentice

    Default Re: Excel Help

    if you PM me or email it to me at [email protected] I can have it done and back within a couple minutes for you.

    Otherwise, "column number within that column" is what column you want to return as data.


    14 Team H2H Cap
    G A DPTS FOW SOG STP H+B | W SV SV% GAA

    C: Scheifele, Stamkos (rw), Tavares
    LW: Gaudreau, Giroux (c/rw), Labanc(rw)
    RW: Atkinson, Thompson (c), Keller (lw), Glendening (c)
    D: Klingberg, Gostisbehere, Dumba, Krug (50%), Trouba, McNabb, Gudas
    G: Holtby, Fleury, Markstrom


    FARM NOTABLES:
    F: Bokk, A Thomas, Kaprizov, Dorofeyev, Mikheyev
    D: Dalhin, Beaudin, Dunn, Kylington, Harley
    G: Hart, Demko

  8. #8
    Location
    Toronto
    Rep Power
    50

    Dobber Sports Star

    Default Re: Excel Help

    Quote Originally Posted by LawMan View Post
    This is helpful, I kept getting lost in the tutorials.

    So far I have this =VLOOKUP(Skaters!b2:b401(players names), Skaters!c2: o401(stats), column number within that column, false)
    1. Does this look right
    2. What does "column number within that column" mean? I want the same column number as the player right?
    Mario nails the column part... so if you want it to return column D, that's "2" (when your range starts in column c). You'll also want to enter it as Skaters!$c$2:$o$401 so the references stay absolute when you copy the formula.

    The first term should be just the single cell of the player name. (in this case, provided the formula is entered on row 2, "Skaters!b2"). When you copy the formula down to the next row, it'll be b3, etc.

  9. #9
    Rep Power
    22

    Dobber Sports Apprentice

    Default Re: Excel Help

    Quote Originally Posted by fantasyhockeygeek View Post
    Mario nails the column part... so if you want it to return column D, that's "2" (when your range starts in column c). You'll also want to enter it as Skaters!$c$2:$o$401 so the references stay absolute when you copy the formula.

    The first term should be just the single cell of the player name. (in this case, provided the formula is entered on row 2, "Skaters!b2"). When you copy the formula down to the next row, it'll be b3, etc.
    YES. The $ signs in your first one will be crucial to making it an easy copy and paste (or populate).


    14 Team H2H Cap
    G A DPTS FOW SOG STP H+B | W SV SV% GAA

    C: Scheifele, Stamkos (rw), Tavares
    LW: Gaudreau, Giroux (c/rw), Labanc(rw)
    RW: Atkinson, Thompson (c), Keller (lw), Glendening (c)
    D: Klingberg, Gostisbehere, Dumba, Krug (50%), Trouba, McNabb, Gudas
    G: Holtby, Fleury, Markstrom


    FARM NOTABLES:
    F: Bokk, A Thomas, Kaprizov, Dorofeyev, Mikheyev
    D: Dalhin, Beaudin, Dunn, Kylington, Harley
    G: Hart, Demko

  10. #10
    LawMan's Avatar
    LawMan is offline
    Join Date
    Sep 2011
    Posts
    5,193
    Rep Power
    50

    Dobber Sports Superstar

    Default Re: Excel Help

    Thanks for the help.

    Can't get it too work, may try again later.
    12 team H-2-H 1 year league, daily roster changes, 3 goalie start minimum/week
    2xC, 2xRW, 2xLW, 4xD, 3xUtil, 2xG, 5 Bench
    G, A, P, PIM, PPP, SHP, GWG, SOG, Hits, W, SV%, GAA, SVs
    C: C. Keller, C. Mittelstadt, B. Nelson, R. Strome,
    LW: K. Connor, B. Tkachuk, J. Gaudreau, J. Marchessault, E. Rodrigues, A. Lafreniere
    RW: K. Fiala, J. Bratt, T. Jeannot V. Arvidsson
    D: R. Josi, J. Trouba, E. Gustafsson,
    G: L. Thompson, F. Gustavsson, V. Vanecek
    NO IR

  11. #11
    Bass56's Avatar
    Bass56 is offline
    Join Date
    Mar 2008
    Posts
    8,332
    Rep Power
    50

    Dobber Sports Giant

    Default Re: Excel Help



    Hehe thanks for posting this LawMan and thanks for the posts/advice fellows! I had a similar issue at work yesterday evening and I was able to use this info!
    Keeper league 14 teams (H2H, 20 active players keep 16 NHLers, 16 minors)
    Scoring Cats: G-A-PTS-PPG-PPA-(+/-)-HITS-BLK-SHP-SOG-FOW-GWG / W-L-SVS-SV%-GAA-SO

    C- Crosby, Barzal, Bennett, Schmaltz, Novak
    W- Forsberg, Batherson, Nichuskin, Tuch, Konecny, Maccelli, Sharangovich, Bertuzzi, Kaliyev, Neighbours, Reichel
    D- Carlson, Rielly, Klingberg
    G- Hellebuyck, Demko, Merzlikins, Vejmelka

    Minors:
    F: Fantilli, Cooley, Bourgault, Perreault, Cowan, Ohgren, N. Foote, Goyette, Iskhakov, Denisenko, Tracey
    D: Simashev, Willander, Lambos
    G: Schmidt, Rodrigue, DiPietro, Gaudreau, McKay

  12. #12
    LawMan's Avatar
    LawMan is offline
    Join Date
    Sep 2011
    Posts
    5,193
    Rep Power
    50

    Dobber Sports Superstar

    Default Re: Excel Help

    Quote Originally Posted by Bass56 View Post


    Hehe thanks for posting this LawMan and thanks for the posts/advice fellows! I had a similar issue at work yesterday evening and I was able to use this info!
    Got it. You were on "DobberSports.com" looking for help with a "work" problem... Office hockey pools count as work right??
    12 team H-2-H 1 year league, daily roster changes, 3 goalie start minimum/week
    2xC, 2xRW, 2xLW, 4xD, 3xUtil, 2xG, 5 Bench
    G, A, P, PIM, PPP, SHP, GWG, SOG, Hits, W, SV%, GAA, SVs
    C: C. Keller, C. Mittelstadt, B. Nelson, R. Strome,
    LW: K. Connor, B. Tkachuk, J. Gaudreau, J. Marchessault, E. Rodrigues, A. Lafreniere
    RW: K. Fiala, J. Bratt, T. Jeannot V. Arvidsson
    D: R. Josi, J. Trouba, E. Gustafsson,
    G: L. Thompson, F. Gustavsson, V. Vanecek
    NO IR

  13. #13
    Mr. Guru's Avatar
    Mr. Guru is offline
    Join Date
    Aug 2009
    Posts
    9,529
    Location
    Toronto
    Rep Power
    50

    The Great One

    Default Re: Excel Help

    Nice project.

    I don't know the answer but how do you get it to import all players?

  14. #14
    Rehdaun's Avatar
    Rehdaun is offline
    Join Date
    Oct 2010
    Posts
    3,104
    Location
    Pittsburgh
    Rep Power
    41

    Dobber Sports Expert

    Default Re: Excel Help

    Quote Originally Posted by eyemissgilmour View Post
    Under the "Data" menu option, there is a "Get External Data" section and you select "From Web" ...
    Thanks! I didn't know about that. Makes scraping data from websites so much easier.

  15. #15
    Rep Power
    0

    Dobber Sports Newbie

    Default Re: Excel Help

    I dont know the answer xD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •