Blue Flower

Examples Hyperlinks Formula in Excel - Part 2

 How to create a hyperlink in Excel worksheets same workbook

In the image above are examples of created Hyperlinks in Excel for various situations whether the cells have a requirement or criterion or to directly put address link.

NOTE: If you copy the formula from this web site tutorials then note that the formula is in one row or one line.

Excel 'A14' cell in the image above:

Hyperlink in which exactly define the workbook, worksheet and cell address

The result of the formula, Hyperlink on 'Sheet2' in cell 'A1' in the same workbook: Link to 'Sheet2' in cell 'A1' in the same Workbook. Note that the defined workbook 'Book1.xlsx' that is connected with the name of the worksheet and cell 'A1'.

The formula is:
=HYPERLINK("[Book1.xlsx]"&"Sheet2!"&"A1";"Link to Sheet2 in cell A1 in the same Workbook")

 

Excel cell A15 in the image above:

Hyperlink in which exactly define the workbook, worksheet and cell address. This formula or hyperlink located in the workbook Book1.xlsx a path of opening a workbook Book7.xlsx

Clicking on the hyperlink will open the Excel workbook Book7.xlsx and position itself on the worksheet 'AB' in cell 'A9' (but only if the two workbooks are in the same folder.

The result is a formula Hyperlink text on the worksheet 'AB' in cell A9 in the workbook Book7.xlsx:

Link to 'Sheet AB' in cell 'A9' in the Workbook 'Book7.xlsx'

Note that the defined workbook 'Book7.xlsx' that is merge with the name of the worksheet and cell 'A1'.

The formula is:
=HYPERLINK("[Book7.xlsx]"&"AB!"&"A9";"Link to Sheet AB in cell A9 in the Workbook Book7.xlsx")

Excel cell A17 in the image above:

Hyperlink in which exactly define the workbook, a condition of the worksheet is located in the cell 'B17' and the target address is designated cell name 'prodaja'. This formula or hyperlink located in the workbook 'Book1.xlsx'

When you click on a hyperlink Excel will be positioned on the worksheet in cell 'B17', which is a condition in which the cell is called name 'Prodaja'

The result is a formula Hyperlink text worksheets provided in the named cell of the same workbook.

Link to the named cell 'Prodaja' in the same Workbook provided in B17

Note that the defined workbook 'Book1.xlsx' that is connected with the name of the worksheet and cell 'A1'.

The formula is:
=HYPERLINK("[Book1.xlsx]"&B17&"!"&"Prodaja";"Link to the named cell 'prodaja' in the same Workbook provided in B17")

 

Excel cell A18 in the image above:

Hyperlink in which exactly define the workbook, worksheet, and the target address is designated cell name 'Prodaja'. This formula or hyperlink located in the workbook 'Book1.xlsx' a path of opening a workbook 'Book7.xlsx'

Clicking on the hyperlink will open the Excel workbook Book7.xlsx and position itself on the worksheet 'ABC' in the cell that is called 'prodaja' (but only if the two workbooks are in the sieve folder).

The result is a formula Hyperlink text on the worksheet 'AB' in cell A9 in the workbook Book7.xlsx:

Link to the named cell 'Prodaja' on ABC sheet in 'Book7.xlsx'

Note that the defined workbook 'Book7.xlsx' that is connected with the name of the worksheet and cell 'A1'.

The formula is:
=HYPERLINK("[Book7.xlsx]"&"ABC!"&"prodaja";"Link to the named cell 'prodaja' on ABC sheet in Book7.xlsx")

Generally it is the HYPERLINK formulas that can seem like a formula below.

=HYPERLINK("[Book.xlsx]"&"Sheet2!"&"$A$1";"Text Link")
=HYPERLINK("[Book.xlsx]"&"AB!"&"A1";"Link of Sheet AB in cell A1")
=HYPERLINK("[Book.xlsx]"&"SheetName"&"!"&"CellAddressPosition";"text_link")
=HYPERLINK("[Book.xlsx]"&"SheetName!"&"CellAddressPosition";"Link to Sheet AB in cell A1 in the Workbook Book7.xlsx")

 

Excel cell A20 in the image above:

Hyperlink in which precisely define only the cell to which we want to position after clicking on a hyperlink.

The result is a formula Hyperlink text on the active worksheet in a cell 'A1' in the active workbook:

Link to cell 'A1' on the active sheet

You notice that it is not defined active workbook, but it determines the "hash sign" (red color) a worksheet formula sets (green letters) that is connected with the name of the worksheet and cell 'A1'.

The formula is:
=HYPERLINK("#"&"'"&MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256)&"'!"&"A1";"Link to cell A1 on the active sheet")

 

Excel cell A21 in the image above:

Hyperlink in which using the Excel CONCATENATE function define workbook, worksheet whose name is in cell 'B21' and a destination cell 'A1'.

