Separate and Hyperlink to proper position of data

[ertan]

Need help for daily rutin task

On my workbook Sheet2 contains variable number of rows and columns of data.

Data titles on column A and data itself expands to other columns.

What I would like to achive is:


Create a index page for those titles (first occurance) with hyperlinks which will take me to beginning of the related data.
(Or Perhaps List those Titles at the top of the shhet starting from a2 - b2 -c2 etc.. And hyperlink them to proper locations)




Problems:

Title's and data constanly changes.
There are duplicate repeating titles ..(which they are should be there) because they are continuation of data
There are blank rows between titles.
Most of the titles are not suitable name creation.(have space , underscore etc)

Any help will save a lot of time doing those rutines

Regards

PS Attached sample data .

[BDavidson]

How about this?


Sub TitleList()
'Written by Barrie Davidson
Dim Title As String
Dim DataSheet, TitleSheet As Worksheet
Dim TitleRange, c As Range
Dim list As String
Application.ScreenUpdating = False
Set DataSheet = ActiveSheet
Sheets.Add before:=DataSheet
Set TitleSheet = ActiveSheet
Set TitleRange = Range("A1")
For Each c In DataSheet.Range("A1:A" & DataSheet.Range("A65536").End(xlUp).Row)
If c.Offset(, 1).Value = "" Then
If c.Value <> Title And c.Value <> "" Then
Title = c.Value
TitleRange.Value = Title
TitleSheet.Hyperlinks.Add Anchor:=TitleRange, Address:="", _
SubAddress:="'" & c.Worksheet.Name & "'!" & c.Address
Set TitleRange = TitleRange.Offset(1)
End If
End If
Next c
Application.ScreenUpdating = True
End Sub

[ertan]

Barry,

I test the code your provide with my real life data and worked like charm.

I dont know how to thank you , this piece of code saved me many hours of
work..


Regards

Ertan...