Solved

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

Posted on 2014-11-30
3
638 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 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

808 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