Hyperlink function fails when using variable row number

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

Balex,

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

HTH,
Bernie
MS Excel MVP


"Balex" <[email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> Balex,
>
> =HYPERLINK("#Notes!B" & ROW();"Click for Note")
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Balex" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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


"Balex" <[email protected]> wrote in message
news:[email protected]...
> 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 !

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 news:[email protected]...
> Balex,
>
> =HYPERLINK("#Notes!B" & ROW();"Click for Note")
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Balex" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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.