Assignmentgeek.com - makes your homework shine. Continued views tutorials from topic HYPERLINK in Excel.

## HYPERLINK with One Condition or Criteria 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. 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 The formula in column 'B' is the following:

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 '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 (Condition or Criteria) 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 save as *.xls or *.xlsm. 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 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()));"") (source: Krishnakumar)

## View the Formula in 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' ### 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. In the picture below you can see the range of data that I have named as 'Sheet3Range' => '\$A\$2:\$A\$10'. In the picture below you can see the range of data that I have named as 'Sheet5Range' => '\$A\$2:\$A\$10'. In the previous three mentioned worksheets I have set Hyperlink '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:

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 (This CSE 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 and higher

=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 Finally formula that automatically creates a hyperlink according to the two preceding conditions in columns A and B of the following:

For Excel 2003

For Excel 2007, 2010, 2013 and higher

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 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