Excel - Hyperlink to a dynamic range Introduction It is very easy to use a hyperlink to navigate within a workbook. Open the dialog Insert Hyperlink and select the button Place in This Document. A treeview allows you to choose a worksheet and type a cell reference but also displays all the names related directly to a range. However names that return a reference through a formula are not displayed and can not be used directly in hyperlink. How add hyperlink to a dynamic range As in many other cases we can cheat the system security of Excel. In this case it is enough to change the name after it was added to the hyperlink. Here are the steps to follow: 1. Create a name refers to a range 2. Add this name to the hyperlink 3. Change the reference in the name to the formula you need This technique is an excellent alternative to the HYPERLINK function. In parentheses: with this technique it is also possible to use a name to change the value of another cell (see the link to our post). A very interesting use is to combine this kind of hyperlink with validation list in a cell. In the below example we would like to illustrate how to create, in a few simple steps, a structure to navigate between worksheets in a workbook - of course without using VBA. How to create a structure to navigate between worksheets in a workbook The whole thing will look as if you select the hyperlink itself from the validation list! First of all, you will need a list with the sheet names - write them somewhere on a sheet and assign a name “Sheet_list” to this range: When you have this name, create a validation with list on the cell you want to have the hyperlink. Important, that this cell must be exactly the same cell on all worksheets. In the example we use cell B1: Now you should create a name which will feed the hyperlink. First create a name only: “My_Sheet_Choice” with reference to the active cell - it will be changed later. The next step is to set up the hyperlink using the name created above. After right-click on the cell, choose Hyperlink, then click on “Place in This Document”. Here, under Defined Names you will find “My_Sheet_Choice” - that is what you need to select. And here comes the trick - change the definition of “My_Sheet_Choice” to give back a reference to the sheet you selected from the list! In the Name Manager enter this formula to the Refers to field of “My_Sheet_Choice”: =INDIRECT(ADDRESS(1,2,,,INDIRECT(ADDRESS(1,2)))) (Please note, this formula will work only if you use B1 for the validation list. If you put the list to another cell, please change accordingly: the first parameter of ADDRESS is the row number of your cell, the second is the column number!) How does this formula work? The nested INDIRECT(ADDRESS(1,2)) reads the name you selected in B1 and gives it to the enclosing ADDRESS formula as 5th parameter = sheet name. ADDRESS combines the sheet name and the cell referenced by row and column numbers and results a syntactically correct reference as string (adds the apostrophes (‘) if needed and the exclamation mark (!) after the sheet name, so it will look like, for example: New York!$B$1). And here comes INDIRECT again, who is able to convert a string to a real range reference. When you enter the above formula into a cell, Excel evaluates immediately and gives back the value stored in the cell. But in our case we give it to the hyperlink as named formula. In this case no evaluation happens, the hyperlink will see the range reference and will navigate to the selected sheet. Now you have the hyperlink-list on one sheet. Copy it to all of your sheets into exactly the same range - the easiest way to do it is to select all the sheets (group) and paste on one sheet - it will paste to all the selected sheets.
Posted on: Mon, 24 Nov 2014 02:35:44 +0000
Trending Topics
Recently Viewed Topics
© 2015