Dropdown list which navigates to another cell

P

Preacherman771

New Member
Joined
Jun 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I have a worksheet which comprises of a weekly schedule by week number for which team plays which team. I would like to create a dropdown list of each week number, i.e., Week-1, Week-2, etc. and when a given week is clicked on for it to automatically navigate to that week number on the worksheet. I am familiar with how to create a dropdown list but need help with how to complete the navigation task. I have attached a view of the complete first two weeks as an example of the table.

Wk#DateTimeNetworkAbrAAwayScoreAResAResHScoreHHomeAbrHCityStadium
Week-1Thursday, September 98:20 PMNBCDALDallas Cowboys29LW31Tampa Bay BuccaneersTBRaymond James Stadium, Tampa
Week-1Sunday, September 121:00 PMCBSJAXJacksonville Jaguars21LW37Houston TexansHOU NRG Stadium, Houston
Week-1Sunday, September 121:00 PMCBSLACLos Angeles Chargers20WL16Washington FootballWASFedEx Field, Landover
Week-1Sunday, September 121:00 PMFOXSEASeattle Seahawks28WL16Indianapolis ColtsINDLucas Oil Stadium, Indianapolis
Week-1Sunday, September 121:00 PMCBSNYJNew York Jets14LW19Carolina PanthersCARBank of America Stadium, Charlotte
Week-1Sunday, September 121:00 PMFOXMINMinnesota Vikings24LW27Cincinnati BengalsCINPaul Brown Stadium, Cincinnati
Week-1Sunday, September 121:00 PMCBSARIArizona Cardinals38WL13Tennessee TitansTENNissan Stadium, Nashville
Week-1Sunday, September 121:00 PMFOXSFSan Francisco 49ers41WL33Detroit LionsDETFord Field, Detroit
Week-1Sunday, September 121:00 PMCBSPITPittsburgh Steelers23WL16Buffalo BillsBUFBills Stadium, Orchard Park
Week-1Sunday, September 121:00 PMFOXPHIPhiladelphia Eagles32WL6Atlanta FalconsATLMercedes-Benz Stadium, Atlanta
Week-1Sunday, September 124:25 PMCBSCLECleveland Browns29LW33Kansas City ChiefsKCArrowhead Stadium, Kansas City
Week-1Sunday, September 124:25 PMFOXGBGreen Bay Packers3LW38New Orleans SaintsNOMercedes-Benz Superdome, New Orleans
Week-1Sunday, September 124:25 PMFOXDENDenver Broncos27WL13New York GiantsNYGMetLife Stadium, East Rutherford
Week-1Sunday, September 124:25 PMCBSMIAMiami Dolphins17WL16New England PatriotsNEGillette Stadium, Foxborough
Week-1Sunday, September 128:20 PMNBCCHIChicago Bears14LW34Los Angeles RamsLARSoFi Stadium, Los Angeles
Week-1Monday, September 138:15 PMESPNBALBaltimore Ravens27LW33Las Vegas RaidersLVAllegiant Stadium, Las Vegas
Week-1------------
Week-1------------
Week-1------------
Week-2Thursday, September 168:20 PMNFLNYGNew York Giants29LW30Washington FootballWASFedEx Field, Landover
Week-2Sunday, September 191:00 PMCBSNENew England Patriots25WL6New York JetsNYJMetLife Stadium, East Rutherford
Week-2Sunday, September 191:00 PMCBSDENDenver Broncos23WL13Jacksonville JaguarsJAXTIAA Bank Field, Jacksonville
Week-2Sunday, September 191:00 PMFOXBUFBuffalo Bills35WL0Miami DolphinsMIAHard Rock Stadium, Miami Gardens
Week-2Sunday, September 191:00 PMFOXSFSan Francisco 49ers17WL11Philadelphia EaglesPHILincoln Financial Field, Philadelphia
Week-2Sunday, September 191:00 PMFOXLARLos Angeles Rams27WL24Indianapolis ColtsINDLucas Oil Stadium, Indianapolis
Week-2Sunday, September 191:00 PMCBSLVLas Vegas Raiders26WL17Pittsburgh SteelersPITHeinz Field, Pittsburgh
Week-2Sunday, September 191:00 PMFOXCINCincinnati Bengals17LW20Chicago BearsCHISoldier Field, Chicago
Week-2Sunday, September 191:00 PMCBSHOUHouston Texans21LW31Cleveland BrownsCLEFirstEnergy Stadium, Cleveland
Week-2Sunday, September 191:00 PMFOXNONew Orleans Saints7LW26Carolina PanthersCARBank of America Stadium, Charlotte
Week-2Sunday, September 194:05 PMFOXMINMinnesota Vikings33LW34Arizona CardinalsARIState Farm Stadium, Glendale
Week-2Sunday, September 194:05 PMFOXATLAtlanta Falcons25LW48Tampa Bay BuccaneersTBRaymond James Stadium, Tampa
Week-2Sunday, September 194:25 PMCBSTENTennessee Titans33WL30Seattle SeahawksSEACenturyLink Field, Seattle
Week-2Sunday, September 194:25 PMCBSDALDallas Cowboys20WL17Los Angeles ChargersLACSoFi Stadium, Los Angeles
Week-2Sunday, September 198:20 PMNBCKCKansas City Chiefs35LW36Baltimore RavensBALM&T Bank Stadium, Baltimore
Week-2Monday, September 208:15 PMESPNDETDetroit Lions17LW35Green Bay PackersGBLambeau Field, Green Bay
Week-2Sunday, September 19---------
Week-2Sunday, September 19---------
Week-2Sunday, September 19---------
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Hi Preacherman771,

