Using data validation list to jump to different cell

[adamjon92]

Hello!
I have an excel sheet with 25 different pie charts that take up 250 rows (10 rows per chart). I used data validation to make a list at the top where you can choose which chart to jump to so you don't have to scroll down manually every time. Unfortunately, I don't know how to do this. For example, the chart for "Denver" is in cell B104 and I want the user to be able to select "Denver" from the list in cell D1 and have it automatically jump to B104. Is there any way to do this?

Thanks!

Adam

 

[L. Howard]

Try either of these, with the assumption the name of the city for the chart will be in column B, say B104 as you say in your post, AND the name in D1 is EXACTLY the same as the name in column B. you cannot have Denver, CO in D1 drop down and Denver in B104.

Change the sheet name in the code to match your sheet name.

You may want to offset 5 rows to better center the chart...??? if so you can use 'SearchRng.Offset(5, 0).Select which is commented out in both macros. You would un-comment it and add a comment to omit the other .Select line to disable it.


Private Sub Worksheet_Change(ByVal Target As Range) copied to the sheet module with the charts will take you to the chart selected in D1 automatically. Just select a city in D1.


Sub adamjon92_Chart() copied to a Standard Module, will need to be run from a button or other means after each selection in D1 to take you to the desired chart.

Howard

'// copy to the sheet module with the charts

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "D1" Or Target.Count > 1 Then Exit Sub
Dim ctyChart As String
ctyChart = Range("D1")
Dim SearchRng As Range

Set SearchRng = Sheets("[COLOR=#FF0000]Sheet1[/COLOR]").Range("B:B").Find(What:=ctyChart, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not SearchRng Is Nothing Then
    SearchRng.Select
    'SearchRng.Offset(5, 0).Select
 Else
   MsgBox "No chart found for - " & ctyChart
End If

End Sub



'// copy to a Standard Module

Sub adamjon92_Chart()
Dim ctyChart As String
ctyChart = Range("D1")
Dim SearchRng As Range
Set SearchRng = Sheets("[COLOR=#FF0000]Sheet1[/COLOR]").Range("B:B").Find(What:=ctyChart, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not SearchRng Is Nothing Then
    SearchRng.Select
    'SearchRng.Offset(5, 0).Select
 Else
   MsgBox "No chart found for - " & ctyChart
End If

End Sub