?
Solved

How do I set up a variable cell Hyperlink

Posted on 2015-02-22
7
Medium Priority
?
115 Views
Last Modified: 2016-02-10
In the attached workbook on sheet1 the variable date is in cell M6
The formula to find the address is in cell S6

How do I set up a Hyperlink of VBA code to have focus jump to cell CI19?

The date in M6 will change all the time, so the hyperlink must handle that with a formula....
Hylperlink.xlsm
0
Comment
Question by:DougDodge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40625142
Hi,

pls try

Sub Macro()
    Application.Goto Reference:=Range(Range("S6").Value).Address(, , xlR1C1)
End Sub

Open in new window

or as  formula

=HYPERLINK("[Hylperlink.xlsm]Sheet1!"&SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19),SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19))

Open in new window

Regards
0
 

Author Comment

by:DougDodge
ID: 40625613
Thank you for your reply, but neither of your solutions work.

The macro() gives:
 Run-time error '1004':
Application-defined or object-defined error

The formula tries to search for a workbook on the network and then fails.
0
 

Author Comment

by:DougDodge
ID: 40625623
The Error with the formula:

Opening c:\Users\ddesk\Desktop\Site Progress\Hyperlink.xlsm

Some files can contain viruses or otherwise be harmful to your computer.
It is important to be certain that this file is from a trustworthy source.

Would you like to open this file? OK - CANCEL

Click on OK and get:

An unexpected error has occurred.
0
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40625625
Hi,
is the excel file the same as in the example

Hylperlink.xlsm
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40625631
pls try

if the Hyperlink file is already open

=HYPERLINK("[Hyperlink.xlsm]Sheet1!"&SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19),SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19))
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40625681
Hi,

with variable file

=HYPERLINK(MID(CELL("filename"), FIND("[",CELL("filename")),100)&"!"&SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19),SUBSTITUTE(ADDRESS(ROW($AP$19:$OP$19),MATCH($M$6,$AP$19:$OP$19,0)+COLUMN(AP19)-1,2),""&ROW($AP$19:$OP$19),19))
HylperlinkV1.xlsm
0
 

Author Closing Comment

by:DougDodge
ID: 40625824
Works Perfectly..... Thank you.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question