The result is a formula Hyperlink text on the active worksheet in a cell 'A1' in the active workbook:

Link to 'Sheet3' on the 'A1' cell provided from 'B21'

You notice that it is not defined active workbook, Workbook is determines with the hash sign a worksheet formula sets (green letters) that is connected with the name of the worksheet and cell 'A1'.

The formula is:
=HYPERLINK(CONCATENATE("#";B21;"!";"A1");"Link to Sheet3 on the A1 cell provided from B21")

 

Generally it is the HYPERLINK formulas that can seem like a formula below.

=HYPERLINK("#"&"'"&MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256)&"'!"&"A1";"LinkName")
=HYPERLINK("#"&"'"&MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256)&"'!"&"CellAddressPosition";"LinkName")

 

The formula below uses the defined name of a specific formula

=HYPERLINK(MySheet&ADDRESS(MATCH(address cell in which there is a condition;$A$1:$A$100;0);1);address cell in which there is a condition&" tekst-link ")

In the Name Manager is necessary to define the name of 'MySheet'.

In the 'Refers to':
=MID(CELL("filename";A!$A$1);FIND("[";CELL("filename";A!$A$1));256)&"!"

 

Excel cell A23 in the image above:

Hyperlink in which the sign of the scale (sharp, pound or hash) determines the active workbook, worksheet directly define the merger of text, as well as a destination cell 'D7' which specifies the address of the cell.

The result of the formula, the Hyperlink text on the worksheet 'Sheet2' in cell 'D7' in the active workbook:

Link to 'Sheet2' in cell 'D7' same Workbook

You notice that it is not defined active workbook, but it determines the hash sign a worksheet sets with term or string (green letters) that is connected to the name of the worksheet.

The formula is:
=HYPERLINK("#"&"'"&"Sheet2"&"'!"&"D7";"Link to Sheet2 in cell D7 same Workbook")

 

Excel cell A24 in the image above:

Hyperlink in which the character hash determines the active workbook, worksheet directly via defined conditions in cell 'B24', and a destination cell 'D7' put address of Excel cells.

The result of the formula, the Hyperlink text on the worksheet 'Sheet2' in cell 'D7' in the active workbook:

Link to 'Sheet2' in cell 'D7' same Workbook provided in 'B24'

You notice that it is not defined active workbook, but it determines the hash sign a worksheet sets with term or string (green letters) that is connected with the name of the worksheet from the 'B24'.

The formula is:

=HYPERLINK("#"&"'"&B24&"'!"&"D7";"Link to Sheet2 in cell D7 same Workbook provided in B24")

 

Excel cell A25 in the image above:

Hyperlink in which the character hash determines the active workbook, worksheet is defined via Excel functions INDIRECT making it a condition of the cell 'B25', and a destination cell 'D7' put address of Excel cells. (see previous tutorial to function INDIRECT)

The result of the formula, Hyperlink text on the worksheet 'Sheet2' in cell 'D7' in the active workbook:

Link in cell D7 on Sheet2 in the same Workbook whose name appears in the 'B25' as a condition

You notice that it is not defined active workbook, but it determines the hash sign a worksheet sets with term or string (green letters) that is connected with the name of the worksheet from the 'B25'.

The formula is:

=HYPERLINK("#"&"'"&INDIRECT("B25")&"'!"&"D7";"Link in cell D7 on the sheet in the same Workbook whose name appears in the B25 as a condition")

Excel cell A27 in the image above:

The name 'MySheet' determines the active workbook and worksheet, the target cell is determined via Excel functions ADDRESS / MATCH making it a condition of 'B27' cells (see previous tutorial for this formula and the result returned).

The result of the formula, Hyperlink text on the worksheet "Sheet1" in the first cell that contains the text or string 'XYZ' in the active workbook:

XYZ Link

The formula is:
=HYPERLINK(MySheet&ADDRESS(MATCH(B27;$A$1:$A$102;0);1);B27&" Link")

 

In the Name Manager must define a new name 'MySheets'.

In the Refers to:
=MID(CELL("filename";Sheet1!$A$1);FIND("[";CELL("filename";Sheet1!$A$1));256)&"!"

 How to define a new name in Excel Name Manager

Generally it is the HYPERLINK formulas that can seem like a formula below.

=HYPERLINK("#"&"'"&"AB"&"'!"&"D7";"Link to Sheet AB in cell D7 same Workbook")
=HYPERLINK("#"&"'"&"SheetName"&"'!"&"CellAddressPosition";"LinkName")
=HYPERLINK("#"&"'"&INDIRECT("A33")&"'!"&"D7";"the name of the worksheet AB is in cell A33 and link in cell D7 SheetAB same Workbook")
=HYPERLINK(MySheet&target_cell;condition&" text ")