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

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
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jkpieterseCommented:
The trick is to prepend the cell address with the #:

=HYPERLINK("#A1","Home")
1
WeThotUWasAToadAuthor Commented:
Great. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.