My Formula too long. It is an epic Formula though

Hey Everyone,

First post ever to this forum, but I have a good one. I made an Excel Sheet from my Fantasy Draft as we do it offline, and this helps me keep track of my picks / players.

Sheet two is my Master List - This is where I store the lists of all of my players by postion.
Sheet two is my Pick List - This is where it will show my next available pick by postion / round / pick.

My pick list needs tweaking, but that is fine.

The error comes into play when I want to do the following:

I want to have Colum C check the value of Colum B's corresponding cell. What this really is doing is checking what postion I want to choose. In doing so it uses the formula I hashed out to decern what players are available.

Here is the basic fomula for Checking the master list:

This Code will Check for available TEs (Tight Ends): I have to manualy copy and paste my formulas if I want to change the postion and pickpostion:

=IF('Master List'!G2=0,IF('Master List'!G3=0,IF('Master List'!G4=0,IF('Master List'!G5=0,IF('Master List'!G6=0,IF('Master List'!G7=0,IF('Master List'!G8=0,IF('Master List'!G9=0,IF('Master List'!G10=0,IF('Master List'!G11=0,IF('Master List'!G14=0,IF('Master List'!G15=0,IF('Master List'!G16=0,IF('Master List'!G17=0,IF('Master List'!G18=0,IF('Master List'!G19=0,IF('Master List'!G20=0,IF('Master List'!G21=0,IF('Master List'!G22=0,IF('Master List'!G23=0,IF('Master List'!G26=0,IF('Master List'!G27=0,IF('Master List'!G28=0,IF('Master List'!G29=0,IF('Master List'!G30=0,IF('Master List'!G31=0,IF('Master List'!G32=0,IF('Master List'!G33=0,IF('Master List'!G34=0,IF('Master List'!G35=0,IF('Master List'!G36=0,IF('Master List'!G37=0,IF('Master List'!G21=0,"NoPlGyGrs",'Master List'!G38),'Master List'!G37),'Master List'!G36),'Master List'!G35),'Master List'!G34),'Master List'!G33),'Master List'!G32),'Master List'!G31),'Master List'!G30),'Master List'!G29),'Master List'!G28),'Master List'!G27),'Master List'!G26),'Master List'!G23),'Master List'!G22),'Master List'!G21),'Master List'!G20),'Master List'!G19),'Master List'!G18),'Master List'!G17),'Master List'!G16),'Master List'!G15),'Master List'!G14),'Master List'!G11),'Master List'!G10),'Master List'!G9),'Master List'!G8),'Master List'!G7),'Master List'!G6),'Master List'!G5),'Master List'!G4),'Master List'!G3),'Master List'!G2)

Here is the formula that is to long (I have 7 postions my code contains 4):

