Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Hyperlink in Excel with conditions

Continued views tutorials for topic HYPERLINK in Excel.

HYPERLINK with one condition in Excel

In Excel, you can create a Hyperlink in many ways for more need Excel calculations. One way of creating a link is a link to a destination cell in the same workbook to another worksheet or provided in a cell.

In the picture below you see a workbook that contains two sheets.

Worksheet 'Sheet2' contains data in column 'A' have some information whether it is text, number or a value. Based on the data in column 'A' we further calculations. On the worksheet "Sheet1 'we want to enter data that is identical to the data in column' A 'and that our Excel automatically creates a Hyperlink that will position us to the destination or target cell.

Creating a link to another worksheet in the same workbook Excel
On the first worksheet "Sheet1' in column 'A' you note the cell in which we enter some information and it is our condition to which it relates link in the column' B '. After entering the data in column 'A' we want to Excel automatically creates a Hyperlink to 'Sheet2' to a destination cell that contains the data entered in column 'A'. This condition in column 'A' can be directly entered or selected via the drop-down list (drop down menu) via Data Validation.

Hyperlink with one condition the same workbook in Excel

The formula in column 'B' is the following

=IF(A2="";"";HYPERLINK("["&MID(CELL("filename");SEARCH("[";CELL("filename"))+1; SEARCH("]";CELL("filename"))-SEARCH("[";CELL("filename"))-1)&"]'Sheet2'!A"&MATCH(A2;Sheet2!$A$2:$A$100;0)+1;"Link"))


If you look at the Evaluate Formula then you will see that the formula does the order following the operation. For more details and explanations, see the previous tutorial related to the topic Hyperlink.

Excel IF function compares the state of the cell that is the condition (in this case it is Excel cells with A2)
Excel Hyperlink function as a result of returning the name of the workbook and merges with the name of the worksheet (which we defined in the formula)
Excel function Match compares the data conditions from the cell 'A2' and the range of data on 'Sheet2' range '$A$2:$A$100' and as a result returns the number of rows it finds identical data. Number +1 behind the tool Match determines the line containing the first cell range of data. So, if you have data on 'Sheet2' are in the range of data '$A$50:$A$100' this particular number will be +49.
Finally summary; After entering the data (our condition) in cell 'A2' Excel creates a Hyperlink. Click on the respective hyperlink Excel automatically positions us to 'Sheet2' in the cell containing the same data (our condition).

HYPERLINK with two conditions Excel

In the following example below will show how we can use Excel functions to create a Hyperlink if we have two conditions. In the tutorial, I show two versions of formulas because they are related to the format of Excel files (versions of Excel 2003 and Excel 2007 to 2013)

In the example below the workbook has the following worksheets:
  • Worksheet 'base': Worksheet on which there is automatically a list of all the worksheets contained in the workbook
  • Worksheet 'LINKS': Worksheet where we have two conditions by which we create a hyperlink to the other worksheets, depending on conditions
  • Worksheet Sheet2, Sheet3, Sheet5: Worksheets that contain data with calculations on that link by clicking the link to the worksheet 'LINKS'. The objective is to click on the link is automatically positioned to the destination cell that contains identical data.

Making a list of all the worksheets in the workbook by using the formula

Worksheet 'base' contains a list, or a list of all the worksheets contained in the workbook. List of all the worksheets can be created using a VBA macro. This list is created using the formula.

The picture below note named range data 'SheetList' which is in the range 'A4: A19. When creating a new worksheet in the active workbook This list is updated automatically. Also note that the first two worksheets from the list not used for further calculations (red letters).
NOTE: File must be saved as *.xls or *.xlsm.

List of all the worksheets in an Excel workbook
The cell 'A2' contains a formula that is copied to a certain row down.

=IF(ROW(A2)-ROW($A$2)+1<=COUNTA(All_Sheets);INDEX(All_Sheets;ROW(A2)-ROW($A$2)+1);"")

For this, the formula above is required to function in the Name Manager to create the additional formula which is linked to the respective above formula. Click on "Formulas => Name Manager => New" and type in the following fields

Name: All_Sheets

Refers to:
=REPLACE(GET.WORKBOOK(1)&T(NOW());1;FIND("]";GET.WORKBOOK(1)&T(NOW()));"") (autor: Krishnakumar)

View the Name Manager in Excel

In the Name Manager in the picture below you will see all the named ranges of data.
  • All_Sheets: see the formula above
  • Sheet2Range: range of data on 'Sheet2' => '$A$2:$A$10'
  • Sheet3Range: range of data on 'Sheet3' => '$A$2:$A$10'
  • Sheet5Range: range of data on 'Sheet5' => '$A$2:$A$10'
  • SheetList: range of data in the worksheet 'base' => '$A$4:$A$19'
