Excel Drop Down List - Jump to Corresponding Cell

[sparkytech]

I need a drop-down list that is populated from column "A", which only looks at the first row of each "section", and then allows the user to click the selected name in the drop-down box, and then the cursor will move to that cell containing the name selected. The way I currently do this, is a helper column "B" with a formula only in the rows with {Project Name}. For example, A12 contains {Project Name} and B12 contains the formula "=A12". Cells B13 through B19 are blank, and B20 contains "=A20", etc. This was my "easy" way to ignore everything in column A other than the project name and create a list for my drop-down. Is there a way to allow the user to select the project listed in the drop-down list and have the cursor jump to the cell with that project name? So, if the user clicks "Project A" in the drop-down, the cursor moves to cell A12? The project list will change often, so I need the links in the drop-down to happen automatically. I'm not opposed to VBA, but would prefer to avoid it if possible in order to future proof the sheet from company policies at a later date. Thanks in advance!

 

[kevin9999]

A couple of suggestions. Firstly, with regard to the data validation. I note that your profile says you have 365, therefore you should be able to access Unique/Filter functions. The data validation demonstrated below uses such a combination and has 2 advantages over your current method: 1) it will automatically adjust as you add more Projects to column A. I made the formula look down as far as row 100, but you can change that to however many rows that you think you’ll need. 2) your data validation list is free from blanks.

sparkytech.xlsm
	A	B	C
1
2
3	Select Project
4	Project C		Project A
5			Project B
6			Project C
7
8
9
10
11
12	Project A
13	ID1
14	Current Status:
15
16
17
18
19
20	Project B
21	ID1
22	Current Status:
23
24
25
26
27
28	Project C
29	ID1
30	Current Status:
31
32
33
34
35
Sheet1

Cell Formulas
Range	Formula
C4:C6	C4	=UNIQUE(FILTER(A12:A100,(A12:A100<>"")*(LEFT(A12:A100,7)="Project")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A4	List	=$C$4#

Secondly, there is no such function/formula that will move the focus of the active cell, you’ll need VBA for that. The following worksheet change event code will do what you want. If you’re not sure where to put it – right click on the tab name & select “View Code”. Paste the code in the window that appears to the right of the screen. As before you can change the row reference from 100 to whatever you think you’ll need. I’ve also indicated a line of code that scrolls the window down to the Project selected in the dropdown – you can delete that line if that’s not what you wanted.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim r As Range, s As String
        s = Range("A4")
        Set r = Range("A5:A100").Find(s, , xlFormulas, xlWhole, xlByRows, xlNext, False)
        If Not r Is Nothing Then
            r.Select
            ActiveWindow.ScrollRow = Selection.Row  '<~~ Remove this if you prefer
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[kevin9999]

Did either of the suggestions work out for you?