NFL Draft Spreadsheet

[jj5484]

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.

 

[jj5484]

NFL2021DRAFT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	SELECT TEAM
2	Jacksonville			Team Needs
3	1st	2		Primary	QB	TE	OT
4	2nd	2		Secondary	CB	S	DE	WR
5	3rd	1		Ancillary	RB	IDL	WR	IOL
6	4th	2		Don't Need	LB
7	5th	2
8	6th	0
9	7th	2
10		11
11
12											Round One	Team	Selection	Position		Round Two	Team	Selection	Position
13											1	Jacksonville	Trevor Lawrence	QB		33	Jacksonville
14											2	NY Jets				34	NY Jets
15											3	Miami (from Houston)				35	Atlanta
16											4	Atlanta				36	Miami (from Houston)
17											5	Cincinnati				37	Philadelphia
18											6	Philadelphia				38	Cincinnati
19											7	Detroit				39	Carolina
20											8	Carolina				40	Denver
21											9	Denver				41	Detroit
22											10	NY Giants				42	NY Giants
23											11	Dallas				43	San Francisco
24											12	San Francisco				44	Dallas
25											13	LA Chargers				45	Jacksonville (from Minnesota)
26											14	Minnesota				46	New England
27											15	New England				47	LA Chargers
28											16	Arizona				48	Las Vegas
29											17	Las Vegas				49	Arizona
30											18	Miami				50	Miami
31											19	Washington				51	Washington
32											20	Chicago				52	Chicago
33											21	Indianapolis				53	Tennessee
34											22	Tennessee				54	Indianapolis
35											23	NY Jets (from Seattle)				55	Pittsburgh
36											24	Pittsburgh				56	Seattle
37											25	Jacksonville (from LA Rams)				57	LA Rams
38											26	Cleveland				58	Baltimore
39											27	Baltimore				59	Cleveland
40											28	New Orleans				60	New Orleans
41											29	Tampa Bay				61	Tampa Bay
42											30	Buffalo				62	Buffalo
43											31	Green Bay				63	Green Bay
44											32	Kansas City				64	Kansas City
Dashboard

Cell Formulas
Range	Formula
E3	E3	=IF(LEN(VLOOKUP($A$2,Needs,2,0))=0,"",VLOOKUP($A$2,Needs,2,FALSE))
F3	F3	=IF(LEN(VLOOKUP($A$2,Needs,3,0))=0,"",VLOOKUP($A$2,Needs,3,FALSE))
G3	G3	=IF(LEN(VLOOKUP($A$2,Needs,4,0))=0,"",VLOOKUP($A$2,Needs,4,FALSE))
H3	H3	=IF(LEN(VLOOKUP($A$2,Needs,5,0))=0,"",VLOOKUP($A$2,Needs,5,FALSE))
I3	I3	=IF(LEN(VLOOKUP($A$2,Needs,6,0))=0,"",VLOOKUP($A$2,Needs,6,FALSE))
E4	E4	=IF(LEN(VLOOKUP($A$2,Needs,7,0))=0,"",VLOOKUP($A$2,Needs,7,FALSE))
F4	F4	=IF(LEN(VLOOKUP($A$2,Needs,8,0))=0,"",VLOOKUP($A$2,Needs,8,FALSE))
G4	G4	=IF(LEN(VLOOKUP($A$2,Needs,9,0))=0,"",VLOOKUP($A$2,Needs,9,FALSE))
H4	H4	=IF(LEN(VLOOKUP($A$2,Needs,10,0))=0,"",VLOOKUP($A$2,Needs,10,FALSE))
I4	I4	=IF(LEN(VLOOKUP($A$2,Needs,11,0))=0,"",VLOOKUP($A$2,Needs,11,FALSE))
J4	J4	=IF(LEN(VLOOKUP($A$2,Needs,12,0))=0,"",VLOOKUP($A$2,Needs,12,FALSE))
E5	E5	=IF(LEN(VLOOKUP($A$2,Needs,13,0))=0,"",VLOOKUP($A$2,Needs,13,FALSE))
F5	F5	=IF(LEN(VLOOKUP($A$2,Needs,14,0))=0,"",VLOOKUP($A$2,Needs,14,FALSE))
G5	G5	=IF(LEN(VLOOKUP($A$2,Needs,15,0))=0,"",VLOOKUP($A$2,Needs,15,FALSE))
H5	H5	=IF(LEN(VLOOKUP($A$2,Needs,16,0))=0,"",VLOOKUP($A$2,Needs,16,FALSE))
I5	I5	=IF(LEN(VLOOKUP($A$2,Needs,17,0))=0,"",VLOOKUP($A$2,Needs,17,FALSE))
J5	J5	=IF(LEN(VLOOKUP($A$2,Needs,18,0))=0,"",VLOOKUP($A$2,Needs,18,FALSE))
E6	E6	=IF(LEN(VLOOKUP($A$2,Needs,19,0))=0,"",VLOOKUP($A$2,Needs,19,FALSE))
F6	F6	=IF(LEN(VLOOKUP($A$2,Needs,20,0))=0,"",VLOOKUP($A$2,Needs,20,FALSE))
B3	B3	=COUNTIF(L$13:L$44,$A2&"*")
B4	B4	=COUNTIF(Q$13:Q$44,$A2&"*")
B5	B5	=COUNTIF(V$13:V$44,$A2&"*")
B6	B6	=COUNTIF(AA$13:AA$44,$A2&"*")
B7	B7	=COUNTIF(AF$13:AF$44,$A2&"*")
B8	B8	=COUNTIF(AK$13:AK$44,$A2&"*")
B9	B9	=COUNTIF(AP$13:AP$44,$A2&"*")
B10	B10	=SUM(B3:B9)
S13:S44,N13:N44	N13	=IF(M13="","",VLOOKUP(M13,Prospects,5,FALSE))
P13	P13	=+K44+1
P14:P44	P14	=+P13+1

Named Ranges
Name	Refers To	Cells
Needs!_FilterDatabase	=Prospects!$A$1:$M$1499	N13:N44, S13:S44
Prospects!_FilterDatabase	=Prospects!$A$1:$S$1499	N13:N44, S13:S44
Needs	=Needs!$A$3:$T$34	J4:J5, E3:I5, E6:F6
ProspectNames	=Prospects!$A$1:$A$1494	N13:N44, S13:S44
Prospects	=Prospects!$A$1:$W$1494	N13:N44, S13:S44
Teams	=Needs!$A$3:$A$34	J4:J5, E3:I5, E6:F6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R13:S44	Cell Value	beginning with ""	text	NO
Q34	Cell Value	beginning with ""	text	NO
Q31:Q33	Cell Value	beginning with ""	text	NO
P13:Q30	Cell Value	beginning with ""	text	NO
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:AF44	Cell Value	beginning with ""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Teams
M13:M44	List	=ProspectNames
R13:R44	List	=ProspectNames