Using data validation list to jump to different cell

adamjon92

New Member
Joined
Dec 8, 2015
Messages
21
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

Code:
'// 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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,311
Messages
5,600,886
Members
414,414
Latest member
neil_c

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
Top