Tutorial with examples of creating a hyperlink in Exel in different situations, formulas that are used to create a hyperlink in Excel

## HYPERLINK

Sintax function of Hyperlink in Excel

the path and file name of the document to be opened using a link. Link_location may refer to the place in a document - such as a specific cell or a named range in an Excel worksheet or workbook, or the bookmark in the document. The path can be to files stored on a disk or can be a way that respects the Universal Naming Convention UNC on the server (in Microsoft Excel for Windows) or URL (Uniform Resource Locator (URL):

Link_location can be a text string included in quotation marks, or a cell that contains the link as a text string.

If the link listed in link_location there is to it, or can not come, there is a fault when you click on the cell.

The link text or numeric value that is displayed in the cell. Friendly_name is shown in blue and underlined. If friendly_name not specified, the cell displays link_location as link text.

Friendly_name can be a value, text string, name, or cells containing text links or value.

If friendly_name returns the error value (eg. #VALUE!), Cell displays the error instead of text links.

Before I go on formula for different variants of a hyperlink in Excel want to show you some Excel formula by which we can get some results for certain actions. See the image below and read it.

HYPERLINK(link_location;friendly_name)HYPERLINK(link_location;friendly_name)

**Link_location**the path and file name of the document to be opened using a link. Link_location may refer to the place in a document - such as a specific cell or a named range in an Excel worksheet or workbook, or the bookmark in the document. The path can be to files stored on a disk or can be a way that respects the Universal Naming Convention UNC on the server (in Microsoft Excel for Windows) or URL (Uniform Resource Locator (URL):

Link_location can be a text string included in quotation marks, or a cell that contains the link as a text string.

If the link listed in link_location there is to it, or can not come, there is a fault when you click on the cell.

**Friendly_name**The link text or numeric value that is displayed in the cell. Friendly_name is shown in blue and underlined. If friendly_name not specified, the cell displays link_location as link text.

Friendly_name can be a value, text string, name, or cells containing text links or value.

If friendly_name returns the error value (eg. #VALUE!), Cell displays the error instead of text links.

Before I go on formula for different variants of a hyperlink in Excel want to show you some Excel formula by which we can get some results for certain actions. See the image below and read it.

In the image above, there are some good results in the first eleven rows obtained formulas in columns 'B and C' are possible conditions if they are to set up a formula that will show the following:

Excel cell A2 in the image above:

The result of the formula is the trajectory or path to the active worksheet:

The formula is: =CELL("filename";Sheet1!A1)

Excel cell A3 in the image above:

The result of the formula is the name of the workbook and the active worksheet:

The formula is ARRAY: formulas must be in yen row or one line

=RIGHT(CELL("filename");LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\";CELL("filename"); ROW(1:260))));SEARCH("\";CELL("filename");ROW(1:260)))))

Excel cell A4 in the image above:

The result of the formula is the same as the previous one, the name of the workbook and the active worksheet, only the formula is performed differently:

The formula is: =MID(CELL("filename";Sheet1!$A$1);FIND("[";CELL("filename";Sheet1!$A$1));256)&"!"

Excel cell A5 in the image above:

The result of the formula is the name of the active worksheet:

The formula is: =MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256)

Excel cell A6 in the image above:

The result of the formula is the same as the previous one, the name of the active worksheet, only the formula is different:

The formula is: =RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-SEARCH("]";CELL("filename";A1)))

Excel cell A7 in the image above:

You notice that there are two conditions in the cells 'B7' and 'C7', Based on the conditions of the result of the formula is the worksheet name and address of the cell on it. Text in the 'B7' can be anything:

The result of the formula is:

The formula is: =B7&"!"&ADDRESS(MATCH(C7;Sheet2!$A$1:$A$100;0);1)

Excel cell A8 in the image above:

You notice that there is no requirement in the cell 'B8', Based on conditions result formula is the address of the cell in the worksheet that we defined in the formula:

The result of the formula is:

The formula is: =ADDRESS(MATCH(B8;Sheet2!$A$1:$A$10;0);1)

Excel cell A9 in the image above:

You notice the row and column of this formula. The formula returns the absolute address of the cell where the formula is:

The formula is: =ADDRESS(ROW();1)

Excel cell A10 in the image above:

You note that the condition of the cell 'B10'. The INDIRECT function is often used in formulas that create hyperlinks in Excel because it fixes a specific text string or for further manipulation:

The result of the formula is:

The formula is: =INDIRECT("B"&10))

Excel cell A11 in the image above:

You note that the condition of the cell 'B11'. Function INDIRECT also returns the desired result and that is the absolute address of the cell in which there is 'name' brands on the 'worksheet 2' which is set as a condition in the 'B11' and is located in the cell 'A2'

The result of the formula is:

The formula is: =INDIRECT(B11&"!A2")

On the next page tutorials, see the formula that created the Hyperlink to specific examples from the image.

To continue views tutorials click NEXT button.

Excel cell A2 in the image above:

The result of the formula is the trajectory or path to the active worksheet:

**C:\Users\User\tutorials\[Book1.xlsx]Sheet1**The formula is: =CELL("filename";Sheet1!A1)

Excel cell A3 in the image above:

The result of the formula is the name of the workbook and the active worksheet:

**[Book1.xlsx]Sheet1**The formula is ARRAY: formulas must be in yen row or one line

=RIGHT(CELL("filename");LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\";CELL("filename"); ROW(1:260))));SEARCH("\";CELL("filename");ROW(1:260)))))

Excel cell A4 in the image above:

The result of the formula is the same as the previous one, the name of the workbook and the active worksheet, only the formula is performed differently:

**[Book1.xlsx]Sheet1!**The formula is: =MID(CELL("filename";Sheet1!$A$1);FIND("[";CELL("filename";Sheet1!$A$1));256)&"!"

Excel cell A5 in the image above:

The result of the formula is the name of the active worksheet:

**Sheet1**The formula is: =MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256)

Excel cell A6 in the image above:

The result of the formula is the same as the previous one, the name of the active worksheet, only the formula is different:

**Sheet1**The formula is: =RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-SEARCH("]";CELL("filename";A1)))

Excel cell A7 in the image above:

You notice that there are two conditions in the cells 'B7' and 'C7', Based on the conditions of the result of the formula is the worksheet name and address of the cell on it. Text in the 'B7' can be anything:

The result of the formula is:

**Sheet2!$A$4**The formula is: =B7&"!"&ADDRESS(MATCH(C7;Sheet2!$A$1:$A$100;0);1)

Excel cell A8 in the image above:

You notice that there is no requirement in the cell 'B8', Based on conditions result formula is the address of the cell in the worksheet that we defined in the formula:

The result of the formula is:

**$A$4**The formula is: =ADDRESS(MATCH(B8;Sheet2!$A$1:$A$10;0);1)

Excel cell A9 in the image above:

You notice the row and column of this formula. The formula returns the absolute address of the cell where the formula is:

**$A$9**The formula is: =ADDRESS(ROW();1)

Excel cell A10 in the image above:

You note that the condition of the cell 'B10'. The INDIRECT function is often used in formulas that create hyperlinks in Excel because it fixes a specific text string or for further manipulation:

The result of the formula is:

**$A$9**The formula is: =INDIRECT("B"&10))

Excel cell A11 in the image above:

You note that the condition of the cell 'B11'. Function INDIRECT also returns the desired result and that is the absolute address of the cell in which there is 'name' brands on the 'worksheet 2' which is set as a condition in the 'B11' and is located in the cell 'A2'

The result of the formula is:

**MarkoM**The formula is: =INDIRECT(B11&"!A2")

On the next page tutorials, see the formula that created the Hyperlink to specific examples from the image.

To continue views tutorials click NEXT button.