Drafttracker (Excel Kit) - Final Update 3.21.09 - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Drafttracker (Excel Kit) - Final Update 3.21.09

Moderator: Baseball Moderators

Re: Excel Draft Kit

Postby C2ThaLo88 » Mon Feb 09, 2009 12:48 pm

Curtis Pride wrote:Conditional formatting is the easy way to do what you are looking for.

No need for cell equations.

To make it easy, have an extra column on each tab that shows "-1" or really anything you want if the player is listed on the drafted tab (vlookup).

Then have a conditional formatting statement that says if the extra column on the page is "-1" that you strike out the name or delete it entirely with a white font (I personally like strikeout).

Conditional formatting doesn't use as much system resources as having a bunch of formulas in every cell, so the sheet may be faster.


The point was to do it so there was no need to have an extra column to put an "X" or check mark or in this case a "-1." And that by typing in the players name once drafted it does the work for you on the stat pages. It's definitely easier with the conditional formatting however. You could always just manually strike out or turn text white just as easily without any formulas or formatting.
C2ThaLo88 Beginner
Minor League Mentor
Minor League Mentor

User avatar

Posts: 379
Joined: 5 Dec 2008
Home Cafe: Baseball

Re: Excel Draft Kit

Postby Curtis Pride » Mon Feb 09, 2009 12:53 pm

Well, you wouldn't NEEED to have the extra column, you could have it all driven in the conditional formatting equation, but this is just simpler to explain.

And doing it this way (or with the more complex conditional formatting equation) the striking out (or whiting out) would automatically flow through to each other tab once you put the name on the draft tracker.

But it looks like it's moot as I've read the rest of the thread, it looks like they've solved the problem anyway.
Curtis Pride
Major League Manager
Major League Manager

User avatar

Posts: 1361
(Past Year: 3)
Joined: 19 May 2007
Home Cafe: Baseball

Re: Excel Draft Kit

Postby rookies and cream » Mon Feb 09, 2009 12:54 pm

C2ThaLo88 wrote:
Curtis Pride wrote:Conditional formatting is the easy way to do what you are looking for.

No need for cell equations.

To make it easy, have an extra column on each tab that shows "-1" or really anything you want if the player is listed on the drafted tab (vlookup).

Then have a conditional formatting statement that says if the extra column on the page is "-1" that you strike out the name or delete it entirely with a white font (I personally like strikeout).

Conditional formatting doesn't use as much system resources as having a bunch of formulas in every cell, so the sheet may be faster.


The point was to do it so there was no need to have an extra column to put an "X" or check mark or in this case a "-1." And that by typing in the players name once drafted it does the work for you on the stat pages. It's definitely easier with the conditional formatting however. You could always just manually strike out or turn text white just as easily without any formulas or formatting.


There must be a way that this could happen automatically though...

1) Player entered in drafttracker
2) Vlookup to player projection page
3) Position of player registered (next to player's name)
4) Position removed from drafttracker

I also think we should tabulate each team's 5x5 stats using forthcoming consensus projections.
Image
rookies and cream
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterWeb Supporter
Posts: 6487
(Past Year: 43)
Joined: 4 Apr 2007
Home Cafe: Baseball
Location: Long Island, NY

Re: Excel Draft Kit

Postby C2ThaLo88 » Mon Feb 09, 2009 1:03 pm

rookies and cream wrote:I also think we should tabulate each team's 5x5 stats using forthcoming consensus projections.

That's a great Idea, would not be too hard to do, I think just a lot of busy work. Trouble comes in when a player not projected gets drafted. But we could put a * or something to denote when that happens.

As far as position removal.... What do we do when a player with eligibility in 2B and SS gets drafted? How will it know which one to remove? Can we write up something so that it does either 2B or SS and then depending on if 2B or SS is already removed it will remove the other?
C2ThaLo88 Beginner
Minor League Mentor
Minor League Mentor

User avatar

Posts: 379
Joined: 5 Dec 2008
Home Cafe: Baseball

Re: Excel Draft Kit

Postby rookies and cream » Mon Feb 09, 2009 1:06 pm

C2ThaLo88 wrote:That's a great Idea, would not be too hard to do, I think just a lot of busy work. Trouble comes in when a player not projected gets drafted. But we could put a * or something to denote when that happens.


I highly doubt this will happen, given all the players in the database. However, we could do what you suggest with the *

C2ThaLo88 wrote:As far as position removal.... What do we do when a player with eligibility in 2B and SS gets drafted? How will it know which one to remove? Can we write up something so that it does either 2B or SS and then depending on if 2B or SS is already removed it will remove the other?


Yeah. Seems too complicated...
Image
rookies and cream
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterWeb Supporter
Posts: 6487
(Past Year: 43)
Joined: 4 Apr 2007
Home Cafe: Baseball
Location: Long Island, NY

Re: Excel Draft Kit

Postby swyck » Mon Feb 09, 2009 2:00 pm

C2ThaLo88 wrote:
rookies and cream wrote:I also think we should tabulate each team's 5x5 stats using forthcoming consensus projections.

That's a great Idea, would not be too hard to do, I think just a lot of busy work. Trouble comes in when a player not projected gets drafted. But we could put a * or something to denote when that happens.

