I need help create a searchable drop down without repetition

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
132
Office Version
  1. 365
Platform
  1. Windows

Attachments

  • GREEN HIGHLIGHT AREA IS A DROP DOWN LIST.jpg
    GREEN HIGHLIGHT AREA IS A DROP DOWN LIST.jpg
    149.9 KB · Views: 19
  • SEARCHABLE TEAMS.jpg
    SEARCHABLE TEAMS.jpg
    58.4 KB · Views: 17

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Do a quick search for DATA VALIDATION. Tons of examples on the internet. It really is easy and fast to put together.
 
Upvote 0
I need help
1. Wher All The Drop Down List Are Located At Only First Drop Down Is Working Need Help With F38 To F68 Get Searchable Drop Down List Of NFL Team
2.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
3.I have two name manager names

DOWNLOAD FILE HERE

SEARCHABLE DROP DOWN WITHOUT REPETITION.xlsb
ABCDEFGHIJKLMNOPQRS
35BYE WEEK WORKSHEET DATA VALIDATION SELECTIONC NO REPETITIONTYPE LETTER IN F37 TO F68 SEARCHABLE DROP DOWNSEARCHABLE DROP DOWN LIST1PACKERSTYPE LETTER OR LETTERS NEXT CELL BELOW
36TEAMS NAMESHELPER 1HELPER 2TEAMS REMANING049ERSPACKERS1I have two name manager names
3749ERS1149ERSPACKERS0BEARS 2
38BEARS22BEARS0BENGALS 3
39BENGALS33BENGALS0BILLS 4
40BILLS44BILLS0BRONCOS 5
41BRONCOS55BRONCOS0BROWNS 6
42BROWNS66BROWNS0BUCCANEERS 7
43BUCCANEERS77BUCCANEERS0CARDINALS 8
44CARDINALS88CARDINALS0CHARGERS 9
45CHARGERS99CHARGERS0CHIEFS 10
46CHIEFS1010CHIEFS0COLTS 11
47COLTS1111COLTS0COWBOYS 12Where Is All The Formulas Are Located At
48COWBOYS1212COWBOYS0DOLPHINS 13
49DOLPHINS1313DOLPHINS0EAGLES 14
50EAGLES1414EAGLES0FALCONS 15
51FALCONS1515FALCONS0GIANTS 16Wher 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
52GIANTS1616GIANTS0JAGUARS 17
53JAGUARS1717JAGUARS0JETS 18
54JETS1818JETS0LIONS 19I 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
55LIONS1919LIONS1PACKERS 20
56PACKERS2020PACKERS0PANTHERS 21
57PANTHERS2121PANTHERS0PATRIOTS 22
58PATRIOTS2222PATRIOTS0RAIDERS 23I have two name manager names
59RAIDERS2323RAIDERS0RAMS 24
60RAMS2424RAMS0RAVENS 25
61RAVENS2525RAVENS0REDSKINS 26
62REDSKINS2626REDSKINS0SAINTS 27
63SAINTS2727SAINTS0SEAHAWKS 28
64SEAHAWKS2828SEAHAWKS0STEELERS 29
65STEELERS2929STEELERS0TEXANS 30
66TEXANS3030TEXANS0TITANS 31
67TITANS3131TITANS0VIKINGS 32
68VIKINGS3232VIKINGS
TEAMS_INFOS
Cell Formulas
RangeFormula
M35M35=COUNTIF($K$36:$K$67,"?*")
O35O35=OFFSET($K$36,,,COUNTIF($K$36:$K$67,"?*"))
Q36Q36=CELL("contents")
B37:B68B37=IF(COUNTIFS('BYE WEEKS'!$A$1:$Q$23,$A37)>0,"",ROWS($B$37:B37))
C37:C68C37=IFERROR(SMALL($B$37:$B$68,ROWS($C$37:C37)),"")
D37:D68D37=IFERROR(INDEX(TEAMS_INFOS!$A$37:$A$68,$C37),"")
H36:H67H36=IF(ISNUMBER(SEARCH($F$37,A37)),MAX($H$35:H35,0)+1,0)
K36:K67K36=IFERROR(VLOOKUP(ROWS($K$35:K35),$H$36:$I$67,2,0),"")
Named Ranges
NameRefers ToCells
NFL_TEAMS_VALIDATION_LIST=OFFSET(TEAMS_INFOS!$K$36,,,COUNTIF(TEAMS_INFOS!$K$36:$K$67,"?*"))O35, K37:K67, M35
Cells with Data Validation
CellAllowCriteria
F37:F68List=NFL_TEAMS_VALIDATION_LIST
 
