Q: hyperlink to value and not cell?

[Same Guy]

I have an Excel workbook (database) with three sheets.

The first sheet is an index with hyperlinks to cells in the other two
sheets. The hyperlinks are to alphabetical indexes ex:

A
144 Snakes
145 T
146 Truffles
147 Turtles

'A' is the column and the numbers are the row numbers.

I noticed that when I deleted a row on the second sheet, that the
hyperlinks below that row stayed fixed to the cell to which I had
assigned
then and didn't shift up with the value to which I had intended the cell
to
link.

Using the above example:

If I had a hyperlink to A145, or T since it is an index point, and I
deleted A144, then my hyperlink would still point to A145 or 'Truffles'
and
not to 'T' as I would like it to.

How do I code the hyperlinks to point to the value in the cell and not
the
cell itself?

Since I'm trying to index my database, is there an easier way to do this
instead of creating individual hyperlinks to each alphanumeric index
cell?

If this doesn't make any sense, please let me know and I will try to
explain in a better fashion.

Thank you for your time.

 

[Ken Wright]

Name the cell (Insert / Name / Define) and then link to the defined name.

 

[David McRitchie]

This looks so much like a question recently posted
simply create hyperlinks to the A's, B's, T's
to their headers they can be to the same page or
different page.

The other thread began at
http://groups.google.com/[email protected]

The hyperlinks created by ctrl+k not the Hyperlink Worksheet Function
at least I think that would be easier to setup and work with than
defined names.

I think your title is misleading and incorrect.

 

[Same Guy]

This looks so much like a question recently posted
simply create hyperlinks to the A's, B's, T's
to their headers they can be to the same page or
different page.

What do you mean by ..."to their headers..." above?

The other thread began at
http://groups.google.com/[email protected]

Thanks. I took a look at it and got confused as to what they were
saying pretty quickly.

The hyperlinks created by ctrl+k not the Hyperlink Worksheet Function
at least I think that would be easier to setup and work with than
defined names.

I think your title is misleading and incorrect.

Ummm, how so?

I have a sheet with 0-9, and the alphabet in individual cells and I
would like a hyperlink on each of the aforementioned cells to be linked
to a specific value on another sheet in the same excel workbook.

For example, if you click on 'A', it takes you to the cell which
contains the value 'A' and not a particular cell that is defined as
column#/row#.

Or do I just not have the terminology down yet?

Thanks again!

 

[Same Guy]

Tue, 5 Aug 2003 22:33:07 +0100: written by "Ken Wright"

Name the cell (Insert / Name / Define) and then link to the defined name.

ooo, I think that's it and very easy as well. Thank you! ^_^

 

[Ed]

David:

Please forgive me for jumping into another coversation here, but I'd like to
ask for a clarification on this point. I can see wanting to create a
hyperlink jump to the letter at the beginning of the list - actually to the
cell containing that letter.

"Same Guy" posted a received answer of:
Tue, 5 Aug 2003 22:33:07 +0100: written by "Ken Wright"

Name the cell (Insert / Name / Define) and then link to the defined name.

I haven't worked with names yet. If a certain cell is named, and rows
and/or columns are inserted/deleted, the name will follow that cell with
that value wherever it goes? If you've named A20, will A20 retain that name
if you insert 5 rows above? Or will the name follow to A25?

Ed

The single letter header is the single letter before the words following it.
Not an Excel term.

A hyperlink will take you somewhere (to a location). Both an HTML term and an Excel
term. If you want a value then you obtain a value, you do not goto a value,
because a value is not a location.

As I understand it you choose "S" you would want to go to the "S" at the
beginning of the S-words.

If you don't want to go there, but you want to obtain the value, what
value do you want to obtain, how would you identify it.

But since you said you have the answer you want, I guess it's done.
---
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

What do you mean by ..."to their headers..." above?

http://groups.google.com/[email protected]

Thanks. I took a look at it and got confused as to what they were
saying pretty quickly.




Ummm, how so?

I have a sheet with 0-9, and the alphabet in individual cells and I
would like a hyperlink on each of the aforementioned cells to be linked
to a specific value on another sheet in the same excel workbook.

