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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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