Hyperlink function fails when using variable row number

[Guest]

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

 

[Bernie Deitrick]

Balex,

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

And I was convinced I had tried everything, and yet the simplest version is
working !... I tried the single quotes before I had the # sign, then after
that I always had loads of quotes, and it never worked...

But one thing is for sure: the syntax rules are bl.. unclear. The # sign is
not mentioned anywhere, the function wizard generates in the case of the
HYPERLINK function a result which is NOT valid (because the quotes are
missing as well as the # sign...), it is an absolute mess !

Thanks a lot, anyway !
Balex

 

[Bernie Deitrick]

Just as a side note, single quotes would be needed if the linked-to sheet name has spaces in it:

HYPERLINK("#'Notes Sheet'!B" & ROW(),"Click for Note")

HTH,
Bernie
MS Excel MVP

 

[David McRitchie]

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/sheets.htm#hyperlink_ref

 

[David McRitchie]

It mitght be noted that if you just have one word for the sheet name and
surround it by single quotes like you would if you had two or more words
that the single quotes will disappear automatically. Which may account
for some of the confusion. experienced.

The more complicated formula does *exactly* the same, it is just coded
to remove place the sheetname and cell outside of the double quotes.