For example, if you click on 'A', it takes you to the cell which
contains the value 'A' and not a particular cell that is defined as
column#/row#.

Or do I just not have the terminology down yet?

Thanks again!

 

[David McRitchie]

Hi Ed,
If you use the object hyperlinks, the ones you use Ctrl+K or
right-click then Edit hyperlink then insertion/deletion of
rows will not affect the hyperlink.

If you use the HYPERLINK Worksheet Function, which
is usually learned about much later, the link is within
double quotes so will not be changed. So you have to
use defined names and not a cell value.

Cell addresses within quotes will not change when lines are
inserted/deleted. If you use a definedname within the quotes
then after insert/delete of rows you still refer to the same definedname
and the correct range.

Setting up definednames is a bit more work
and you will have 26 defined names to look at in the namebox.
Defined names apply to the entire workbook. I think it
is a bit messy to work with for this purpose.

I had a bit of trouble trying to hyperlink to the defined name
in Excel 2000. The following suggested by Dave Peterson
in the other thread did not work for me.
=HYPERLINK(Start_A,"Jump to A")
but the following did work
=HYPERLINK("#Start_A","Jump to A")

Apologies to Dave Peterson and Ken Wright.
I'm also changing my mind on the defined name, while it creates
a mess of defined names to look at when trying to pick something
from defined names it does have the advantage if you are going
to refer to the link from **more than one place** in your workbook,
or even use the name box to pick out a name.

Example
skip to specific letter from another worksheet
skip to specific letter from same worksheet
skip to the next letter from header you just hyperlinked to.

In the other thread I mentioned using [A] to simulate what
a printer/publisher might use, But I think it might be better
to have the header cell as yellow background, centered,
with a value such as '--- A ---

If the workbook is of significant size, and you have a lot of users,
I wonder if it would be worth the extra effort to create a user
version in HTML. As much as I dislike the Microsoft
conversion with all the extra round-tripping code.
---
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

David:

Please forgive me for jumping into another coversation here, but I'd like to
ask for a clarification on this point. I can see wanting to create a
hyperlink jump to the letter at the beginning of the list - actually to the
cell containing that letter.

"Same Guy" posted a received answer of:
Tue, 5 Aug 2003 22:33:07 +0100: written by "Ken Wright"

Name the cell (Insert / Name / Define) and then link to the defined name.

I haven't worked with names yet. If a certain cell is named, and rows
and/or columns are inserted/deleted, the name will follow that cell with
that value wherever it goes? If you've named A20, will A20 retain that name
if you insert 5 rows above? Or will the name follow to A25?

Ed

The single letter header is the single letter before the words following it.
Not an Excel term.

A hyperlink will take you somewhere (to a location). Both an HTML term and an Excel
term. If you want a value then you obtain a value, you do not goto a value,
because a value is not a location.

As I understand it you choose "S" you would want to go to the "S" at the
beginning of the S-words.

If you don't want to go there, but you want to obtain the value, what
value do you want to obtain, how would you identify it.

But since you said you have the answer you want, I guess it's done.
---
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

Tue, 5 Aug 2003 18:28:42 -0400: written by "David McRitchie" <>:

This looks so much like a question recently posted
simply create hyperlinks to the A's, B's, T's
to their headers they can be to the same page or
different page.

What do you mean by ..."to their headers..." above?


The other thread began at
http://groups.google.com/[email protected]

Thanks. I took a look at it and got confused as to what they were
saying pretty quickly.


The hyperlinks created by ctrl+k not the Hyperlink Worksheet Function
at least I think that would be easier to setup and work with than
defined names.

I think your title is misleading and incorrect.

Ummm, how so?

I have a sheet with 0-9, and the alphabet in individual cells and I
would like a hyperlink on each of the aforementioned cells to be linked
to a specific value on another sheet in the same excel workbook.

For example, if you click on 'A', it takes you to the cell which
contains the value 'A' and not a particular cell that is defined as
column#/row#.

Or do I just not have the terminology down yet?

Thanks again!

 

[Same Guy]

You can't sort anything without having to redo it.

That stinks.

You will have to write a macro. None of the methods
will survive sorting the target.

