Excel formula using bookmark name: create click-link

[4mula freak]

I already used Insert, Name, Create, Left column, to automatically assign a
named cell location (i.e., bookmark) at first line only of each author's
books in a tall alphabetic list of many authors and many books per author.
This will be my GoTo spot. Now a separate worksheet acts like a master index
-- it lists just authors names, which I want to make into clickable links,
each going to first line of its own author's portion of the book list. I do
NOT want to manually use the link wizard (Insert, Hyperlink) one at a time on
each author's name, as there are many dozens of names. How can I use a
formula to do this? I tried several ways with HYPERLINK, but so far could
not make it work.

 

[Gary''s Student]

Let's say Ayn Rand's books start at A1000 and we have assigned the Name
Rand_Ayn to that cell.

In column B we have a simple list of the authors, so in B1 we have Rand_Ayn.
In C1 enter:

=HYPERLINK("#" & B1,B1)

This will produce a nice "clickable" link to cell A1000. This is a good
example of setting up an internal hyperlink to a Named Range.