You could build a HYPERLINK

Preacherman771-2.xlsx
ABCDEFG
1WeeksWeek
2Week-1Week-2JUMP
3Week-2
4Week-3
5Week-4
6Week-5Wk#Date
7Week-6Week-1Thursday, September 9, 2021
8Week-7Week-1Sunday, September 12, 2021
9Week-8Week-1Sunday, September 12, 2021
10Week-9Week-1Sunday, September 12, 2021
11Week-10Week-1Sunday, September 12, 2021
12Week-11Week-1Sunday, September 12, 2021
13Week-12Week-1Sunday, September 12, 2021
14Week-13Week-1Sunday, September 12, 2021
15Week-14Week-1Sunday, September 12, 2021
16Week-15Week-1Sunday, September 12, 2021
17Week-16Week-1Sunday, September 12, 2021
18Week-17Week-1Sunday, September 12, 2021
19Week-18Week-1Sunday, September 12, 2021
20Week-19Week-1Sunday, September 12, 2021
21Week-20Week-1Sunday, September 12, 2021
22Week-21Week-1Monday, September 13, 2021
23Week-22Week-1---
24Week-23Week-1---
25Week-24Week-1---
26Week-25Week-2Thursday, September 16, 2021
27Week-26Week-2Sunday, September 19, 2021
28Week-27Week-2Sunday, September 19, 2021
29Week-28Week-2Sunday, September 19, 2021
30Week-29Week-2Sunday, September 19, 2021
31Week-30Week-2Sunday, September 19, 2021
32Week-31Week-2Sunday, September 19, 2021
33Week-32Week-2Sunday, September 19, 2021
34Week-33Week-2Sunday, September 19, 2021
Sheet2
Cell Formulas
RangeFormula
G2G2=HYPERLINK("#F"&MATCH($F$2,$F$7:$F$9999,0)+ROW($F$6),"JUMP")
G8G8=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G9G9=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G10G10=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G11G11=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G12G12=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G13G13=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G14G14=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G15G15=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G16G16=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G17G17=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G18G18=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G19G19=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G20G20=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G21G21=DATE(YEAR(G7),MONTH(G7),DAY(G7)+3)
G22G22=DATE(YEAR(G7),MONTH(G7),DAY(G7)+4)
A2:A34A2="Week-"&ROW()-1
G26G26=DATE(YEAR(G7),MONTH(G7),DAY(G7)+7)
G27G27=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
G28G28=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
G29G29=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
G30G30=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
G31G31=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
G32G32=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
G33G33=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
G34G34=DATE(YEAR($G26),MONTH($G26),DAY($G26)+3)
Cells with Data Validation
CellAllowCriteria
F2List=$A$2:$A$54
 
