Problem: Sorting with Hyperlinks

[It's the Principle!]

I need expert help, please.

Before you say anything, yes, I would much rather do this in Access,
but my boss doesn't know how to use it. (He also uses a Mac, so what
do you expect?)

I am creating a spreadsheet in Excel to include my boss' trauma cases.
Linking to the hospital database is clunky and unreliable, and so he
wants me to pull in JPGs of the imaging to our own server and link to
them. I just started the project and already hit a snag.

When I have just one image, I put in a hyperlink to it directly on the
server. But when there are several images he wants, instead of
creating several more columns in the main sheet, I created a second
sheet and link to the patient there, which are kept as pasted links so
the order on the second sheet will remain the same as the order on the
main sheet. From there, there are links to the individual images on
the server. This way, the second sheet just has the patient's name and
a few columns each with a link to an image.

The problem comes when I sort the main sheet to look at particular
types of trauma, age of patients, etc. The hyperlinks from the main
page to the single images follow just fine, but the hyperlinks to the
second sheet remain absolute, i.e., pointing to the same cell reference
even though the data has moved, effectively pointing to the wrong
patient, ergo the wrong images.

I saw exactly this question in this newsgroup, when I checked Google,
but never saw a resolution. Is there one? Are the overpaid MS
programmers still up to their old tricks?

 

[Dave Peterson]

If I understand correctly, this is what you're doing.

