NFL Draft Spreadsheet

jj5484

New Member
Joined
Nov 8, 2013
Messages
6
So to keep my skills up I like to make fun spreadsheets. I worked my way into a bit of a problem that I haven't been able to find a good way out of. Any help would be appreciated.

I have downloaded the draft position for every NFL team for the upcoming draft. Additionally I have downloaded the "needs" for each team as well as the list of "prospects" that have declared for the draft. I have "normalized" the data and it all seems to be working well. Here is the issue I am facing: 1) On the "Dashboard" tab, when you select the team from the drop down in cell A2 (the green cell), you will see the related position by round in the summary (column B) as well as the Round by Round tally starting in cell K12 (the team highlights as green). What I would like to do is have conditional formatting set up so that the "need" is struck through when the team selects the player. EG -- for Jacksonville they pick Trevor Lawrence, a QB. Therefore what I would like to see is the QB designation in cell E3 struck through. NOTE: as the Teams Needs are dynamic, this changes for each team, so the formatting should also be dynamic.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jj5484

New Member
Joined
Nov 8, 2013
Messages
6
NFL2021DRAFT.xlsx
ABCDEFGHIJKLMNOPQRST
1SELECT TEAM
2JacksonvilleTeam Needs
31st2PrimaryQBTEOT  
42nd2SecondaryCBSDEWR  
53rd1AncillaryRBIDLWRIOL  
64th2Don't NeedLB 
75th2
86th0
97th2
1011
11
12Round OneTeamSelectionPositionRound TwoTeamSelectionPosition
131JacksonvilleTrevor LawrenceQB33Jacksonville 
142NY Jets 34NY Jets 
153Miami (from Houston) 35Atlanta 
164Atlanta 36Miami (from Houston) 
175Cincinnati 37Philadelphia 
186Philadelphia 38Cincinnati 
197Detroit 39Carolina 
208Carolina 40Denver 
219Denver 41Detroit 
2210NY Giants 42NY Giants 
2311Dallas 43San Francisco 
2412San Francisco 44Dallas 
2513LA Chargers 45Jacksonville (from Minnesota) 
2614Minnesota 46New England 
2715New England 47LA Chargers 
2816Arizona 48Las Vegas 
2917Las Vegas 49Arizona 
3018Miami 50Miami 
3119Washington 51Washington 
3220Chicago 52Chicago 
3321Indianapolis 53Tennessee 
3422Tennessee 54Indianapolis 
3523NY Jets (from Seattle) 55Pittsburgh 
3624Pittsburgh 56Seattle 
3725Jacksonville (from LA Rams) 57LA Rams 
3826Cleveland 58Baltimore 
3927Baltimore 59Cleveland 
4028New Orleans 60New Orleans 
4129Tampa Bay 61Tampa Bay 
4230Buffalo 62Buffalo 
4331Green Bay 63Green Bay 
4432Kansas City 64Kansas City 
Dashboard
Cell Formulas
RangeFormula
E3E3=IF(LEN(VLOOKUP($A$2,Needs,2,0))=0,"",VLOOKUP($A$2,Needs,2,FALSE))
F3F3=IF(LEN(VLOOKUP($A$2,Needs,3,0))=0,"",VLOOKUP($A$2,Needs,3,FALSE))
G3G3=IF(LEN(VLOOKUP($A$2,Needs,4,0))=0,"",VLOOKUP($A$2,Needs,4,FALSE))
H3H3=IF(LEN(VLOOKUP($A$2,Needs,5,0))=0,"",VLOOKUP($A$2,Needs,5,FALSE))
I3I3=IF(LEN(VLOOKUP($A$2,Needs,6,0))=0,"",VLOOKUP($A$2,Needs,6,FALSE))
E4E4=IF(LEN(VLOOKUP($A$2,Needs,7,0))=0,"",VLOOKUP($A$2,Needs,7,FALSE))
F4F4=IF(LEN(VLOOKUP($A$2,Needs,8,0))=0,"",VLOOKUP($A$2,Needs,8,FALSE))
G4G4=IF(LEN(VLOOKUP($A$2,Needs,9,0))=0,"",VLOOKUP($A$2,Needs,9,FALSE))
H4H4=IF(LEN(VLOOKUP($A$2,Needs,10,0))=0,"",VLOOKUP($A$2,Needs,10,FALSE))
I4I4=IF(LEN(VLOOKUP($A$2,Needs,11,0))=0,"",VLOOKUP($A$2,Needs,11,FALSE))
J4J4=IF(LEN(VLOOKUP($A$2,Needs,12,0))=0,"",VLOOKUP($A$2,Needs,12,FALSE))
E5E5=IF(LEN(VLOOKUP($A$2,Needs,13,0))=0,"",VLOOKUP($A$2,Needs,13,FALSE))
F5F5=IF(LEN(VLOOKUP($A$2,Needs,14,0))=0,"",VLOOKUP($A$2,Needs,14,FALSE))
G5G5=IF(LEN(VLOOKUP($A$2,Needs,15,0))=0,"",VLOOKUP($A$2,Needs,15,FALSE))
H5H5=IF(LEN(VLOOKUP($A$2,Needs,16,0))=0,"",VLOOKUP($A$2,Needs,16,FALSE))
I5I5=IF(LEN(VLOOKUP($A$2,Needs,17,0))=0,"",VLOOKUP($A$2,Needs,17,FALSE))
J5J5=IF(LEN(VLOOKUP($A$2,Needs,18,0))=0,"",VLOOKUP($A$2,Needs,18,FALSE))
E6E6=IF(LEN(VLOOKUP($A$2,Needs,19,0))=0,"",VLOOKUP($A$2,Needs,19,FALSE))
F6F6=IF(LEN(VLOOKUP($A$2,Needs,20,0))=0,"",VLOOKUP($A$2,Needs,20,FALSE))
B3B3=COUNTIF(L$13:L$44,$A2&"*")
B4B4=COUNTIF(Q$13:Q$44,$A2&"*")
B5B5=COUNTIF(V$13:V$44,$A2&"*")
B6B6=COUNTIF(AA$13:AA$44,$A2&"*")
B7B7=COUNTIF(AF$13:AF$44,$A2&"*")
B8B8=COUNTIF(AK$13:AK$44,$A2&"*")
B9B9=COUNTIF(AP$13:AP$44,$A2&"*")
B10B10=SUM(B3:B9)
S13:S44,N13:N44N13=IF(M13="","",VLOOKUP(M13,Prospects,5,FALSE))
P13P13=+K44+1
P14:P44P14=+P13+1
Named Ranges
NameRefers ToCells
Needs!_FilterDatabase=Prospects!$A$1:$M$1499N13:N44, S13:S44
Prospects!_FilterDatabase=Prospects!$A$1:$S$1499N13:N44, S13:S44
Needs=Needs!$A$3:$T$34J4:J5, E3:I5, E6:F6
ProspectNames=Prospects!$A$1:$A$1494N13:N44, S13:S44
Prospects=Prospects!$A$1:$W$1494N13:N44, S13:S44
Teams=Needs!$A$3:$A$34J4:J5, E3:I5, E6:F6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R13:S44Cell Valuebeginning with ""textNO
Q34Cell Valuebeginning with ""textNO
Q31:Q33Cell Valuebeginning with ""textNO
P13:Q30Cell Valuebeginning with ""textNO
T13:T44,Y13:Y44,AD13:AD44,AI13:AI44,AN13:AN44,AS13:AS44,V37:V38,AJ13:AK36,AO13:AP36,Q35:Q36,K13:O44,P31:P44,P37:Q44,V40:V44,AA37:AA44,AP42:AP44,AF37:AF44Cell Valuebeginning with ""textNO
Cells with Data Validation
CellAllowCriteria
A2List=Teams
M13:M44List=ProspectNames
R13:R44List=ProspectNames
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top