Upvote 0
If you were looking for a VBA Worksheet Change Event solution, then the following may give you what you want. Copy the code to the sheet code area of whatever sheet you are using. Change dropdown cell & column with weeks listed to suit.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C1"), Target) Is Nothing Then   '<~~ Change dropdown cell (C1) to suit
        Dim JumpTo As Long, WeekNo As String
        WeekNo = Target.Value
        JumpTo = Application.Match(WeekNo, Me.Columns(1), 0)    '<~~ assumes column A (1) change to suit
        Application.Goto Me.Cells(JumpTo, 1), scroll:=1
    End If
End Sub
 
Upvote 0
Thank you Toadstool.

I tried your suggestion by setting up the dropdown list in one cell and the "jump" in the cell to the right. After a couple adjustments got it to work perfectly. Was wondering if there was someway of combining the two cells into one or whatever so that when I click on the arrow for the dropdown and then click on say "Week - 11" it would immediately navigate to the cell which "jump" would do so, T226.

NFL 2021-2022 Standings (Template).xlsm
EFGHIJKLMN
2NFL 2021/22
3Weekly Game ScheduleWeek-11JUMP
Schedule
Cell Formulas
RangeFormula
F2F2=Sheet2!$I$8
K3K3=HYPERLINK("#T"&MATCH($J$3,$F$7:$F$9999,0)+ROW($T$25),"JUMP")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
J3List=Calc_Team!$B$6:$B$23
 
Upvote 0
Sounds like a good suggestion. My understanding of VBA codes is very limited. Would there be anyway of incorporating =HYPERLINK("#T"&MATCH($J$3,$F$7:$F$9999,0)+ROW($T$25),"JUMP")?

Otherwise, I would need some help getting this VBA to work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C1"), Target) Is Nothing Then '<~~ Change dropdown cell (C1) to suit
Dim JumpTo As Long, WeekNo As String
WeekNo = Target.Value
JumpTo = Application.Match(WeekNo, Me.Columns(1), 0) '<~~ assumes column A (1) change to suit
Application.Goto Me.Cells(JumpTo, 1), scroll:=1
End If
End Sub

With my dropdown list located in J3, would I change any references to "C1" to "J3"? If there are any, what other changes would I need to make?
 
Upvote 0
Preacherman771 said:
Sounds like a good suggestion. My understanding of VBA codes is very limited. Would there be anyway of incorporating =HYPERLINK("#T"&MATCH($J$3,$F$7:$F$9999,0)+ROW($T$25),"JUMP")?

Otherwise, I would need some help getting this VBA to work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C1"), Target) Is Nothing Then '<~~ Change dropdown cell (C1) to suit
Dim JumpTo As Long, WeekNo As String
WeekNo = Target.Value
JumpTo = Application.Match(WeekNo, Me.Columns(1), 0) '<~~ assumes column A (1) change to suit
Application.Goto Me.Cells(JumpTo, 1), scroll:=1
End If
End Sub

With my dropdown list located in J3, would I change any references to "C1" to "J3"? If there are any, what other changes would I need to make?
Click to expand...

OK, follow these steps:
1. Right-click on the sheet tab and select "View Code".
2. The VBA code area for that sheet should appear
3. The large area to the right of screen is where you copy the code (below) to (your assumption is correct - I've changed the reference from C1 to J3)
4. Save the file as a macro-enabled or binary excel file & test

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("J3"), Target) Is Nothing Then
        Dim JumpTo As Long, WeekNo As String
        WeekNo = Target.Value
        JumpTo = Application.Match(WeekNo, Me.Columns(1), 0)    '<~~ assumes column A (1) change to suit
        Application.Goto Me.Cells(JumpTo, 1), scroll:=1
    End If
End Sub

The code assumes that Column A contains the Week-# that the code will attempt to Match.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Question Question
Consecutive Superbowls
Replies
7
Views
438
Fluff
Fluff
JenniferMurphy
  • Question Question
Are these macros the best way to make custom sorts easily accessible?
Replies
2
Views
116
JenniferMurphy
JenniferMurphy
S
  • Question Question
Returning Consecutive Super Bowl Wins
Replies
6
Views
393
Peter_SSs
Peter_SSs
D
  • Question Question
Duplicate cells not formatting problem.
Replies
6
Views
278
etaf
etaf
A
  • Solved
Last 6 Events Based on Date
Replies
9
Views
542
Fluff
Fluff

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

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