hyperlink to value and not cell?
Ken Wright
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
"Same Guy" <w...@another.address> wrote in message
news:kq70jvomuknsnu16j...@4ax.com...
> 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.
David McRitchie
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/groups?threadm=056201c358d7%24bc4fdfb0%24a501280a%40phx.gbl
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.
---
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 McRitchie
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
"Same Guy" <w...@another.address> wrote in message news:6ol0jvcntn0a4de2v...@4ax.com...
> 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/groups?threadm=056201c358d7%24bc4fdfb0%24a501280a%40phx.gbl
>
> 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!
Ed
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"
<ken.w...@NOSPAMntlworld.com>:
>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
"David McRitchie" <dmcri...@msn.com> wrote in message
news:ue6%23pI8WD...@TK2MSFTNGP11.phx.gbl...
David McRitchie
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
"Ed" <ed_m...@removethis.hotmail.com> wrote in message news:eXuEJ6BX...@tk2msftngp13.phx.gbl...
David McRitchie
>
> 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
=
David McRitchie
You will have to write a macro. None of the methods
will survive sorting the target.
---
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
"Same Guy" <w...@another.address> wrote in message news:gmn2jv8lmmbesaaum...@4ax.com...
> Wed, 6 Aug 2003 06:30:50 -0700: written by "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"
> ><ken.w...@NOSPAMntlworld.com>:
> >
> >>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
>
> I tested that yesterday and found that when I add or delete a row, then
> the name will stick with the cell that contains that value thus
> preserving my indexing.
>
> The real test will happen next time I add a bunch of entries to the end,
> then sort according to first column values, and see if the name still
> sticks with the cell which contains the desired value.
>
>
>
>
David McRitchie
That not a macro that you want to start with.
and there are a lot of additional considerations involved
with presentation. Someone might have something.
Considerateions such as how many rows of data,
is everything in one worksheet.
There may be other solutions perhaps even just using the
FIND shortcut key, rather than hyperlinks. OR even
another column with the first letter of the lastname
=LEFT(B1,1)
and filter on that column.
You could use insert a column to left, use the above formula
and the fill handle to copy down. (my page fillhand.htm )
then an Event macro to place you somewhere else
In the meantime for getting started with macros, you can look
at the following pages on my site.
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm
and some useful User Defined Functions
Show FORMULA of another cell in Excel
http://www.mvps.org/dmcritchie/excel/formula.htm
Look for some VBA Tutorials in
http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials
If the page is relatively small and everything is on one page you could
use the following Event Macro which are installed differently
than regular macro. Install by right click on the worksheet tab,
view code, insert code. To invoke macro double-click on any cell.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim letter As String
letter = InputBox("Supply Letter", "Supply letter or last" _
& " name", "McRitchie")
If letter = "" Then Exit Sub
On Error Resume Next
Columns("A:A").Find(What:=Left(letter, 1), After:=[A1], LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
).Activate
Columns("B:B").Find(What:=letter, After:=[b1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub
---
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
"Same Guy" <w...@another.address> wrote in message news:26r4jv4kl11utcfhd...@4ax.com...
> Wed, 6 Aug 2003 21:26:29 -0400: written by "David McRitchie" <>:
>
> >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.
>
>
David McRitchie
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 ?
--
---
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
"Same Guy" <w...@another.address> wrote in message news:e2q5jvk76ousm34tf...@4ax.com...
> 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
then install this Event macro into that sheet, you would
not need hyperlinks, and you would get closer to where
you actually want to be.
to install event macro
right click on the sheet tab
View code
insert the following code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim letter As String
letter = InputBox("Supply Letter", "Supply letter or last" _
& " name", "McRitchie")
If letter = "" Then Exit Sub
Rows(Application.Match(letter, Range("A:A"), 1)).Activate
End Sub
or if you have descriptive title on the top row, use this code
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim letter As String
letter = InputBox("Supply Letter", "Supply letter or last" _
& " name", "McRitchie")
If letter = "" Then Exit Sub
Rows(Application.Match(letter, Range("A2:A65536"), 1)).Offset(1, 0).Activate
End Sub
The entire column will be looked at and you will get the first match
or if not an exact match the highest value alphabetically before the
value you are looking for. So your data should be sorted but
the macro will not fail either way.
Application.Match means that the Excel MATCH Worksheet Function
will be used.
It would be a lot more friendly if you used your real name in the
newsgroups.
---
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
> "Same Guy" <w...@another.address> wrote ....
David McRitchie
I mistakenly said that the data on the sheets did not need to
be sorted.
I think you will find that the use of the MATCH Worksheet Function
in a macro as application.Match eliminates the need
for Hyperlinks.
I had an additional bit of difficulty, trying to select the row
on another worksheet from the Event macro. I solved it by
invoking another macro, but I don't know why I found this
necessary.
Above your links in Sheet1 insert a new row perhaps row3
A3: Quick Entry to a name/product {Alt+enter}
Fill in name or first letters of name to right
B3: 'Enter here
C3: 'Enter here
Whenever you enter a name into B3 for sheet2
or into C3 for sheet3 you will search on the value entered,
An exact match will highlight that row, an inexact match
will highlight the row before where the name might have
been placed. Example if you try for Gu and you
have G followed by Geri followed by Guy, since you don't have an
exact match you will select the row with Geri. But you will be a lot
closer with or without an exact match than with your hyperlinks.
I think you intend to also have each letter of the alphabet
in your data as cell values.
If you want to go back to same thing you already have entered
in the cell then hit F2 then enter.
Change Event macro for Sheet1: -- Event Macro
installed by right-click on sheet1 tab, view code, insert following:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As String, vrow As Long
Dim sht As Worksheet
If Target.Row <> 3 Then Exit Sub
If Trim(Target.Value) = "" Then Exit Sub
val = Target.Value
If val = "" Then Exit Sub
If Target.Column = 2 Then
Set sht = Worksheets("Sheet2")
ElseIf Target.Column = 3 Then
Set sht = Worksheets("Sheet3")
Else
Exit Sub
End If
sht.Select
Call MatchRow(val, "A2:A65536")
'cannot figure out why need call above instead of following
'Rows(Application.Match(val, Range("A2:A65536"), 1)).Offset(1, 0).Activate
End Sub
Install the following Event macro into Sheet2 and Sheet3
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'-- Column A after A1 title, must be in Ascending order
Dim val As String
val = "M" 'default value
val = InputBox("Supply Name", "Supply Name or first " & _
"few letters of name", val)
If val = "" Then Exit Sub
Rows(Application.Match(val, Range("A2:A65536"), 1)).Offset(1, 0).Activate
End Sub
Install the following in a Standard Module (module1)
see http://www.mvps.org/dmcritchie/excel/excel.htm
Alt+F11, Ctrl+R, select anything in your VBA project library,
Insert menu, module
If you already have a module1 you can install it in there
Sub MatchRow(sht As String, val As String, rng As String)
Rows(Application.Match(val, Range(rng), 1)).Offset(1, 0).Activate
End Sub
I can email you sample workbook, there are no hyperlinks in it.
But you can keep using the hyperlinks in your own workbook
for sheet2 if they are easier to work with.
---
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
"Same Guy" <w...@another.address> wrote in
> Sheet 3 has information that is added in batches of around 15 at a time
[and is sorted after archiving] for complete post and complete thread see:
http://google.com/groups?threadm=bf3ajvos94afcoh7m...@4ax.com
Hari Krishna Dara
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
David McRitchie
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" <w...@another.address> wrote in message news:gatdjvg6p6rv59h9f...@4ax.com...
> 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 ;-).
>
>