Hyperlinks

  • Thread starter Thread starter Shaz
  • Start date Start date
S

Shaz

Hi

I have managed to create hyperlinks,what I am doing is
trying to make an "address book" type thing, where at the
top of the page you have the alphabet, you click on "p"
and it takes you to the "p" section on the same page,
which works fine, except when I add lines, the hyperlink
of the "p" section doesn't move down, it stays where it
is. How can I make it move down and stay linked to the
top alphabet, when I add lines??

Is there a way where you can add data that cannot be
edited?? Such as "Surname", "Name", "Address" and stuff
like that. So if I send the document to someone, they
can't delete anything, but they can add things?? If you
know what I mean??

Please help if you can.

Regards
Shaz
 
I think I'd a nice header right before the beginning of the actual data for each
letter:

A
Anderson
Andrews

B
Baker
Boone

Then I'd define 26 range names that point at those headers:
Start_A, Start_B, ..., Start_Z

And I'd link to the header ranges:
=HYPERLINK(Start_A,"Jump to A")
=HYPERLINK(Start_B,"Jump to B")
and so forth.

Then if I had to insert something before the first entry, it wouldn't make a
difference. I'd still go to the header.
 
Try this...

Select the cell you wish to "protect".
Go to the Menu bar and select Data...go to Validation
under the drop down menu. This should bring up the
Validation Dialog box which will need info from you. It
looks like you want to save your titles over each column
of cells. Go to the Settings look for Allow and use the
drop down to select Custom. Just below that is the Formula
and here enter the "text" that is in the cell you wish to
protect. It has to be exactly the same as the cell...case
sensitive. Next go to the Input tab of this same dialog
box and for a Title just type something relative like the
cell text Ex: Surname and for an input message type
something small like Saved or Protected. If you notice the
check box here allows you to either show or hide this
info...this is up to you...try both to see the effect.
Finally go to the tab Error Alert and choose STOP for your
Style drop down. Next for a Title enter the cell text
again such as Surname. Last for an error message enter
something like...Protected Cell. Hit "OK" to save this
validation. After this is done you can attempt to change
it. What should occur is if you try to change it you will
get a "Retry" button and a "Cancel" button...until you hit
the "Cancel" button it will continue to give the error
message and not accept anything but the original text you
have there.

Good Luck
 
Headers are definitely required, but why use defined names
rather than the object type hyperlink. Is that to get around some
bugs in Excel or to speed things up. I wonder what would happen
if you converted it HTML

Something that may be of interest: Excel does not support multiple
hyperlinks in a cell so those 26 letters to get to the link are each
going to each require a cell. If actually using HTML would probably
have each letter on it's own webpage, if loading time would be
noticeable.

A publisher would probably use a letter in small box, you might be
able to simulate this without using graphics by surrounding in
square brackets and by centering the text.
 
I needed exactly this a few minutes ago and created the following
forumula for that, which seems to work. The only problem is that I
couldn't avoid hard-coding the name of the file and sheet in the
formula, so if someone has an idea on how to avoid that, it becomes
very generic.

=HYPERLINK("[FileNamHere.xls]'Sheet Name
Here'!R"&CELL("row",C72)&"C"&CELL("col",C72), "Repeat Step "&A72)

This shows up something like "Repeat Step 10" in my case. And since
all the cell references are seen as "real" references, excel will
automatically adjust them when rows or columns are inserted or
deleted.

Thank you,
Hari
 
Hi Hari, and Shaz,
That was certainly a pain having to code the pathname, but then I
stumbled into this by mistake in Excel 2000. Excel 2002 , which I do not
have, does not require such measures but I've been told that this works
from Excel 97 on up.

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

The above examples from my sheet.htm page, which
has additional examples including one with VLOOKUP. Since I
didn't understand the original question I had been thinking it might
require VLOOKUP and had forgotten where I had the code to use
a hyperlink with VLOOKUP. Help with VLOOKUP Worksheet
function can be found in
..http://www.mvps.org/dmcritchie/excel/vlookup.htm
if it turns out that was what was also needed.

No address cell is within double quotes so the formulas will adjust if
you insert/delete rows or want to use the fill-handle.
CELL is a Volatile function, but worksheet volatile functions do not present
the serious slowdowns that Volatile User Defined (VBA) Functions can have.




Hari Krishna Dara said:
I needed exactly this a few minutes ago and created the following
forumula for that, which seems to work. The only problem is that I
couldn't avoid hard-coding the name of the file and sheet in the
formula, so if someone has an idea on how to avoid that, it becomes
very generic.

=HYPERLINK("[FileNamHere.xls]'Sheet Name
Here'!R"&CELL("row",C72)&"C"&CELL("col",C72), "Repeat Step "&A72)

This shows up something like "Repeat Step 10" in my case. And since
all the cell references are seen as "real" references, excel will
automatically adjust them when rows or columns are inserted or
deleted.

Thank you,
Hari


Shaz said:
Hi

I have managed to create hyperlinks,what I am doing is
trying to make an "address book" type thing, where at the
top of the page you have the alphabet, you click on "p"
and it takes you to the "p" section on the same page,
which works fine, except when I add lines, the hyperlink
of the "p" section doesn't move down, it stays where it
is. How can I make it move down and stay linked to the
top alphabet, when I add lines??

Is there a way where you can add data that cannot be
edited?? Such as "Surname", "Name", "Address" and stuff
like that. So if I send the document to someone, they
can't delete anything, but they can add things?? If you
know what I mean??

Please help if you can.

Regards
Shaz
Click to expand...
Click to expand...
 
David McRitchie said:
Hi Hari, and Shaz,
That was certainly a pain having to code the pathname, but then I
stumbled into this by mistake in Excel 2000. Excel 2002 , which I do not
have, does not require such measures but I've been told that this works
from Excel 97 on up.

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

The above examples from my sheet.htm page, which
has additional examples including one with VLOOKUP. Since I
didn't understand the original question I had been thinking it might
require VLOOKUP and had forgotten where I had the code to use
a hyperlink with VLOOKUP. Help with VLOOKUP Worksheet
function can be found in
..http://www.mvps.org/dmcritchie/excel/vlookup.htm
if it turns out that was what was also needed.

No address cell is within double quotes so the formulas will adjust if
you insert/delete rows or want to use the fill-handle.
CELL is a Volatile function, but worksheet volatile functions do not present
the serious slowdowns that Volatile User Defined (VBA) Functions can have.
Click to expand...

Excellent!! This definitely is much better than my solution. Thanks a
lot for letting us know about this feature.

Hari
 
Back
Top