SupaStud 0 Posted August 30, 2011 I have a league where we draft two teams - one AFC and one NFC. I can't seem to find a place where the functionality could be, but I'm not awesome at Excel either. Is it possible to, say, exclude team a-p from a rankings, and k-z in another file? Share this post Link to post Share on other sites
Mike MacGregor 20 Posted August 30, 2011 Hi SupaStud. It can be done with a little ingenuity. What I'm going to do is have you put in formulas that change the allocation key so it zeroes out the projections for players in one conference, while keeping them at 100% for the other. We'll reverse the formulas in each file, AFC vs. NFC. Do the following steps: 1. Go to the options tab, the box with the allocation keys, and define one of them - let's say the blank 13th one at the bottom - to 0% in each of the 7 boxes across. 2. Go to the QB position tab, and click the first "+" sign across the top. This will unhide some columns, including one highlighted yellow where you can change the allocation key on a per player basis. 3. In the cell for the first player in that column, cell L5, put in the following formula: =IF(ISNA(VLOOKUP(C5,depth!$B$9:$B$24,1,0)),13,1) The reference to the depth tab is a group of cells for the teams in the AFC. Note the depth tab happens to be sorted by conference. What the formula is basically saying is, if the team for the current player is not found (i.e. not in the AFC), then give him our new 13th allocation key (zeroes). Otherwise, keep him on 1 (FF Today 100%). 4. Copy that formula, and paste it down for all players on the QB tab. 5. Repeat steps 2-4 for each of the RB, WR, TE and K tabs. For the DEF tab you want the exact same formula, just note when you copy and paste it Excel might slightly alter it on you because the allocation key column is in a different spot. Okay, so at this point you have your AFC file. You'll do exactly the same thing for your NFC file, except the formula is this: =IF(ISNA(VLOOKUP(C5,depth!$B$9:$B$24,1,0)),1,13) You'll need to hit Compile Cheatsheets afterwards to see the impact of your changes. Good luck! Share this post Link to post Share on other sites
SupaStud 0 Posted August 30, 2011 Awesome! Worked like a charm! That might even convince me to do this thing live next year! Thanks a lot! Share this post Link to post Share on other sites
SupaStud 0 Posted August 30, 2011 Also noticed one other little glitch. The TE are no longer a different color on the list. Any idea how that happened? Share this post Link to post Share on other sites
SupaStud 0 Posted August 30, 2011 Also noticed one other little glitch. The TE are no longer a different color on the list. Any idea how that happened? You know what, I just looked again - and its like that in the default compiler too. Share this post Link to post Share on other sites
