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
Hyperlink to first match
Build hyperlink with VLOOKUP
Hyperlink to first blank cell
Related functions
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....
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...
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....
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...
