Excel 2010 : linking cells across worksheets and still being able to sort

Hopefully this is possible without complex coding as I won't be the person curating this spreadsheet...

We have a large spreadsheet that is row upon row of case information, with each row representing a separate case.  One of the columns in the information is "patient name".   I've been asked if we can link that cell (patient name) from the main worksheet to a second worksheet (Summaries) that would simply be row after row of patient information with each patient being a separate row.   So if you click on [Smith, John] in the main worksheet, you'll be taken to [Smith, John]'s row in the Summaries worksheet.

On the surface this appears simple.  Hyperlink the [Smith, John] cell in the first worksheet to the corresponding cell in Summaries.  However, names will be added as time goes on, and the Summaries sheet will need to be sorted alphabetically when that happens, so even though [Smith, John] might be Row 25 today, he could be row 40 in a week.  Referencing Cell A25 from his entry on the main worksheet won't work, as next week A25 will have someone else's information.

So, how can I create a link from [Smith, John] on the main worksheet to [Smith, John] on the Summaries worksheet that will automatically adjust when the Summaries worksheet is resorted?  I'm not sure if the main worksheet will ever be resorted, I suppose it's possible, so the link should stand up to that happening as well.

Impossible task?  I can't seem to find a combination of hyperlink settings that work.   Thanks for any suggestions you can provide.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you supply a sample workbook?
Martin LissOlder than dirtCommented:
This is done via a double-click of the patient name on the main sheet.  If you like it could be changed so that just selecting the cell would trigger the code.

 Add the code to the main sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rngFound As Range
Dim strName As String

    ' This assumes that the "patient namne" column is column "A".
    ' Check to see if we are double-clicking on a cell in that column
    If Intersect(Target, Range("A2:A" & UsedRange.Rows.Count)) Is Nothing Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    strName = ActiveCell
    With Sheets("Summaries")
        Set rngFound = .Cells.Find(What:=strName)
        If rngFound Is Nothing Then
            MsgBox "No Summary record found for '" & strName & "'", vbOKOnly + vbCritical, "Missing Summaries Record"
            Exit Sub
        End If
    End With
Application.ScreenUpdating = True
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
This was a tricky one to solve but here are the couple of ways you can do the same...Enclosed is the workbook for your reference where it will change hyperlink automatically basis of value which you are entering in C1 or D1 on sheet2 and it will automatically move itself to the row which has that value in sheet1 in hyperlink... I have done it two ways...

I went into formulas-->Name Manager and created name range by two methods as in two formulas...

1st Formula-->=OFFSET(Sheet1!$A$1,MATCH(Sheet2!$C$1,Sheet1!$A:$A,0)-1,0)

2nd Formula-->=INDIRECT("Sheet1!A"&MATCH(Sheet2!$D$1,Sheet1!$A:$A,0))

You can use either of the formula to do what you are looking for..and i created a name range of the same...

Post this in order to assign hyperlink since the name range wasn't reflecting in when you right click to assign hyperlink..so i used a simple macro which is this...

Sub createhyperlink()

    Sheets("Sheet2").Hyperlinks.Add Anchor:=Sheets("Sheet2").Range("C1"), Address:="", SubAddress:="lke", TextToDisplay:=Sheets("Sheet2").Range("C1").Value
    Sheets("Sheet2").Hyperlinks.Add Anchor:=Sheets("Sheet2").Range("d1"), Address:="", SubAddress:="def", TextToDisplay:=Sheets("Sheet2").Range("d1").Value
End Sub

Open in new window

This is just to assign the hyperlink to the cell..Now if you check if you change the value of the cell it automatically updates itself to the row which has that value...

Enclosed is the workbook for your reference...

Remy001Author Commented:
I am sure these are both valid solutions, however they are way beyond my level of Excel comprehension, and I have to turn this spreadsheet over to someone who knows very little about Excel beyond the basics, so I think the answer to my question is probably "It's possible, but too difficult to manage given the situation."

I really don't want to add VB Code to the spreadsheet, as nobody that touches it after me will have a clue what is happening.  The use of INDIRECT when referencing cells looks like it might be workable, but I don't understand what is going on there from the description.  Where do I enter the INDIRECT formula, and how does it reference the original cell and the row on the second sheet?
Saurabh Singh TeotiaCommented:

What i'm basically doing find the row number where the value you are trying to find is present..Now once i find the row number i concatenate that with sheet1!a which is basically a address value written as text..Now indirect converts this entire value back from text to a range and refers to that range automatically which then i use in hyperlink to link your value..

Once you understand the logic you will know its not that difficult..Again you dont have to set the code in the workbook..You just have to use it once to set hyperlink by macro of the calculate name range..once the hyperlink is set..It will automatically works and will link you to the desired value what you are looking for..and post that you can remove the macro code since hyperlink will be still their and it will work when you enter a new value...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.