As far as position removal.... What do we do when a player with eligibility in 2B and SS gets drafted? How will it know which one to remove? Can we write up something so that it does either 2B or SS and then depending on if 2B or SS is already removed it will remove the other?


Once upon a time I did a spreadsheet with player projections using conditional formatting to gray out the row of the players that were drafted. I did this with a drafted column. I used 1 for my team and the rest were 2-12. So If =$D2=1 then the row would be yellow, if =$D2>1 it would be gray. Relatively easy to do.

You can now create a pivot table that can automatically fill in the sums of all the stats by team. So you can easily compare yourself to the other teams as the draft is going. Remember to average uh the average instead of summing it. You can also create a formula to get the real average based on projections, but I never bothered. Again this is relatively easy to do.

I also was able to create an automatic tier list of the top 12 players at each position that was not drafted. I remember it was a PITA to set up but worked fairly well. I think I used HQ projections since they have position listings. Separating SP and RP automatically was also a bit of work, don't recall what I did but probably saves or IP were used to differentiate. This was a lot more work.

For an easier process it should be possible to use pivot tables to sort the undrafted players by position. HQ has the positions data and you can use filters to show only undrafted players by position. However I'm not sure how to set up a filter to include all players at a position for HQ data. For example 2B includes 4, 45, 64, 46, etc. Figuring this out would make it quick to see the list of undrafted position players. It may be easier to create a column for each position, put an x there say for 2b if it finds a 4 in the position string. Then create a pivot table for each position.

As soon as a player is drafted, you enter the appropriate number in the draft column, the other pages are automatically updated, the team stats, the player tiers, whatever. This worked pretty good.

However, I find that an online draft goes too fast for me to itemize each team. I start falling behind and I'm trying to update the sheet instead of paying attention to what's happening and concentrating on my next pick. I find it better to just list a player as drafted by me or drafted by another team. If I use the pivots I should be able to see my team pretty well, and I can compare to a list of desired stat levels. I should also be able to list the count of drafted 2b,ss, etc., so I can anticipate runs.

I think I'll do one again this year using pivot tables to streamline the work I have to do. Total work to setup I estimate at one to two hours. Too bad that Marcel's doesn't include position data or I'd use that and post it. As is I'll probably use HQ.
swyck
Major League Manager
Major League Manager

User avatar
Cafe Ranker
Posts: 1524
(Past Year: 14)
Joined: 7 Feb 2005
Home Cafe: Baseball

Re: Excel Draft Kit

Postby jlm53089 » Mon Feb 09, 2009 3:26 pm

Ill give you some feedback when I get on a computer with excel.
Image
jlm53089
General Manager
General Manager

User avatar
Pick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 3058
(Past Year: 3)
Joined: 6 Feb 2006
Home Cafe: Baseball
Location: Tampa, praying for a new stadium.

Re: Excel Draft Kit

Postby RRGL1 » Mon Feb 09, 2009 4:18 pm

Great work guys ;-D

One thing I noticed, on the Draft Tracker tab, there are 2 "Chris Young", 1 needs to be "Chris B. Young".
RRGL1
Minor League Mentor
Minor League Mentor


Posts: 536
Joined: 13 Mar 2004
Home Cafe: Baseball

Re: Excel Draft Kit

Postby rookies and cream » Mon Feb 09, 2009 4:21 pm

RRGL1 wrote:Great work guys ;-D

One thing I noticed, on the Draft Tracker tab, there are 2 "Chris Young", 1 needs to be "Chris B. Young".


1. Unhide rows 1-392
2. Change one of Chris Youngs to Chris B.
3. Copy across row

I'll fix this on the master
Image
rookies and cream
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterWeb Supporter
Posts: 6487
(Past Year: 43)
Joined: 4 Apr 2007
Home Cafe: Baseball
Location: Long Island, NY

Re: Excel Draft Kit

Postby RynoDyno » Mon Feb 09, 2009 4:39 pm

I show one Chris Young and one Chris B Young under the draft tracker sheet (for the pulldown menus), maybe I have a newer version?
DaSh 1s wrote:PS how do u not want to cup the best player in baseball's sack?


ewwww
RynoDyno
College Coach
College Coach

User avatar

Posts: 274
Joined: 7 Jan 2005
Home Cafe: Baseball

PreviousNext

Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 7 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Tuesday, Jul. 29
(All times are EST, weather icons show forecast for game time)

LA Angels at Baltimore
(7:05 pm)
Seattle at Cleveland
(7:05 pm)
Chi White Sox at Detroit
(7:08 pm)
Milwaukee at Tampa Bay
(7:10 pm)
indoors
Philadelphia at NY Mets
(7:10 pm)
Washington at Miami
(7:10 pm)
indoors
Arizona at Cincinnati
(7:10 pm)
Toronto at Boston
(7:10 pm)
Colorado at Chi Cubs
(8:05 pm)
NY Yankees at Texas
(8:05 pm)
Oakland at Houston
(8:10 pm)
Minnesota at Kansas City
(8:10 pm)
Atlanta at LA Dodgers
(10:10 pm)
St. Louis at San Diego
(10:10 pm)
Pittsburgh at San Francisco
(10:15 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact