Using the selection from a data validated list to jump to a named rqange

[StrugglingToCrackIt]

Hi all, I'm new here, but I hope that someone can help me please? Does any one know if it is possible to do the following?
Select an entry from a data validated list; that selection then triggers a jump to appropriate named range in the work book. eg. select "choice3" from list including "choice 1,choice2,choice3" etc.As soon as it is selected, immediately you jump to named range "choice3". I've got my ranges named and ready to go I am just looking for a neat way to trigger the jump to there.
Would be really grateful for help, either on my question or suitable alternative.Many thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use the Worksheet_Change event procedure. In which cell is your Data Validation? The alternative is Hyperlinks.

 

[StrugglingToCrackIt]

wow! cheers for the quick reply! My named ranges are in one of two worksheets, different to the worksheet where you choose. I am not familiar with the worksheet_event procedure but I looked at hyperlinks but couldn't work out how to get them to work using a variable field value.It is the contents of the cell which will define the range name - there are 208 ranges so I didn't want to create 208 single points to jump to the appropriate range. In the past I have put a macro button in and written the logic "if rangename is selected goto rangename else if.... end if" but with 208 clauses I wanted a neater solution. My cell is D6 on the first worksheet in the book.

 

[Andrew Poulsom]

Right click the sheet that contains your data validation and choose View Code. Paste this into the window on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$6" Then Application.Goto Target.Value, True
End Sub

Close the Visual Basic Editor and try it out by selecting from your dropdown.

You could also use a hyperlink like:

=HYPERLINK("#Choice1","Choice1")

 

[StrugglingToCrackIt]

Perfect! Thank you so much for your time and expertise!