Bookmark System To Navigate Through Large List Style Spreadsheets

[miketurn]

I thought I had found a solution to this in the past but can't seem to figure this out.
I am looking for a way to navigate through large spreadsheets where the text will be in Column A, so you can almost think of them as lists, where every now and then you have "headers" that I want to navigate to.
I guess I am looking for some kind of bookmarking system that will allow me to add these "headers" to a list that I can click on these links and Excel will navigate to the specific header on this list, placing (scrolling) that header location to the top of my screen.

EXAMPLE
This is a very simplistic representation, below each header will most likely be notes rather than single items in a lists, but this is just a simple example.
I want to be able to add the header titled "FRUITS" to a bookmark list so when I click on it, Excel will scroll down to row 200, placing it at the top of my screen. The tough part is that the header titled "FRUITS" will not always be located in row 200, as new rows get inserted/deleted above, its location will change.

|_____A_____|
1 CARS (HEADER)
2 camry
3 accord
4 mustang
5 corolla
6
7
.....
200 FRUITS (HEADER)
201 apple
202 banana
203 orange
204 grape

I could think of a couple of ways I guess this could be implemented....
(Currently using Excel 2003)

METHOD 1
(This method would be more desired if it exists in Excel)
Some kind of sidebar / dialog box that you can create a list of linked headers to that allows you to just click on the header link to navigate to it.
Thought I had found something like this in the past.
Does anyone know if something like this exists?

METHOD 2
(Not as desired, but would use if no other option)
I tried making a kind of glossary list of all the headers in one of my lists and put it at the top.
I then created hyperlinks to each of these items from the glossary list to the actual headers locations.
This worked but two things don't work well, which maybe they can be fixed?
1.) Hyperlinks have to be created with row number which as mentioned above, as rows get added / deleted the header locations row numbers change.
2.) When it navigates to them, it places the header name at the bottom of the screen as opposed to scrolling it to the top of the screen.

All this typing!, anyways, does anyone know of a simple method of achieving what I am looking for.

Thank You to anyone who reads this and offers any help.

 

[StephenCrump]

Perhaps something like this ...

Data Validation cell "MyList" at the top of the sheet, inside frozen pane, containing your list of headers in Column A.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
    Set rng = Range("MyList")
    
    If Not Intersect(Target, rng) Is Nothing Then _
        ActiveWindow.ScrollRow = Application.Match(rng.Value, Range("A:A"), 0)
    
End Sub

 

[miketurn]

@StephenCrump
Thank You very much for your response, I have been trying to test out the macro you have provided, but I am having a little trouble understanding how it works.
If you have a little time, can you explain a little more how to use the macro you provided.
I am not too familiar with Data Validation cells and I am not sure what you mean by "frozen pane"
Is this something I would have to assign to a button or would this apply directly to the glossary list of headers that I would have to create in each sheet?
Thank You again for providing this macro, and for any extra help offered.

@ anyone else
If anyone else has any ideas to share please to so.

Odd that it seems like something Excel would have within its programming, for when people have large spreadsheets to navigate through.
Was anything like this ever added to newer versions of Excel beyond 2003?
I can't be the only one to have to navigate through large spreadsheets, and really my spreadsheets with just data in Column A, I don't imagine are as large as others users spreadsheets with multiple columns full of data.

Anyways, Thank You for any help provided

 

[StephenCrump]

Here's a workbook implementing the approach: https://app.box.com/s/7527fd9kydlh6an9chd4gaxf32j4sx1m

 

[miketurn]

@stephen
Thank You for your response and your example, unfortunately I am using 2003 so I was not able to open up your example.
Thank You anyways.

 

[StephenCrump]

Here's an Excel 2003 version: https://app.box.com/s/xu341zofu3tv8rruh1nrqxwqhp9jp85q

 

[miketurn]

@stephen
I apologize for not getting back to as promptly as I would have liked, some family emergencies came up.
Thank you very much for providing me with these examples and your continued help. I tested the 2003 version example and it worked.
Overall this is very clever, It covers almost all the items of request.
The only thing that is slightly not as smooth, is the fact that it has to use a pulldown menu, which I believe is what is making the entire thing work
Wondering if there is any way to be able to apply this same method in a glossary list form, where all header topics can be seen at once for quicker access.

Don't think this helps any, but I typically also will bold the text of each header and also give it a background fill color.

I was hoping for other members to chime in with some ideas just to see what others could come up with in comparison, just to bounce some ideas off each other, but I guess this isn't an easy or desired tool to create. Thank You again for your specific version of the tool I was looking for, again this is very impressive.

If anyone else has anything to mention, please do so.