?
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
?
825 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
3 Comments
 
LVL 48

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

578 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