Upvote 0
The file you give me not I was looking for
Type any letter in cell F37 to F68 than drop down.
sample if put letter B bring up all teams that have letter B in their name should bring up teams
Bears
Bengals
Bills
Broncos
Browns
Buccaneers
Cowboys


SEARCHABLE TEAMS 2.jpg
 
Upvote 0
SEARCHABLE DROP DOWN WITHOUT REPETITION.xlsb
ABCDEFGHIJKLMNOPQR
35BYE WEEK WORKSHEET DATA VALIDATION SELECTIONC NO REPETITIONTYPE LETTER IN F37 TO F68 SEARCHABLE DROP DOWNSEARCHABLE DROP DOWN LIST7BEARSTYPE LETTER OR LETTERS NEXT CELL BELOW
36TEAMS NAMESHELPER 1HELPER 2TEAMS REMANING049ERSBEARS1BENGALS0
3749ERS1149ERSB1BEARSBENGALS2BILLS
38BEARS22BEARS2BENGALSBILLS3BRONCOS
39BENGALS33BENGALS3BILLSBRONCOS4BROWNS
40BILLS44BILLS4BRONCOSBROWNS5BUCCANEERS
41BRONCOS55BRONCOS5BROWNSBUCCANEERS6COWBOYS
42BROWNS66BROWNS6BUCCANEERSCOWBOYS7
43BUCCANEERS77BUCCANEERS0CARDINALS 8
44CARDINALS88CARDINALS0CHARGERS 9
45CHARGERS99CHARGERS0CHIEFS 10
46CHIEFS1010CHIEFS0COLTS 11
47COLTS1111COLTS7COWBOYS 12
48COWBOYS1212COWBOYS0DOLPHINS 13
49DOLPHINS1313DOLPHINS0EAGLES 14
50EAGLES1414EAGLES0FALCONS 15
51FALCONS1515FALCONS0GIANTS 16
52GIANTS1616GIANTS0JAGUARS 17
53JAGUARS1717JAGUARS0JETS 18
54JETS1818JETS0LIONS 19
55LIONS1919LIONS0PACKERS 20
56PACKERS2020PACKERS0PANTHERS 21
57PANTHERS2121PANTHERS0PATRIOTS 22
58PATRIOTS2222PATRIOTS0RAIDERS 23
59RAIDERS2323RAIDERS0RAMS 24
60RAMS2424RAMS0RAVENS 25
61RAVENS2525RAVENS0REDSKINS 26
62REDSKINS2626REDSKINS0SAINTS 27
63SAINTS2727SAINTS0SEAHAWKS 28
64SEAHAWKS2828SEAHAWKS0STEELERS 29
65STEELERS2929STEELERS0TEXANS 30
66TEXANS3030TEXANS0TITANS 31
67TITANS3131TITANS0VIKINGS 32
68VIKINGS3232VIKINGS
69
TEAMS_INFOS
Cell Formulas
RangeFormula
M35M35=COUNTIF($K$36:$K$67,"?*")
O35:O41O35=OFFSET($K$36,,,COUNTIF($K$36:$K$67,"?*"))
Q36Q36=CELL("contents")
B37:B68B37=IF(COUNTIFS('BYE WEEKS'!$A$1:$Q$23,$A37)>0,"",ROWS($B$37:B37))
C37:C68C37=IFERROR(SMALL($B$37:$B$68,ROWS($C$37:C37)),"")
D37:D68D37=IFERROR(INDEX(TEAMS_INFOS!$A$37:$A$68,$C37),"")
H36:H67H36=IF(ISNUMBER(SEARCH($F$37,A37)),MAX($H$35:H35,0)+1,0)
K36:K67K36=IFERROR(VLOOKUP(ROWS($K$35:K35),$H$36:$I$67,2,0),"")
Dynamic array formulas.
Named Ranges
NameRefers ToCells
NFL_TEAMS_VALIDATION_LIST=OFFSET(TEAMS_INFOS!$K$36,,,COUNTIF(TEAMS_INFOS!$K$36:$K$67,"?*"))O35, K37:K67, M35
Cells with Data Validation
CellAllowCriteria
F37:F69List=NFL_TEAMS_VALIDATION_LIST


DOWNLOAD UPDATE FILE


The orange selection is Column F did not working correcting.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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
Back
Top