• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Hyperlink

P

Phellipe

New Member
Hello everyone!

I´ve been busting my brains trying to solve this, but I've come to the conclusion that it's currently beyond my skills.

Here's the situation:
In the first sheet "Controle", column C4:C28 i have some data that I take from other tabs, like a serial number.

I need to create an hyperlink that changes depending on what's written inside the lines at C4:C28.

For example:

C14, it's written Mantis4.
Mantis4 can be found at B40 in the Mantis tab.

C26, it's written MA444.
MA444 can be found at D25 in the COMAP.N tab.


I'm sorry if my explanation isn't that good, but i've attached a miniature of my sheet to make it easier to understand.


Btw... my job uses excel 2007 (yes.... 2007 still)
 

Attachments

  • Hyperlink Test.xlsx
    39.4 KB · Views: 6
On Controle cell D4:
=HYPERLINK("#"&B$4&"!B"&MATCH(C4,Segurança!$B:$B,0), C4)
Copy down

You will have to adjust the formula for each block of Column B
 
Select cells C4:C28 on Controle sheet and run this macro:
Code: 
Sub blah()
Dim xxx As Range

For Each cll In Selection.Cells
  For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "Controle" Then
      Set xxx = sht.UsedRange.Find(what:=cll.Offset(, -1).Value, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
      If Not xxx Is Nothing Then
        'cll.ClearContents
        cll.Hyperlinks.Delete
        cll.Parent.Hyperlinks.Add Anchor:=cll, Address:="", SubAddress:=xxx.Address(0, 0, external:=True), TextToDisplay:="link"
        Set xxx = Nothing
        Exit For
      End If
    End If
  Next sht
Next cll
End Sub
 
Hui said:
On Controle cell D4:
=HYPERLINK("#"&B$4&"!B"&MATCH(C4,Segurança!$B:$B,0), C4)
Copy down

You will have to adjust the formula for each block of Column B
Click to expand...

Hui, thank you for your reply!

Unfortunately the data changes constantly.
What I need is something that read what's inside C4 (in this case) and then send me to where it is.

In this formula you've sent me, it looks only at "Segurança" tab. I need it to look inside each and every tab, and if the value is found, then it takes me there.


I've tried modifying the formula to:


=IFERROR(IF(C04<>"";HYPERLINK("#'Segurança'!B"&MATCH(C04;'Segurança'!B:B;0);C04);(HYPERLINK("#'Monit.Visual"!B"&MATCH(C04;'Monit.Visual'!B:B;0);C04)));(HIPERLINK("#'Mantis!C"&MATCH(C04;'Mantis'!B:B;0);C04)))


To see if i could make it look into at least 3 tabs, but it also failed.
 
p45cal said:
Select cells C4:C28 on Controle sheet and run this macro:
Code: 
Sub blah()
Dim xxx As Range

For Each cll In Selection.Cells
  For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "Controle" Then
      Set xxx = sht.UsedRange.Find(what:=cll.Offset(, -1).Value, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
      If Not xxx Is Nothing Then
        'cll.ClearContents
        cll.Hyperlinks.Delete
        cll.Parent.Hyperlinks.Add Anchor:=cll, Address:="", SubAddress:=xxx.Address(0, 0, external:=True), TextToDisplay:="link"
        Set xxx = Nothing
        Exit For
      End If
    End If
  Next sht
Next cll
End Sub
Click to expand...


p45cal, thank you very much for your reply!

It did create hyperlinks in each and every selected cell, but the links didn't take me anywhere =(
 
bosco_yip said:
Please see attached file

Regards
Bosco
Click to expand...

Bosco, thank you for your reply!

Unfortunately the data changes everytime.

I really do need a way for it to look into ALL the tabs and find the serial number in the corresponding tab and take me there.

Your way would work like a charm if I could format the main sheet the way you did. But unfortunately I cannot =(
 
Phellipe said:
I still need a way to do it without macros, since my dear boss complains like my wife when i send him sheets with macros.
Click to expand...
In the code, change ThisWorkbook to ActiveWorkbook and put the code either into Personal workbook or any other. Do the same as before and you have your automatically generated hyperlinks without a macro in the same workbook. Save and send to your wife.
 
p45cal said:
In the code, change ThisWorkbook to ActiveWorkbook and put the code either into Personal workbook or any other. Do the same as before and you have your automatically generated hyperlinks without a macro in the same workbook. Save and send to your wife.
Click to expand...


Actually the sheet isn't used by me.
I'm helping out to make things easier to the operator who fills the sheet.

I'll try negotiating with the boss.

Your macro really did a fine job after all.
Thanks!
 
You must log in or register to reply here.
Back
Top