=IF(B21="K",IF('Master List'!H2=0,IF('Master List'!H3=0,IF('Master List'!H4=0,IF('Master List'!H5=0,IF('Master List'!H6=0,IF('Master List'!H7=0,IF('Master List'!H8=0,IF('Master List'!H9=0,IF('Master List'!H10=0,IF('Master List'!H11=0,IF('Master List'!H14=0,IF('Master List'!H15=0,IF('Master List'!H16=0,IF('Master List'!H17=0,IF('Master List'!H18=0,IF('Master List'!H19=0,IF('Master List'!H20=0,IF('Master List'!H21=0,IF('Master List'!H22=0,IF('Master List'!H23=0,IF('Master List'!H26=0,IF('Master List'!H27=0,IF('Master List'!H28=0,IF('Master List'!H29=0,IF('Master List'!H30=0,IF('Master List'!H31=0,IF('Master List'!H32=0,IF('Master List'!H33=0,IF('Master List'!H34=0,IF('Master List'!H35=0,IF('Master List'!H36=0,IF('Master List'!H37=0,IF('Master List'!H21=0,"NoPlHyHrs",'Master List'!H38),'Master List'!H37),'Master List'!H36),'Master List'!H35),'Master List'!H34),'Master List'!H33),'Master List'!H32),'Master List'!H31),'Master List'!H30),'Master List'!H29),'Master List'!H28),'Master List'!H27),'Master List'!H26),'Master List'!H23),'Master List'!H22),'Master List'!H21),'Master List'!H20),'Master List'!H19),'Master List'!H18),'Master List'!H17),'Master List'!H16),'Master List'!H15),'Master List'!H14),'Master List'!H11),'Master List'!H10),'Master List'!H9),'Master List'!H8),'Master List'!H7),'Master List'!H6),'Master List'!H5),'Master List'!H4),'Master List'!H3),'Master List'!H2),IF(B21="QB",IF('Master List'!A2=0,IF('Master List'!A3=0,IF('Master List'!A4=0,IF('Master List'!A5=0,IF('Master List'!A6=0,IF('Master List'!A7=0,IF('Master List'!A8=0,IF('Master List'!A9=0,IF('Master List'!A10=0,IF('Master List'!A11=0,IF('Master List'!A14=0,IF('Master List'!A15=0,IF('Master List'!A16=0,IF('Master List'!A17=0,IF('Master List'!A18=0,IF('Master List'!A19=0,IF('Master List'!A20=0,IF('Master List'!A21=0,IF('Master List'!A22=0,IF('Master List'!A23=0,IF('Master List'!A26=0,IF('Master List'!A27=0,IF('Master List'!A28=0,IF('Master List'!A29=0,IF('Master List'!A30=0,IF('Master List'!A31=0,IF('Master List'!A32=0,IF('Master List'!A33=0,IF('Master List'!A34=0,IF('Master List'!A35=0,IF('Master List'!A36=0,IF('Master List'!A37=0,IF('Master List'!A21=0,"NoPlayers",'Master List'!A38),'Master List'!A37),'Master List'!A36),'Master List'!A35),'Master List'!A34),'Master List'!A33),'Master List'!A32),'Master List'!A31),'Master List'!A30),'Master List'!A29),'Master List'!A28),'Master List'!A27),'Master List'!A26),'Master List'!A23),'Master List'!A22),'Master List'!A21),'Master List'!A20),'Master List'!A19),'Master List'!A18),'Master List'!A17),'Master List'!A16),'Master List'!A15),'Master List'!A14),'Master List'!A11),'Master List'!A10),'Master List'!A9),'Master List'!A8),'Master List'!A7),'Master List'!A6),'Master List'!A5),'Master List'!A4),'Master List'!A3),'Master List'!A2),IF(B21="RB",IF('Master List'!B2=0,IF('Master List'!B3=0,IF('Master List'!B4=0,IF('Master List'!B5=0,IF('Master List'!B6=0,IF('Master List'!B7=0,IF('Master List'!B8=0,IF('Master List'!B9=0,IF('Master List'!B10=0,IF('Master List'!B11=0,IF('Master List'!B14=0,IF('Master List'!B15=0,IF('Master List'!B16=0,IF('Master List'!B17=0,IF('Master List'!B18=0,IF('Master List'!B19=0,IF('Master List'!B20=0,IF('Master List'!B21=0,IF('Master List'!B22=0,IF('Master List'!B23=0,IF('Master List'!B26=0,IF('Master List'!B27=0,IF('Master List'!B28=0,IF('Master List'!B29=0,IF('Master List'!B30=0,IF('Master List'!B31=0,IF('Master List'!B32=0,IF('Master List'!B33=0,IF('Master List'!B34=0,IF('Master List'!B35=0,IF('Master List'!B36=0,IF('Master List'!B37=0,IF('Master List'!B21=0,"NoPlayers",'Master List'!B38),'Master List'!B37),'Master List'!B36),'Master List'!B35),'Master List'!B34),'Master List'!B33),'Master List'!B32),'Master List'!B31),'Master List'!B30),'Master List'!B29),'Master List'!B28),'Master List'!B27),'Master List'!B26),'Master List'!B23),'Master List'!B22),'Master List'!B21),'Master List'!B20),'Master List'!B19),'Master List'!B18),'Master List'!B17),'Master List'!B16),'Master List'!B15),'Master List'!B14),'Master List'!B11),'Master List'!B10),'Master List'!B9),'Master List'!B8),'Master List'!B7),'Master List'!B6),'Master List'!B5),'Master List'!B4),'Master List'!B3),'Master List'!B2),IF(B21="WR",IF('Master List'!C2=0,IF('Master List'!C3=0,IF('Master List'!C4=0,IF('Master List'!C5=0,IF('Master List'!C6=0,IF('Master List'!C7=0,IF('Master List'!C8=0,IF('Master List'!C9=0,IF('Master List'!C10=0,IF('Master List'!C11=0,IF('Master List'!C14=0,IF('Master List'!C15=0,IF('Master List'!C16=0,IF('Master List'!C17=0,IF('Master List'!C18=0,IF('Master List'!C19=0,IF('Master List'!C20=0,IF('Master List'!C21=0,IF('Master List'!C22=0,IF('Master List'!C23=0,IF('Master List'!C26=0,IF('Master List'!C27=0,IF('Master List'!C28=0,IF('Master List'!C29=0,IF('Master List'!C30=0,IF('Master List'!C31=0,IF('Master List'!C32=0,IF('Master List'!C33=0,IF('Master List'!C34=0,IF('Master List'!C35=0,IF('Master List'!C36=0,IF('Master List'!C37=0,IF('Master List'!C21=0,"NoPlCyers",'Master List'!C38),'Master List'!C37),'Master List'!C36),'Master List'!C35),'Master List'!C34),'Master List'!C33),'Master List'!C32),'Master List'!C31),'Master List'!C30),'Master List'!C29),'Master List'!C28),'Master List'!C27),'Master List'!C26),'Master List'!C23),'Master List'!C22),'Master List'!C21),'Master List'!C20),'Master List'!C19),'Master List'!C18),'Master List'!C17),'Master List'!C16),'Master List'!C15),'Master List'!C14),'Master List'!C11),'Master List'!C10),'Master List'!C9),'Master List'!C8),'Master List'!C7),'Master List'!C6),'Master List'!C5),'Master List'!C4),'Master List'!C3),'Master List'!C2),IF(B21="TE",IF('Master List'!D2=0,IF('Master List'!D3=0,IF('Master List'!D4=0,IF('Master List'!D5=0,IF('Master List'!D6=0,IF('Master List'!D7=0,IF('Master List'!D8=0,IF('Master List'!D9=0,IF('Master List'!D10=0,IF('Master List'!D11=0,IF('Master List'!D14=0,IF('Master List'!D15=0,IF('Master List'!D16=0,IF('Master List'!D17=0,IF('Master List'!D18=0,IF('Master List'!D19=0,IF('Master List'!D20=0,IF('Master List'!D21=0,IF('Master List'!D22=0,IF('Master List'!D23=0,IF('Master List'!D26=0,IF('Master List'!D27=0,IF('Master List'!D28=0,IF('Master List'!D29=0,IF('Master List'!D30=0,IF('Master List'!D31=0,IF('Master List'!D32=0,IF('Master List'!D33=0,IF('Master List'!D34=0,IF('Master List'!D35=0,IF('Master List'!D36=0,IF('Master List'!D37=0,IF('Master List'!D21=0,"NoPlDyers",'Master List'!D38),'Master List'!D37),'Master List'!D36),'Master List'!D35),'Master List'!D34),'Master List'!D33),'Master List'!D32),'Master List'!D31),'Master List'!D30),'Master List'!D29),'Master List'!D28),'Master List'!D27),'Master List'!D26),'Master List'!D23),'Master List'!D22),'Master List'!D21),'Master List'!D20),'Master List'!D19),'Master List'!D18),'Master List'!D17),'Master List'!D16),'Master List'!D15),'Master List'!D14),'Master List'!D11),'Master List'!D10),'Master List'!D9),'Master List'!D8),'Master List'!D7),'Master List'!D6),'Master List'!D5),'Master List'!D4),'Master List'!D3),'Master List'!D2),"YOU BROKE IT")))))

Is there a smarter way to do this? If so help would be great.

If you have any questions please ask, especialy as this may be a very long and conveluted way of doing things.

Thanks in Advance

-R0113

P.S - I have attachged the File so it all makes sense - This is the Generic file I am using for testing and tinkering so I do not break my real NFL Draft File.

The random typing on Master List - Are Fillers. The spaces on the generic sheet are for spaces and Headers so my Real File look Nice.

Thanks Again,

R0113