?
Solved

Use the Excel =HYPERLINK() function to jump to a cell or defined range in the SAME worksheet

Posted on 2014-11-30
3
Medium Priority
?
748 Views
Last Modified: 2014-12-04
Hello,

When using the Excel =HYPERLINK() function, what is the proper form for jumping to a cell or defined range in the same worksheet?

The Excel Help file isn't very helpful because it only describes more distant links like going to a website URL or opening worksheets in other files. The following is the closest I can find:
=HYPERLINK("[Budget.xlsx]E56", E56)
 To jump to a different sheet in the same workbook, include the name of the sheet, followed by an exclamation point (!), in the link. In the previous example, to create a link to cell E56 on the September sheet, include September! in the link.
Here are a two examples of what I'm after:

1) Suppose you want a link in cell D4 which displays "Home" and takes you to cell A1 in the same worksheet. I know that in practice, it would be simpler to use the Hyperlink box (Ctrl+k) for this link but I'm specifically interested in seeing the form using the =HYPERLINK() function.

2) Suppose you define the range A1:A2 to be named "Origin" and instead of going to a single cell or using the cell reference A1, you want to include the named range in the =HYPERLINK() function and end up with both cells in the range being selected.

If someone could show me the formulas for these two examples, I will be off and running.

Thanks
0
Comment
Question by:WeThotUWasAToad
[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
3 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 40472998
You need to reference the workbook and sheet names when using the Hyperlink function.

1) =HYPERLINK("[Book1.xlsx]Sheet1!A1", "Home")
2) =HYPERLINK("[Book1.xlsx]Origin", "Home")
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40473245
The trick is to prepend the cell address with the #:

=HYPERLINK("#A1","Home")
1
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40482240
Great. Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

752 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