DOWNLOAD SEARCHABLE DROP DOWN WITHOUT REPETITION FILE HERE
I need help with making searchable & repetition
I need help with making searchable & repetition
SEARCHABLE DROP DOWN WITHOUT REPETITION.xlsb | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
35 | BYE WEEK WORKSHEET DATA VALIDATION SELECTIONC NO REPETITION | TYPE LETTER IN F37 TO F68 SEARCHABLE DROP DOWN | SEARCHABLE DROP DOWN LIST | 1 | PACKERS | TYPE LETTER OR LETTERS NEXT CELL BELOW | |||||||||||||||
36 | TEAMS NAMES | HELPER 1 | HELPER 2 | TEAMS REMANING | 0 | 49ERS | PACKERS | 1 | I have two name manager names | ||||||||||||
37 | 49ERS | 1 | 1 | 49ERS | PACKERS | 0 | BEARS | 2 | |||||||||||||
38 | BEARS | 2 | 2 | BEARS | 0 | BENGALS | 3 | ||||||||||||||
39 | BENGALS | 3 | 3 | BENGALS | 0 | BILLS | 4 | ||||||||||||||
40 | BILLS | 4 | 4 | BILLS | 0 | BRONCOS | 5 | ||||||||||||||
41 | BRONCOS | 5 | 5 | BRONCOS | 0 | BROWNS | 6 | ||||||||||||||
42 | BROWNS | 6 | 6 | BROWNS | 0 | BUCCANEERS | 7 | ||||||||||||||
43 | BUCCANEERS | 7 | 7 | BUCCANEERS | 0 | CARDINALS | 8 | ||||||||||||||
44 | CARDINALS | 8 | 8 | CARDINALS | 0 | CHARGERS | 9 | ||||||||||||||
45 | CHARGERS | 9 | 9 | CHARGERS | 0 | CHIEFS | 10 | ||||||||||||||
46 | CHIEFS | 10 | 10 | CHIEFS | 0 | COLTS | 11 | ||||||||||||||
47 | COLTS | 11 | 11 | COLTS | 0 | COWBOYS | 12 | Where Is All The Formulas Are Located At | |||||||||||||
48 | COWBOYS | 12 | 12 | COWBOYS | 0 | DOLPHINS | 13 | ||||||||||||||
49 | DOLPHINS | 13 | 13 | DOLPHINS | 0 | EAGLES | 14 | ||||||||||||||
50 | EAGLES | 14 | 14 | EAGLES | 0 | FALCONS | 15 | ||||||||||||||
51 | FALCONS | 15 | 15 | FALCONS | 0 | GIANTS | 16 | Wher All The Drop Down List Are Located At Only First Drop Down Is Working Need Help With F38 To F68 Get Searchable Drp Down List Of NFL Team | |||||||||||||
52 | GIANTS | 16 | 16 | GIANTS | 0 | JAGUARS | 17 | ||||||||||||||
53 | JAGUARS | 17 | 17 | JAGUARS | 0 | JETS | 18 | ||||||||||||||
54 | JETS | 18 | 18 | JETS | 0 | LIONS | 19 | I Also Need Help To Combine Both Together Somehow I Not Sure How Do That Put In Bye Week Worksheet after it working . I Want Put In BYE Weeks worksheet in All green Highlight area | |||||||||||||
55 | LIONS | 19 | 19 | LIONS | 1 | PACKERS | 20 | ||||||||||||||
56 | PACKERS | 20 | 20 | PACKERS | 0 | PANTHERS | 21 | ||||||||||||||
57 | PANTHERS | 21 | 21 | PANTHERS | 0 | PATRIOTS | 22 | ||||||||||||||
58 | PATRIOTS | 22 | 22 | PATRIOTS | 0 | RAIDERS | 23 | I have two name manager names | |||||||||||||
59 | RAIDERS | 23 | 23 | RAIDERS | 0 | RAMS | 24 | ||||||||||||||
60 | RAMS | 24 | 24 | RAMS | 0 | RAVENS | 25 | ||||||||||||||
61 | RAVENS | 25 | 25 | RAVENS | 0 | REDSKINS | 26 | ||||||||||||||
62 | REDSKINS | 26 | 26 | REDSKINS | 0 | SAINTS | 27 | ||||||||||||||
63 | SAINTS | 27 | 27 | SAINTS | 0 | SEAHAWKS | 28 | ||||||||||||||
64 | SEAHAWKS | 28 | 28 | SEAHAWKS | 0 | STEELERS | 29 | ||||||||||||||
65 | STEELERS | 29 | 29 | STEELERS | 0 | TEXANS | 30 | ||||||||||||||
66 | TEXANS | 30 | 30 | TEXANS | 0 | TITANS | 31 | ||||||||||||||
67 | TITANS | 31 | 31 | TITANS | 0 | VIKINGS | 32 | ||||||||||||||
68 | VIKINGS | 32 | 32 | VIKINGS | |||||||||||||||||
TEAMS_INFOS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M35 | M35 | =COUNTIF($K$36:$K$67,"?*") |
O35 | O35 | =OFFSET($K$36,,,COUNTIF($K$36:$K$67,"?*")) |
Q36 | Q36 | =CELL("contents") |
B37:B68 | B37 | =IF(COUNTIFS('BYE WEEKS'!$A$1:$Q$23,$A37)>0,"",ROWS($B$37:B37)) |
C37:C68 | C37 | =IFERROR(SMALL($B$37:$B$68,ROWS($C$37:C37)),"") |
D37:D68 | D37 | =IFERROR(INDEX(TEAMS_INFOS!$A$37:$A$68,$C37),"") |
H36:H67 | H36 | =IF(ISNUMBER(SEARCH($F$37,A37)),MAX($H$35:H35,0)+1,0) |
K36:K67 | K36 | =IFERROR(VLOOKUP(ROWS($K$35:K35),$H$36:$I$67,2,0),"") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
NFL_TEAMS_VALIDATION_LIST | =OFFSET(TEAMS_INFOS!$K$36,,,COUNTIF(TEAMS_INFOS!$K$36:$K$67,"?*")) | O35, K37:K67, M35 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F37:F68 | List | =NFL_TEAMS_VALIDATION_LIST |
SEARCHABLE DROP DOWN WITHOUT REPETITION.xlsb | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
35 | BYE WEEK WORKSHEET DATA VALIDATION SELECTIONC NO REPETITION | TYPE LETTER IN F37 TO F68 SEARCHABLE DROP DOWN | SEARCHABLE DROP DOWN LIST | 7 | BEARS | TYPE LETTER OR LETTERS NEXT CELL BELOW | ||||||||||||||
36 | TEAMS NAMES | HELPER 1 | HELPER 2 | TEAMS REMANING | 0 | 49ERS | BEARS | 1 | BENGALS | 0 | ||||||||||
37 | 49ERS | 1 | 1 | 49ERS | B | 1 | BEARS | BENGALS | 2 | BILLS | ||||||||||
38 | BEARS | 2 | 2 | BEARS | 2 | BENGALS | BILLS | 3 | BRONCOS | |||||||||||
39 | BENGALS | 3 | 3 | BENGALS | 3 | BILLS | BRONCOS | 4 | BROWNS | |||||||||||
40 | BILLS | 4 | 4 | BILLS | 4 | BRONCOS | BROWNS | 5 | BUCCANEERS | |||||||||||
41 | BRONCOS | 5 | 5 | BRONCOS | 5 | BROWNS | BUCCANEERS | 6 | COWBOYS | |||||||||||
42 | BROWNS | 6 | 6 | BROWNS | 6 | BUCCANEERS | COWBOYS | 7 | ||||||||||||
43 | BUCCANEERS | 7 | 7 | BUCCANEERS | 0 | CARDINALS | 8 | |||||||||||||
44 | CARDINALS | 8 | 8 | CARDINALS | 0 | CHARGERS | 9 | |||||||||||||
45 | CHARGERS | 9 | 9 | CHARGERS | 0 | CHIEFS | 10 | |||||||||||||
46 | CHIEFS | 10 | 10 | CHIEFS | 0 | COLTS | 11 | |||||||||||||
47 | COLTS | 11 | 11 | COLTS | 7 | COWBOYS | 12 | |||||||||||||
48 | COWBOYS | 12 | 12 | COWBOYS | 0 | DOLPHINS | 13 | |||||||||||||
49 | DOLPHINS | 13 | 13 | DOLPHINS | 0 | EAGLES | 14 | |||||||||||||
50 | EAGLES | 14 | 14 | EAGLES | 0 | FALCONS | 15 | |||||||||||||
51 | FALCONS | 15 | 15 | FALCONS | 0 | GIANTS | 16 | |||||||||||||
52 | GIANTS | 16 | 16 | GIANTS | 0 | JAGUARS | 17 | |||||||||||||
53 | JAGUARS | 17 | 17 | JAGUARS | 0 | JETS | 18 | |||||||||||||
54 | JETS | 18 | 18 | JETS | 0 | LIONS | 19 | |||||||||||||
55 | LIONS | 19 | 19 | LIONS | 0 | PACKERS | 20 | |||||||||||||
56 | PACKERS | 20 | 20 | PACKERS | 0 | PANTHERS | 21 | |||||||||||||
57 | PANTHERS | 21 | 21 | PANTHERS | 0 | PATRIOTS | 22 | |||||||||||||
58 | PATRIOTS | 22 | 22 | PATRIOTS | 0 | RAIDERS | 23 | |||||||||||||
59 | RAIDERS | 23 | 23 | RAIDERS | 0 | RAMS | 24 | |||||||||||||
60 | RAMS | 24 | 24 | RAMS | 0 | RAVENS | 25 | |||||||||||||
61 | RAVENS | 25 | 25 | RAVENS | 0 | REDSKINS | 26 | |||||||||||||
62 | REDSKINS | 26 | 26 | REDSKINS | 0 | SAINTS | 27 | |||||||||||||
63 | SAINTS | 27 | 27 | SAINTS | 0 | SEAHAWKS | 28 | |||||||||||||
64 | SEAHAWKS | 28 | 28 | SEAHAWKS | 0 | STEELERS | 29 | |||||||||||||
65 | STEELERS | 29 | 29 | STEELERS | 0 | TEXANS | 30 | |||||||||||||
66 | TEXANS | 30 | 30 | TEXANS | 0 | TITANS | 31 | |||||||||||||
67 | TITANS | 31 | 31 | TITANS | 0 | VIKINGS | 32 | |||||||||||||
68 | VIKINGS | 32 | 32 | VIKINGS | ||||||||||||||||
69 | ||||||||||||||||||||
TEAMS_INFOS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M35 | M35 | =COUNTIF($K$36:$K$67,"?*") |
O35:O41 | O35 | =OFFSET($K$36,,,COUNTIF($K$36:$K$67,"?*")) |
Q36 | Q36 | =CELL("contents") |
B37:B68 | B37 | =IF(COUNTIFS('BYE WEEKS'!$A$1:$Q$23,$A37)>0,"",ROWS($B$37:B37)) |
C37:C68 | C37 | =IFERROR(SMALL($B$37:$B$68,ROWS($C$37:C37)),"") |
D37:D68 | D37 | =IFERROR(INDEX(TEAMS_INFOS!$A$37:$A$68,$C37),"") |
H36:H67 | H36 | =IF(ISNUMBER(SEARCH($F$37,A37)),MAX($H$35:H35,0)+1,0) |
K36:K67 | K36 | =IFERROR(VLOOKUP(ROWS($K$35:K35),$H$36:$I$67,2,0),"") |
Dynamic array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
NFL_TEAMS_VALIDATION_LIST | =OFFSET(TEAMS_INFOS!$K$36,,,COUNTIF(TEAMS_INFOS!$K$36:$K$67,"?*")) | O35, K37:K67, M35 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F37:F69 | List | =NFL_TEAMS_VALIDATION_LIST |