software

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 quot;Project overviewquot; 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 quot;Notesquot;, 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(quot;#Notes!B21quot;;quot;Click for Notequot;) (if the proj/task was in the
overview in cell B21)
I wanted to have something like:
=HYPERLINK(quot;quot;quot;#Notes!Bquot; amp; ROW() amp; quot;quot;quot;quot;;quot;Click for Notequot;)
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
quot;Cannot open the specified file quot;.

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

Thanks
Balex

Balex,

=HYPERLINK(quot;#Notes!Bquot; amp; ROW();quot;Click for Notequot;)

HTH,
Bernie
MS Excel MVPquot;Balexquot; gt; wrote in message
...
gt; Hi
gt;
gt; I have a very irritating hyperlink problem.
gt;
gt; Let me first explain what I want, which is very simple:
gt; I have a first sheet called quot;Project overviewquot; with, in the rows, a list of
gt; projects/tasks. To some of these projs/tasks, I'd like to add a lengthy note
gt; of explanation. So I decided to have on the second sheet, called quot;Notesquot;, on
gt; the same row and column as the proj/task on the overview sheet, the
gt; corresponding note. To indicate the presence of a note, and to make
gt; navigation to the note and back, I added a hyperlink. So far so good.
gt;
gt; Then I decided to improve, because I wanted the hyperlink to have a variable
gt; row number, in fact the row number of the cell where the hyperlink is, so
gt; that I could copy the hyperlink cell to another task in an other row and it
gt; would reference that other row.
gt;
gt; So I wanted the hyperlink to have, instead of the working version:
gt; =HYPERLINK(quot;#Notes!B21quot;;quot;Click for Notequot;) (if the proj/task was in the
gt; overview in cell B21)
gt; I wanted to have something like:
gt; =HYPERLINK(quot;quot;quot;#Notes!Bquot; amp; ROW() amp; quot;quot;quot;quot;;quot;Click for Notequot;)
gt; i.e. replacing the fixed value 21 with the current row number.
gt;
gt; Well, NO WAY can I get this to work. I can fiddle with the apostrophes, the
gt; # sign and whatever as much as I want, it WON'T work ! All I get is the usual
gt; quot;Cannot open the specified file quot;.
gt;
gt; I'm pulling my hair out. Can somone please tell me what the right syntax is
gt; and why ?
gt;
gt; Thanks
gt; 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 su 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

quot;Bernie Deitrickquot; wrote:

gt; Balex,
gt;
gt; =HYPERLINK(quot;#Notes!Bquot; amp; ROW();quot;Click for Notequot;)
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;Balexquot; gt; wrote in message
gt; ...
gt; gt; Hi
gt; gt;
gt; gt; I have a very irritating hyperlink problem.
gt; gt;
gt; gt; Let me first explain what I want, which is very simple:
gt; gt; I have a first sheet called quot;Project overviewquot; with, in the rows, a list of
gt; gt; projects/tasks. To some of these projs/tasks, I'd like to add a lengthy note
gt; gt; of explanation. So I decided to have on the second sheet, called quot;Notesquot;, on
gt; gt; the same row and column as the proj/task on the overview sheet, the
gt; gt; corresponding note. To indicate the presence of a note, and to make
gt; gt; navigation to the note and back, I added a hyperlink. So far so good.
gt; gt;
gt; gt; Then I decided to improve, because I wanted the hyperlink to have a variable
gt; gt; row number, in fact the row number of the cell where the hyperlink is, so
gt; gt; that I could copy the hyperlink cell to another task in an other row and it
gt; gt; would reference that other row.
gt; gt;
gt; gt; So I wanted the hyperlink to have, instead of the working version:
gt; gt; =HYPERLINK(quot;#Notes!B21quot;;quot;Click for Notequot;) (if the proj/task was in the
gt; gt; overview in cell B21)
gt; gt; I wanted to have something like:
gt; gt; =HYPERLINK(quot;quot;quot;#Notes!Bquot; amp; ROW() amp; quot;quot;quot;quot;;quot;Click for Notequot;)
gt; gt; i.e. replacing the fixed value 21 with the current row number.
gt; gt;
gt; gt; Well, NO WAY can I get this to work. I can fiddle with the apostrophes, the
gt; gt; # sign and whatever as much as I want, it WON'T work ! All I get is the usual
gt; gt; quot;Cannot open the specified file quot;.
gt; gt;
gt; gt; I'm pulling my hair out. Can somone please tell me what the right syntax is
gt; gt; and why ?
gt; gt;
gt; gt; Thanks
gt; gt; Balex
gt;
gt;
gt;

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

HYPERLINK(quot;#'Notes Sheet'!Bquot; amp; ROW(),quot;Click for Notequot;)

HTH,
Bernie
MS Excel MVPquot;Balexquot; gt; wrote in message
...
gt; And I was convinced I had tried everything, and yet the simplest version is
gt; working !... I tried the single quotes before I had the # sign, then after
gt; that I always had loads of quotes, and it never worked...
gt;
gt; But one thing is for su the syntax rules are bl.. unclear. The # sign is
gt; not mentioned anywhere, the function wizard generates in the case of the
gt; HYPERLINK function a result which is NOT valid (because the quotes are
gt; missing as well as the # sign...), it is an absolute mess !
gt;
gt; Thanks a lot, anyway !
Hi quot;Balexquot;, (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 quot;Notesquot; worksheet
name might be extremely remote so might not be worth doing this compared to
Bernie's suggestion of: . .. HYPERLINK(quot;#Notes!Bquot; amp; ROW();quot;Click for Notequot;)

to hyperlink to the Note in Column B same row on Notes sheet
=HYPERLINK(quot;#quot;amp;CELL(quot;addressquot;,OFFSET(Notes!B1,ROW( )-1,0)), quot;Click for Notequot;)
or use the same row in the reference as your current sheetquot;

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(quot;#quot;amp;CELL(quot;addressquot;,OFFSET(Notes!B1,ROW( )-1,0)), OFFSET(Notes!A1,ROW()-1,0))

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

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

quot;Bernie Deitrickquot; lt;deitbe @ consumer dot orggt; wrote in message ...
gt; Balex,
gt;
gt; =HYPERLINK(quot;#Notes!Bquot; amp; ROW();quot;Click for Notequot;)
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;Balexquot; gt; wrote in message
gt; ...
gt; gt; Hi
gt; gt;
gt; gt; I have a very irritating hyperlink problem.
gt; gt;
gt; gt; Let me first explain what I want, which is very simple:
gt; gt; I have a first sheet called quot;Project overviewquot; with, in the rows, a list of
gt; gt; projects/tasks. To some of these projs/tasks, I'd like to add a lengthy note
gt; gt; of explanation. So I decided to have on the second sheet, called quot;Notesquot;, on
gt; gt; the same row and column as the proj/task on the overview sheet, the
gt; gt; corresponding note. To indicate the presence of a note, and to make
gt; gt; navigation to the note and back, I added a hyperlink. So far so good.
gt; gt;
gt; gt; Then I decided to improve, because I wanted the hyperlink to have a variable
gt; gt; row number, in fact the row number of the cell where the hyperlink is, so
gt; gt; that I could copy the hyperlink cell to another task in an other row and it
gt; gt; would reference that other row.
gt; gt;
gt; gt; So I wanted the hyperlink to have, instead of the working version:
gt; gt; =HYPERLINK(quot;#Notes!B21quot;;quot;Click for Notequot;) (if the proj/task was in the
gt; gt; overview in cell B21)
gt; gt; I wanted to have something like:
gt; gt; =HYPERLINK(quot;quot;quot;#Notes!Bquot; amp; ROW() amp; quot;quot;quot;quot;;quot;Click for Notequot;)
gt; gt; i.e. replacing the fixed value 21 with the current row number.
gt; gt;
gt; gt; Well, NO WAY can I get this to work. I can fiddle with the apostrophes, the
gt; gt; # sign and whatever as much as I want, it WON'T work ! All I get is the usual
gt; gt; quot;Cannot open the specified file quot;.
gt; gt;
gt; gt; I'm pulling my hair out. Can somone please tell me what the right syntax is
gt; gt; and why ?
gt; gt;
gt; gt; Thanks
gt; gt; Balex
gt;
gt;
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.