Displaying a Worksheet from a Drop-Down List Choice

Mark knows he can use data validation to create a drop-down list. What he doesn't know is how to use the drop-down list in conjunction with the HYPERLINK function so the user can jump to a particular worksheet. In other words, they pick something in the drop-down list, then click on a link that says "Go to Report," and then the report they selected in the drop-down list is displayed. Mark wonders if there is a way to represent the report's worksheet (the target) in the drop-down list, as well as the name displayed in the drop-down list.

For Mark's purposes, I'm going to assume that the reports are contained on other worksheets in the current workbook. In that case, it would be good to define a small table that contains three columns. The first column contains the text you want in the validation list, perhaps the name of the reports. The second column is the name of the target worksheet containing the report, and the third column is the target cell within the worksheet. (See Figure 1.)

Figure 1. Setting up table information.

Notice that the table doesn't really need any column headings. I will, however, select the cells and give them a name. In this case, I'll use the name JumpTable. Using three columns in the table allows flexibility in specifying destination worksheets and cells. In fact, over time you can make any changes in the table desired, and your hyperlink will still work just fine.

Speaking of the hyperlink, the next step is to use data validation to set up a drop-down list. Select cell F1 and then, on the Data tab of the ribbon, click the Data Validation tool. Excel displays the Data Validation dialog box. Using the Allow drop-down list, choose List, and then enter this formula in the Source box:

=INDEX(JumpTable,0,1)

Click OK, and your drop-down list, in cell F1, is now populated with the values in the first column of the JumpTable range. Now you can combine this selection with the HYPERLINK function in the following manner:

=HYPERLINK("#"&TEXTJOIN("!",, XLOOKUP(F1, CHOOSECOLS(JumpTable,1), CHOOSECOLS(JumpTable,{2,3}))),"Go to Report")

This is a long formula, but it works because the TEXTJOIN function returns a combination of cells from the second and third columns of JumpTable based on a match in the first column. The way the formula is set up assumes that the worksheet name (in the second column) is only a single word. If the names contained in the second column contain spaces, then you'll want to modify the formula to add apostrophes around what is pulled from the second column:

=HYPERLINK(LET(r, XLOOKUP(F1, CHOOSECOLS(JumpTable,1), CHOOSECOLS(JumpTable,{2,3})), "#'" & INDEX(r,1,1) & "'!" & INDEX(r,1,2)),"Go to Report")

These formulas, though, will only work if you are using Excel 2021, Excel 2024, or Microsoft 365. If you are using an earlier version of the program, then you'll need to rely on older lookup functions:

=HYPERLINK("#" & IFERROR("#'" & VLOOKUP(F1,JumpTable,2,FALSE) & "'!" & VLOOKUP(F1,JumpTable,3,FALSE),""),"Go to Report")

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9424) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.