You have a list of names on sheet1. Each one of those names has a hyperlink
(via insert|Hyperlink) to a second sheet. This second sheet contains hyperlinks
(via insert|Hyperlink) to a picture (that opens in another program) and a
hyperlink (via insert|hyperlink

And when you sort the first sheet, the hyperlinks to the second sheet work
fine. And the hyperlinks to the picture files work fine. But the hyperlink to
go back to Sheet1 get broken.

If this is correct, you have at least a couple of choices.

#1. Define a name in Sheet1 for each cell to come back to. The hyperlink on
the second page that comes back to the cell on the second page can be changed to
an =hyperlink() formula that points at that named cell.

For instance, I named A25 on Sheet1 this: _Patient_Jones

Then I could add this =hyperlink() formula in a cell on that second sheet:

=HYPERLINK("#"&CELL("address",_Patient_Jones),_Patient_Jones)
or
=HYPERLINK("#"&CELL("address",_Patient_Jones),"Click Me")

#2. Stop sorting the data--maybe use data|filter|Autofilter instead???

#3. Replace the hyperlinks with macros. Provide a button that takes the user
back to a location based on the what's in column A of the row with the
activecell.

Option Explicit
Sub testme()

Dim res As Variant
Dim RngToMatch As Range

With Worksheets("Sheet1")
Set RngToMatch = .Range("a:a")
End With

With ActiveSheet
res = Application.Match(.Cells(ActiveCell.Row, "A").Value, _
RngToMatch, 0)
If IsError(res) Then
'no match found
Beep
Else
Application.Goto RngToMatch(res), scroll:=True
End If
End With

End Sub

 

[It's the Principle!]

If I understand correctly, this is what you're doing.

You have a list of names on sheet1. Each one of those names has a
hyperlink (via insert|Hyperlink) to a second sheet. This second
sheet contains hyperlinks (via insert|Hyperlink) to a picture
(that opens in another program) and a hyperlink (via
insert|hyperlink

And when you sort the first sheet, the hyperlinks to the second
sheet work fine. And the hyperlinks to the picture files work
fine. But the hyperlink to go back to Sheet1 get broken.

If this is correct, you have at least a couple of choices.

#1. Define a name in Sheet1 for each cell to come back to. The
hyperlink on the second page that comes back to the cell on the
second page can be changed to an =hyperlink() formula that points
at that named cell.

For instance, I named A25 on Sheet1 this: _Patient_Jones

Then I could add this =hyperlink() formula in a cell on that
second sheet:

=HYPERLINK("#"&CELL("address",_Patient_Jones),_Patient_Jones)
or
=HYPERLINK("#"&CELL("address",_Patient_Jones),"Click Me")

#2. Stop sorting the data--maybe use data|filter|Autofilter
instead???

#3. Replace the hyperlinks with macros. Provide a button that
takes the user back to a location based on the what's in column A
of the row with the activecell.

Option Explicit
Sub testme()

Dim res As Variant
Dim RngToMatch As Range

With Worksheets("Sheet1")
Set RngToMatch = .Range("a:a")
End With

With ActiveSheet
res = Application.Match(.Cells(ActiveCell.Row, "A").Value,
_
RngToMatch, 0)
If IsError(res) Then
'no match found
Beep
Else
Application.Goto RngToMatch(res), scroll:=True
End If
End With

End Sub

No, not correct. I have simple linked cell between the first and
second sheet so that when I enter a new name on the first it will
populate on the second. They aren't hyperlinks.

The hyperlinks are on the first page to the images stored on the
server, we are not accessing another program. I simply mentioned
that some background as to why we're doing this. I am pulling all
the images of x-rays, MRIs, etc., out of the hospital's storage into
our server and linking to them from the Excel sheet via hyperlink.

Again, when there is more than one image, instead of a hyperlink
directly to the image, I link to the second page where it lands on
the patient's row which then has the links to the images on the
server. The problem is, when I sort the first sheet, the hyperlinks
to the second sheet then point to the wrong place on the second
sheet because they do not follow. For example:

First sheet:

Jones, Date of Birth, Displaced Patella w/Fracture, CPT, ICD-9

If there is one image, I would hyperlink the injury to the image.
If there is more than one image, I would hyperlink the injury to the
second sheet:

Second Sheet:

Jones, RF1, RF1, CT

Each of the images after "Jones" are hyperlinked to the
corresponding image on the server.

Add a few more and then sort it by, say, the date of service because
you want to see if you get more cases in the summertime (of course
you do, silly!), Jones moves from A1 to A4 on both sheets, but the
hyperlink on his A4 line points to Maxwell who is now occupying A1
on both the first and second sheet. And the hyperlinks on the A1
row to supposedly Maxwell's broken femur are pointing to Jones'
broken kneecap.

I understand your filtering suggestion, but filters won't work for
certain kind of sorts. Not to mention, you might have noticed, I'm
dealing with a surgeon. Best in the country with a scalpel (or a
mallet might be more accurate) not so reliable with a mouse.

Thanks for any help.

 

[Dave Peterson]

Formula links that refer to the address of that second sheet aren't going to
work for you.

It looks like your data has a unique key (patient name is unique???). If you
want to return a value from that second sheet, don't use a formula like:

=sheet2!x99

Instead, try matching up on that unique key and bring the value back that way.
If that unique key is in the leftmost column of the range of values to bring
back, you could use =vlookup(). If it isn't the leftmost column of that range,
you could use =index(match()).

=vlookup(a2,sheet2!a:e,3,false)
will bring back the 3rd column of sheet2 columns A:E (so it would be column C)
for the first row that matches the key stored in A2.

=index(sheet2!c:c,match(a2,sheet2!d:d,0))
will bring back the value in column C of sheet2 for the first value in column D
of sheet2 that matches A2.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[It's the Principle!]

Formula links that refer to the address of that second sheet
aren't going to work for you.

It looks like your data has a unique key (patient name is
unique???). If you want to return a value from that second sheet,
don't use a formula like:

=sheet2!x99

Instead, try matching up on that unique key and bring the value
back that way. If that unique key is in the leftmost column of the
range of values to bring back, you could use =vlookup(). If it
isn't the leftmost column of that range, you could use
=index(match()).

=vlookup(a2,sheet2!a:e,3,false)
will bring back the 3rd column of sheet2 columns A:E (so it would
be column C) for the first row that matches the key stored in A2.

=index(sheet2!c:c,match(a2,sheet2!d:d,0))
will bring back the value in column C of sheet2 for the first
value in column D of sheet2 that matches A2.

Debra Dalgleish has lots of notes on =vlookup() and
=index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for
=index(match()))

I don't see how that works for this purpose at all.

 

[Dave Peterson]

I thought you had formula links that returned values from sheet2 into sheet1.

But I guess I was mistaken.

:

 

[It's the Principle!]

I thought you had formula links that returned values from sheet2
into sheet1.

But I guess I was mistaken.

:

No, I'm just jumping to Sheet2 so I can place links to the imaging
without having Sheet1 clogged up with columns that would be rarely
used. To say it a different way, I don't want to design Sheet1 with 10
columns to use for links to images when I will rarely use them.
Instead, I'm trying to use that column to jump to a second sheet where
the additional columns are and link to the additional images from
there. The problem is that when you sort the sheet the links point to
the right place. They are absolute rather than relative. I'm only
going to another sheet to access additional information, but the
information is not keep up if you change the order of the patients.

 

[Dave Peterson]

What does this mean: "The problem is that when you sort the sheet the links
point to
the right place."

I still don't understand when you say links--are you talking hyperlinks, formula
links or what???

 

[It's the Principle!]

What does this mean: "The problem is that when you sort the sheet
the links point to
the right place."

I still don't understand when you say links--are you talking
hyperlinks, formula links or what???

Sorry, I meant DON'T point to the right place.

Hyperlinks. It has always been hyperlinks. Never mind. Apparently
you don't know the answer.

 

[Dave Peterson]

Good luck.

Sorry, I meant DON'T point to the right place.

Hyperlinks. It has always been hyperlinks. Never mind. Apparently
you don't know the answer.


--
Brandy Alexandre

The measure of a man's real character is what he would do if he knew
he never would be found out. -- Thomas Babington Macaulay

 

[Adam]

Let me see if I can understand this. The OP wants to connect pictures that are linked on Sheet 2 to information on Sheet 1. That works fine. This is using Excel as a crude type of data base because her boss is used to cutting instead of Access. And uses a MAC. And trying to train a doctor is like deprogramming ducks.

The problem is that the Sheet 1 needs to be sorted on various items. Why? Probably for convenience or to look things up or as a crude form of knowledge retrieval. Like bring up all the kneecaps that had keyhole surgery on the dorsal side, assuming there is a dorsal side.

But when Sheet 1 gets sorted, the links to the photos on Sheet 2 do not get sorted. Apparently they became absolute instead of relative.

Your suggestion for ways to sort is okay for Sheet 1 but can that work for Sheet 2?

Does this sound about right so far? Could there be a simple switch to say connect Sheet 1 to Sheet 2 if using links to yet another place?



EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Dave Peterson]

If you're asking me, I don't know.

I haven't been able to figure out the question.

 

[Adam Adam]

It's a strange problem.

Even on one sheet.

I set up a little rows and column. Just 2 columns x 4 rows, 1 2 3 4, and 10 20 30 40. A tiny test.

I made one of them a Hyperlink, just one cell.
I connected the cell with 40 to a web site.

When I go to sort, that one cell just won't sort.

Interesting.

I am not an expert at all with Xcel. I can't even get rid of the Hyperlink. Probably this may be a bug and not a feature, the lack of sorting with hyperlinks.

I was using Xcel 2000 which is from Office 2000. Does this problem exist in the later versions?

I suspect the problem is with Hyperlinks and not necessarily with Sheet 1 and Sheet 2. And it appears to make the cell absolute. I can't look up the Help functions yet because that crashes, need to do some tricks. Maybe this is on the internet and there is a macro or work-around. Not surprised but I think we have the buzz words down, Hyperlinks, Xcel, sorts, absolute, and relative - the same words the OP used.

Is this a bug or a feature?

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Dave Peterson]

I couldn't duplicate that sort problem in xl2003 and I don't recall having a
problem in any version of excel.

 

[Adam Adam]

It seems Hyperlinks don't sort.

http://groups.google.com/group/micr...0ec9aca7dba/27134ac53b6accb8#27134ac53b6accb8

Interesting.

The person who replied with VLOOKUP suggestion, that's was suggest also above.

That's going to take some coding though.

Anyone know why you can't sort Hyperlinks? Is it too much to ask a spreadsheet to do, as opposed to a data base?

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Adam Adam]

I can't believe it. Going back 10 years, there is a bug that Hyperlinks cannot be sorted, so solution is to manually correct all the hyperlinks, gee thanks, from the mouth of the monopoly, http://support.microsoft.com/kb/214328 :

Hyperlinks are removed or invalid after you sort cells that contain these hyperlinks in Excel
View products that this article applies to.
Article ID : 214328
Last Review : January 16, 2007
Revision : 5.1
This article was previously published under Q214328
SYMPTOMS
In Microsoft Excel, if you sort a range of cells in a worksheet that contains hyperlinks, the following problems may occur:
• Some of the hyperlinks may be removed. Although the blue, underlined text appears in the cell, nothing happens when you click the link.
• Some of the hyperlinks may be changed so that they refer to the wrong addresses.

For example, a hyperlink that previously referred to one Web page may now refer to a different Web page.

CAUSE
This problem may occur if you copy and paste cells that contain hyperlinks, and then sort the cells.

RESOLUTION
If this problem occurs, manually correct the hyperlinks. To do this, use one of the following methods:
• If Excel has removed the hyperlink from the cell, select the cell and click Hyperlink on the Insert menu. Type the correct address in the Address box, and then click OK.

Note If Excel 2007 has removed the hyperlink from the cell, select the cell, and then click Hyperlink in the Links group on the Insert tab. In the Insert Hyperlink dialog box, type the correct address into the Address box, and then click OK.

-or-
• If the hyperlink refers to the wrong address, right-click the cell, and then click Edit Hyperlink. In the Edit Hyperlink dialog box, correct the address in the Address box, and then click OK.

Back to the top
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

APPLIES TO
• Microsoft Office Excel 2003
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition
• Microsoft Excel 97 Standard Edition
• Microsoft Excel 2004 for Mac
• Microsoft Excel X for Mac
• Microsoft Excel 2001 for Mac
• Microsoft Excel 98 for Macintosh
• Microsoft Office Excel 2007


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Adam]

I can't believe it. Going back 10 years, there is a bug that Hyperlinks cannot be sorted, so solution is to manually correct all the hyperlinks, gee thanks, from the mouth of the monopoly, http://support.microsoft.com/kb/214328 :

Hyperlinks are removed or invalid after you sort cells that contain these hyperlinks in Excel
View products that this article applies to.
Article ID : 214328
Last Review : January 16, 2007
Revision : 5.1
This article was previously published under Q214328
SYMPTOMS
In Microsoft Excel, if you sort a range of cells in a worksheet that contains hyperlinks, the following problems may occur:
• Some of the hyperlinks may be removed. Although the blue, underlined text appears in the cell, nothing happens when you click the link.
• Some of the hyperlinks may be changed so that they refer to the wrong addresses.

For example, a hyperlink that previously referred to one Web page may now refer to a different Web page.

CAUSE
This problem may occur if you copy and paste cells that contain hyperlinks, and then sort the cells.

RESOLUTION
If this problem occurs, manually correct the hyperlinks. To do this, use one of the following methods:
• If Excel has removed the hyperlink from the cell, select the cell and click Hyperlink on the Insert menu. Type the correct address in the Address box, and then click OK.

Note If Excel 2007 has removed the hyperlink from the cell, select the cell, and then click Hyperlink in the Links group on the Insert tab. In the Insert Hyperlink dialog box, type the correct address into the Address box, and then click OK.

-or-

• If the hyperlink refers to the wrong address, right-click the cell, and then click Edit Hyperlink. In the Edit Hyperlink dialog box, correct the address in the Address box, and then click OK.

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

APPLIES TO
• Microsoft Office Excel 2003
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition
• Microsoft Excel 97 Standard Edition
• Microsoft Excel 2004 for Mac
• Microsoft Excel X for Mac
• Microsoft Excel 2001 for Mac
• Microsoft Excel 98 for Macintosh
• Microsoft Office Excel 2007


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com