Name Manager in Excel and created formulas

Worksheets on to make a hyperlink and position itself in the cell after clicking

Worksheets Sheet2, Sheet3 and Sheet5 are destination worksheets in Excel that we should automatically position itself according to which we hyperlink clicked on the worksheet LINKS.

You notice the range of data that I have named as 'Sheet2Range' => '$A$2:$A$10'. You ask why I such a name for the range of data. Because that would be easier for me merger the conditions, however you will see explanation below tutorials.

Destination worksheet via hyperlinks

In the picture below you can see the range of data that I have named as 'Sheet3Range' => '$A$2:$A$10'.

How to create a hyperlink to another worksheet of the same workbook in Excel

In the picture below you can see the range of data that I have named as 'Sheet5Range' => '$A$2:$A$10'.

How to create a hyperlink if you have a condition

In the previous three mentioned worksheets I have set Hyper link 'Home' which refers to the starting position and that is the worksheet LINKS. The formula in the 'F1' link 'Home' on all three sheets is as follows:
= HYPERLINK ("#"&CELL("address";LINKS!A1),"Home")

Hash sign (pound or sharp) determines the active workbook.

The hyperlink with the two conditions in Excel

Now we come to the most important worksheet in which we create a hyperlink depending on two conditions. In the picture below you see the situation on the worksheet 'LINKS'. This is our worksheet on which we want to automatically create a hyperlink to another worksheet (the second condition - Column B) exactly to the destination cell that contains the same data (the first condition - column A).

So, in the column 'A', we input the name, number or some data

In the column 'B' Excel automatically as a result of the formula returns the name of the worksheet provided in column 'A'. ARRAY formula in cell 'B2' which is copied down as follows (formula ends with a CTRL+SHIFT+ENTER):

For Excel 2003

=IF(ISERROR(VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$B$10");A2)>0);0))&"'!$A$1:$B$10");2;FALSE));"";VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$B$10");A2)>0);0))&"'!$A$1:$B$10");2;FALSE))

For Excel 2007, 2010, 2013

=IFERROR(VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$B$100");A2)>0);0))&"'!$A$1:$B$100");2;FALSE);"")

The starting position, are empty cells range of data in the image marked border Excel cells. In the column 'A', we input data. When you enter a unique fact that we know that he is on one of the worksheets (there may be dozens) of formulas in column 'B' (see above) automatically displays the name of the worksheet on which the respective data is unique. The formula in column 'C' automatically creates a hyperlink on the basis of data in columns 'A and B'.
Small summary: When entering the 'A2' cell data 'John Doe' Excel automatically displays the name of the worksheet where the data is concerned and 'C2' cell creates a hyperlink to a destination cell in the worksheet names from 'B2' cells.
  • Column 'A': Entering a unique data
  • The column 'B': Automatically display a worksheet that contains the data in column A in the same row
  • Column 'C': Automatically create a hyperlink on the basis of data in columns A and B
The hyperlink with the two conditions in Excel

Finally formula that automatically creates a hyperlink according to the two preceding conditions in columns A and B of the following:

For Excel 2003

=IF(ISERROR(HYPERLINK("#"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link"));"";HYPERLINK("#"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link"))

For Excel 2007, 2010, 2013

=IFERROR(HYPERLINK("#"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link");"")

Now I can explain to you why I'm on the worksheet Sheet2, Sheet3 and Sheet5 named ranges of data as "Sheet?Range" You notice in the formulas above nested function INDIRECT(B2&"Range"). Within a function Indirect combine data from cells 'B2' and the text 'Range'. In this way it is possible to copy the formula down because copying the address change cell B2 so that copying down as a result of a back-appointed name for the range of data (Sheet2Range, Sheet3Range, Sheet5Range).

Instead of the above formulas can also use the following formula to create a hyperlink with the two conditions in Excel within the active workbook.
Note: IFERROR function using Excel 2007 and later. For Excel 2003 to use the IF / ISERROR.

Alternative ARRAY formula in column 'B' to restore the name of the worksheet in relation to the condition set out in column 'A' is as follows

=VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$B$10");A2)>0);0))&"'!$A$1:$B$10");{2\3};FALSE) (Jerry Beaucaires)

Alternative formulas in column 'C' to create a hyperlink in relation to the conditions set out in column 'A and B' may be next

=IFERROR(HYPERLINK("[Book1.xlsm]"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link");"")

NOTE: If you copy the formula from this website tutorials then note that the formula is in a single row or a single line.