Hi "Balex", (and Bernie)

Possibly one might want to take the sheetname of Notes out of the double quotes, so that
changing the sheetname will be automatically reflected in the formula. Though it does
make the formula more complicated and the chances of changing the "Notes" worksheet
name might be extremely remote so might not be worth doing this compared to
Bernie's suggestion of: . .. HYPERLINK("#Notes!B" & ROW();"Click for Note")

to hyperlink to the Note in Column B same row on Notes sheet
=HYPERLINK("#"&CELL("address",OFFSET(Notes!B1,ROW( )-1,0)), "Click for Note")
or use the same row in the reference as your current sheet"

to hyperlink to the Note in Column B same row on Notes sheet,
but display short description in Column A of the Notes sheet.
=HYPERLINK("#"&CELL("address",OFFSET(Notes!B1,ROW( )-1,0)), OFFSET(Notes!A1,ROW()-1,0))

If everything in a column is going to say "Click for Note" you might
consider a shorter version "[Note]" or "[ref]"

More information in:
http://www.mvps.org/dmcritchie/excel...#hyperlink_ref
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ...
Balex,

=HYPERLINK("#Notes!B" & ROW();"Click for Note")

HTH,
Bernie
MS Excel MVP


"Balex" wrote in message
...
Hi

I have a very irritating hyperlink problem.

Let me first explain what I want, which is very simple:
I have a first sheet called "Project overview" with, in the rows, a list of
projects/tasks. To some of these projs/tasks, I'd like to add a lengthy note
of explanation. So I decided to have on the second sheet, called "Notes", on
the same row and column as the proj/task on the overview sheet, the
corresponding note. To indicate the presence of a note, and to make
navigation to the note and back, I added a hyperlink. So far so good.

Then I decided to improve, because I wanted the hyperlink to have a variable
row number, in fact the row number of the cell where the hyperlink is, so
that I could copy the hyperlink cell to another task in an other row and it
would reference that other row.

So I wanted the hyperlink to have, instead of the working version:
=HYPERLINK("#Notes!B21";"Click for Note") (if the proj/task was in the
overview in cell B21)
I wanted to have something like:
=HYPERLINK("""#Notes!B" & ROW() & """";"Click for Note")
i.e. replacing the fixed value 21 with the current row number.

Well, NO WAY can I get this to work. I can fiddle with the apostrophes, the
# sign and whatever as much as I want, it WON'T work ! All I get is the usual
"Cannot open the specified file ".

I'm pulling my hair out. Can somone please tell me what the right syntax is
and why ?

Thanks
Balex