Drafttracker (Excel Kit) - Final Update 3.21.09

[C2ThaLo88]

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.

[Curtis Pride]

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.

[rookies and cream]

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.

[C2ThaLo88]

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?

[rookies and cream]

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 *

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...

[swyck]

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.

[jlm53089]

Ill give you some feedback when I get on a computer with excel.

[RRGL1]

Great work guys

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

[rookies and cream]

Great work guys

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

[RynoDyno]

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?