Now you have the hyperlink-list on one sheet. In this case no evaluation happens, the hyperlink will see the range reference and will navigate to the selected sheet. But in our case we give it to the hyperlink as named formula. When you enter the above formula into a cell, Excel evaluates immediately and gives back the value stored in the cell. 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").Īnd here comes INDIRECT again, who is able to convert a string to a real range reference. 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. The whole thing will look as if you select the hyperlink itself from the validation list! How to create a structure to navigate between worksheets in a workbook
#Link to excel zerodha pi showing na values how to#
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. 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).Ī very interesting use is to combine this kind of hyperlink with validation list in a cell. This technique is an excellent alternative to the HYPERLINK function.
0 kommentar(er)
