Summary

To build links to multiple sheets in a workbook, you can use the HYPERLINK function. In the example shown, the formula in D5, copied down, is:

=HYPERLINK("#"&B5&"!"&C5,"Link")

This formula generates a working hyperlink to cell A1 in each of the 9 worksheets as shown.

Generic formula

=HYPERLINK("#"&sheet&"!"&cell,"linktext")

Explanation 

This formula relies on concatenation to assemble a valid location for the HYPERLINK function.

In cell D5, the link location argument is created like this:

"#"&B5&"!"&C5 // returns ""#Sheet1!A1""

which returns the string "#Sheet1!A1". The formula then resolves to:

=HYPERLINK("#Sheet1!A1","Link")

Which returns a valid link.

The cell value in column C is entirely arbitrary and can be any cell you like. It could also be hardcoded into the formula as a string like this:

=HYPERLINK("#"&B5&"!A1","Link")

Note: The hash character (#) at the start of the sheet name is required. For more link syntax examples, see HYPERLINK.

Related formulas

Excel formula: Hyperlink to first match

Hyperlink to first match

Working from the inside out, we use a standard INDEX and MATCH function to locate the first match of lookup values in column B: INDEX(data,MATCH(B5,data,0)) The MATCH function gets the position of the value in B5 inside the named range data, which for the lookup value "blue" is 3. This result goes...
Excel formula: Build hyperlink with VLOOKUP

Build hyperlink with VLOOKUP

The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name . Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named range "link_table" (B5:C8). The lookup value comes...
Excel formula: Hyperlink to first blank cell

Hyperlink to first blank cell

Working from the inside out, we use MATCH to locate the relative position of the last entry in column C: MATCH(9.99E+307,C5:C100) Basically, we are giving MATCH a "big number" it will never find in approximate match mode. In this mode, MATCH will "step back" to the last numeric value. Note: this...

Related functions

Excel HYPERLINK function

HYPERLINK Function

The Excel HYPERLINK function returns a hyperlink to a given destination. You can use HYPERLINK to create a clickable hyperlink with a formula. The HYPERLINK function can build links to workbook locations, pages on the internet, or files on network servers....
Excel CELL function

CELL Function

The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. See below for a full list of...
Excel INDEX function

INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
Excel MATCH function

MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions