Interactive Index

[Fergus]

I have a large amount of data on an Excel worksheet, 4000 rows. The data is split into sections, each section with a title in column D. I am trying, without success to compile an interactive index on a separate worksheet.

The Index will comprise only a list of the section titles and I want to be able to include a vba macro that will allow me to click on any particular title in the index and for that to take me straight to the matching section title in the larger worksheet of data.

If anyone can point me in the right direction as to how to achieve this I would be most grateful.

 

[RasGhul]

You can use a hyperlink formula to snap straight to range, if you don't need vba?

 

[Fergus]

Thank you RasGhul for that idea, which I have looked at but found problems. The length of the Data Worksheet is liable to increase over time so I cannot give a precise cell reference to a title. Using the MATCH Function I can determine the row number of the title on the data worksheet but cannot get that into the hyperlink reference cell, I tried using CONCATENATE and the INDIRECT functions but can't make that work.

I appreciate that I could assign a name to each title but as their are 121 titles I would prefer to avoid this route and would prefer a VBA using ******* or CLICK or something along those lines.

 

[RasGhul]

If your set on VBA thats fine, although I do have this working with table references so does expand with new data....

=HYPERLINK("#Sheet1!"&"$B$"&MATCH([@Food],Table1[Food],0)+1,"Back to Form")

 

[Fergus]

Thanks again RasGhul, I appreciate your help but I cannot see how to use that. Where do I put it?

In my Index Worksheet I have a list of all the titles which match all the titles in my data Worksheet. I can make each of the titles in the Index Worksheet into a hyperlink and in the dialogue box enter the address of the data Worksheet and cell reference and then it works. But if I add rows to the data Worksheet the cell reference in the hyperlink doesn't update.

 

[RasGhul]

If you can post some sample data I can look at it for you

 

[Fergus]

Hello RasGhul, It is very many years since I regularly used Mr. Excel and I cannot remember how to post data using the HTMLMaker. The two Worksheets I have are easy to describe. Worksheet named "Detail" contains 4,000 rows of data, this is split into categories of anything between 10 to 50 rows. Each category has a title. Titles and category item descriptions are all in Column D.

In Worksheet named "Index", in the same Workbook, I have in column D a list of just the titles, there are about 121 titles.

My aim is to be able to click or double click on a title in the Index Worksheet and jump directly to the same title in the Detail Worksheet.

I thought this was going to be easy using VBA but it is turning out not to be so!

 

[RasGhul]

Hi Fergus,

Here's a hyperlink mock up sheet with, you will need to convert your detail data into a table for that dynamic function.

https://www.dropbox.com/s/kq0fofvzdwzuryk/Hyperlink demo_jp.xlsx?dl=0

 

[Fergus]

Hi RasGhul, Thanks for that, I an still working on converting it for my problem. I am stuck on your use of # and @ as prefixes to sheet names. But for a while I must leave it as I am about to go on two weeks vacation. I will pick it up when I get back.

 

[Fergus]

Hi RasGhul, I got back from my vacation and got to work on my index. With the help of your example I eventually got it all working a treat, so many thanks for your help - much appreciated.