title: Excel Dynamic Dropdown go to Cell
body: <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
      Select Case Range("B1").Value
          Case Is = "G&amp;A Total"
          Application.Goto Range("G_A_Total"), Scroll:=True
  
          Case Is = "Accounting"
          Application.Goto Range("Accounting"), Scroll:=True
  
          Case Is = "Environmental"
          Application.Goto Range("Environmental"), Scroll:=True
  
          Case Is = "GSC"
          Application.Goto Range("GSC"), Scroll:=True
  
          Case Is = "Human Resources"
          Application.Goto Range("Human_Resources"), Scroll:=True
  
      End Select
  
  End Sub
  </code></pre>
  
  <p>I currently have this code which works with a data verification drop down. What I want to know is there anyway to make this dynamic so that anything I put in the list becomes what it is pulling from instead of having to manually enter each one.</p>
  
  <p>Current Edits below.</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
      Select Case Range("B1").Value
          Case Is = Range("A1")
          Application.Goto Range("G_A_Total"), Scroll:=True
  
          Case Is = Range("A2")
          Application.Goto Range("Accounting"), Scroll:=True
  
          Case Is = Range("A3")
          Application.Goto Range("Environmental"), Scroll:=True
  
          Case Is = Range("A4")
          Application.Goto Range("GSC"), Scroll:=True
  
          Case Is = Range("A5")
          Application.Goto Range("Human_Resources"), Scroll:=True
      End Select
  
  End Sub
  </code></pre>
  
  <p>I changed out the specific Case Is to be cell references which reference the cells selected by Data Validation instead of refering to each individual cell is there a way to range name them then refer to the range name?</p>
  
  <p>Maybe something such as this</p>
  
  <pre><code>    Select Case Range("B1").Value
              Case Is = Range("Departments")
              Application.Goto Range("DepartmentsGoTo"), Scroll:=True                
          End Select
  </code></pre>
  
  <p>Then have 2 ranged named column that I can update to avoid having to change the code often?</p>