How do I write a macro? I've never done that.

 

[Same Guy]

Thu, 7 Aug 2003 13:30:32 -0400: written by "David McRitchie" <>:

Do you mind if I email you the spreadsheet so you can actually see what
I'm trying to do? It is 108KB.

 

[David McRitchie]

You have the means of manually creating hyperlinks.

It is when you say you want to sort them, meaning that you
will be adding more data essentially starting over because
your links are not going to be in same place if you sort. So what
you need to have those same links is a method of automatically
creating those links, which would not be trivial.

For that reason I suggested perhaps you could do something
without actually using hyperlinks. That way you can refresh your
data from whatever source and since you would have no links
you would not have to update anything..

If you want me go write a macro to automatically create all
your hyperlinks, I won't.

If you think I missed something you can post more information.
I don't mind you sending the workbook but I'm not about to do
anything non trivial like providing the means for you to start
with new data and generating all the links..

What have you done so far ?
--

 

[Same Guy]

You have the means of manually creating hyperlinks.

It is when you say you want to sort them, meaning that you
will be adding more data essentially starting over because
your links are not going to be in same place if you sort. So what
you need to have those same links is a method of automatically
creating those links, which would not be trivial.

For that reason I suggested perhaps you could do something
without actually using hyperlinks. That way you can refresh your
data from whatever source and since you would have no links
you would not have to update anything..

~snip~

Hmmm, let me give a little more detailed information and then see if
there is a non-macro option available. I most certainly DO NOT want you
to write any scripts. That would make me dependent on someone else's
work and quite possibly limit my future flexibility if I so choose to
change anything. Sorry if I gave you the impression I wanted you to
write anything, that is NOT my intention.


Here's what I have (done):

1 Workbook that contains 3 sheets

Sheet 1 is an index for the contents of Sheet 2 and Sheet 3. Sheet 1
has two separate lists of '0-9,a,b,c,...,z' with one list for Sheet 2
and the other list for Sheet3. I have hyperlinks in place from Sheet 1
to Sheet 2, but creating links from Sheet 1 to Sheet 3 is the challenge.

Sheet 2 has information added one entry at a time since that is the
frequency with which information needs to be added. There may be the
rare occasion on which more than one entry will need to be added. This
sheet tracks physical objects stored in a particular location, so the
information added is not from an electronic source.

Since I only add one entry at a time, right-clicking and inserting a new
row where necessary is not an issue. I am now using the named-cell
option for hyperlinking Sheet 1 to Sheet 2 and this works very well.

Sheet 3 has information that is added in batches of around 15 at a time
and needs to be placed throughout the sheet. This information is a
record of large data files that have been burned to an Archival CD-R.

I currently copy and paste the information from the manifest '*.txt'
file I burn on the archival CD-R to the end of Sheet 3. I then clean
the data and sort in alphanumerical order according to the first column.

This is where I am trying to find a solution for linking the index list
on Sheet 1 to the desired index points on Sheet 3. The aforementioned
first column sorting causes the cell-naming method to fail.

I hope this is clear and if I haven't done a good job of explaining what
I ma trying to do, please let me know where I need to clarify things.

With all that said, any ideas on how to link the index on Sheet 1 to the
index points on Sheet 3?

 

[Same Guy]

Sat, 9 Aug 2003 20:22:08 -0400: written by "David McRitchie" <>:

~snip~

Thanks or the info and the macro(?). This will take me a bit of time to
digest. I'll post again when I have questions (which should occur ;-).

 

[David McRitchie]

When you find out if it is the solution that you want or not,
please post to the thread, as it is now the thread is beginning
to run in circles without direction..

As I think I suggested before it is a lot more user friendly if you
use your real name. (and perhaps a hotmail email address if
you don't want to use a personal one or a business one, but
only if you will read it).

 

[Same Guy]

Fri, 15 Aug 2003 23:28:05 -0400: written by "David McRitchie"

When you find out if it is the solution that you want or not,
please post to the thread, as it is now the thread is beginning
to run in circles without direction..

Will do. I've been uber busy w/Real Life, so this has been on the back
burner. Sorry for the delay in responding.