How to lookup and display info?

[Guest]

Am sure this is really easy but the logic of it is escaping me. I'm trying to
the following to make an interactive table of contents whith main chapter
headings, which when you click on one displays a set of hyperlinks to
relevant worksheets. For instance:

Worksheet 1, column A contains 10 text entries A1-A10

Each of the text entries in Worksheet 1 have 1-5 related text entries which
are displayed in Worksheet 2 column B.

What I would like to do, is that when in Worksheet 1, you click on any of
the A1-A10 entries, the "related" entries (displayed in Worksheet 2 column B)
are displayed in Worksheet 1, column B.

 

[ilia]

This is kind of inflexible, but should give you an idea of the logic.
I was trying to think of a FormulaArray method of doing this, but
figured since you're using VB anyway might as well do it all there.

Enter your table of content on Sheet 2 to repeat each value (A1:A10
from Sheet 1) and the corresponding values to match in column B on
Sheet 2.

Put this code in Sheet 1 code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Me.Range("B1:B5").ClearContents
Dim i As Integer, j As Integer
j = 1
For i = 1 To
Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
If Sheet2.Cells(i, 1).Value = Target.Value Then
Me.Cells(j, 2).Value = Sheet2.Cells(i, 2).Value
j = j + 1
End If
Next i
End If
End Sub