How to navigate quickly in a complex workbook using hyperlinks
Question: I have a workbook containing a lot of worksheets and data. I need a way to find certain data/sheets quickly. How can I do this?
Answer: Create a table of contents and use hyperlinks to move around in a large complex workbook.
Here is how to do this in Excel:
-
On your front sheet create a list with important workbook sheets you often visit
- Right click on January and select Hyperlink...
-
Click "Link to: A place in this document"
- Type cell reference. You can link to the same sheet as many times as you want. But by using a cell reference you can link to a certain place in that sheet, creating a more sophisticated navigation system.
-
Click OK!
- Try clicking on January. Now can find important places in a workbook really quick!
- Repeat with remaining sheets.
Download excel sample file for this tutorial.
table-of-contents.xls (Excel 97-2003 Workbook *.xls)
Create links to all sheets in a workbook
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly [โฆ]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise [โฆ]
Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When [โฆ]
Quickly jump to last row in a data set using excel hyperlink function
Today Iยดll show you how to create a useful hyperlink in excel. If you click the link it will take [โฆ]
List all hyperlinks in worksheet
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" [โฆ]
Macro creates links to all sheets, tables, pivot tables and named ranges
This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, [โฆ]
Quickly select a data set or an excel defined table [HYPERLINK]
If you often copy data sets or tables, the following technique might be interesting! The animated gif shows two hyperlinks, [โฆ]
List files in folder and create hyperlinks (VBA)
This article demonstrates a macro that populates a new worksheet with filenames from the active folder which is the same [โฆ]
One Response to โHow to navigate quickly in a complex workbook using hyperlinksโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
this is really usefull