Drop down list to jump to specific cell

[nikwak1]

I have a sheet with 7000 lines of data. Out of those 7000 lines there are about 90 cells that I need to jump to quickly, rather than scrolling. I'd love to be able to do it with a drop down list, where those specific 90 cells are listed and you can choose the cell name and the page jumps to the cell chosen. (I would have the drop down list in a cell above the frozen part of the page so it is always visible). I cannot use a table as it is not that sort of a sheet. Have a I described this enough to make sense. Please tell me if not and I will try and make it a better description

 

[dave3009]

Hi

If the cells are in a fixed position, why not define them in the name manager - Formulas >> Name Manager - then you can just choose them from the Name Box, or by F5

 

[myall_blues]

There's no direct way to create a dropdown list containing active hyperlinks, but you could do it in two columns using a formula.

In the example shown below, the dropdown list in B2 uses E2:E5 as its validation list. When you choose something from the dropdown list, the HYPERLINK formula in C2 converts it to a clickable hyperlink to go to your required cell. Obviously the validation list can go anywhere - on another sheet for example.

Book1
	A	B	C	D	E	F	G	H
1		Dropdown list			Target cells (List for dropdown)
2		A30	A30		A20
3					A30
4					A40
5					A50
6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=HYPERLINK("#'Sheet1'!" &B2,B2)

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=$E$2:$E$5

 

[kevin9999]

If you didn't want to click on a Hyperlink after you performed your dropdown action, you could use a Worksheet change event like the following. Assumes the dropdown cell is in A1 (change to suit) - right click the sheet tab, select View Code, and copy the code into the window that appears on the right of screen. You'll need to save the file as either macro-enabled or Binary format.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Application.Goto Range(Target), Scroll:=True
End Sub

 

[nikwak1]

thank you for all replies - been very helpful