Displaying a Worksheet from a Drop-Down List Choice

Gonzonator1982:
Thank you for your reply. I did not know that and there is no mention of this in the Microsoft help for the HYPERLINK function in Excel.
So I learned something new. And I thought I knew Excel - I have been using it for about 30 years.

Tomek, the Hash "#" tells the hyperlink formula to look inside the document for the hyperlink; without it, the hyperlink looks out to the web by default.

As I mentioned in my earlier comment, both the solution from Allen's tip, and my alternate macro-based approach may not properly display the requested report. If the range covering a particular report is away from left and/or top of the sheet, selecting the cell/range may display it far to the right/down with large portion of it hidden outside the visible part of the sheet. To avoid this, for the hyperlink solution, you can add a simple macro to each target sheet code:

Private Sub Worksheet_Activate()
Application.Goto Selection, Scroll:=True
End Sub

When the hyperlink is clicked, it will select the desired range on the target. Upon activating the target sheet, the screen will scroll to display the active cell in the top corner. And yes, you can pass a range of cells via hyperlink, not only a single cell. (see Figure 1 below) The limitation of this is that the report cannot be on the same sheet as the hyperlink, as this sheet is already active.

For the macro solution a slight modification will do the same. To make it more user friendly, I added the second window, in which the report will be displayed. So you will have two windows for the same file, one for the Table of Contents, and one for the report. Closing the second window after report is reviewed/printed will simply get you back to the Table of Contents.

As I said in my earlier comment, the macro approach saves you some clicks - only two are required for selecting from the drop-down list vs. four for the hyperlink approach. As I always say, there are many ways to skin a catfish (not a cat - I love cats), but some are better than others.

=========
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F1")) Is Nothing Then
        ActiveWindow.NewWindow
        sht = Range("G1").Value
        cel = Range("H1").Value
        Sheets(sht).Activate
        ActiveSheet.Range(cel).Select
        Application.Goto , True
    End If
    
End Sub
====================

@Peter
Your formula does not work

I have used a jump table similar to this just using a macro, but found that if the target worksheet was edited the target cell often moved. To avoid ending up in the wrong cell, taking the last row of the example, I would refer to the target cell with the following formula in column C
=CELL("address",'My Assets'W15).

Further to my earlier comment:
You may not need to go to a specific target cell, just activate the sheet with the requested report.
In such case you can omit the entry in the cell H1, as well as two lines of the code making it simpler:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("F1")) Is Nothing Then
        sht = Range("G1").Value
        Sheets(sht).Activate
    End If

End Sub
=================
In any case you may want to make sure that the relevant area of sheet that opens is properly positioned in the Excel Window; just selecting a particular cell does not necessarily position it in the upper left corner of the window. This applies also to the hyperlink method. How to get the proper view of the sheet with the report when it opens is a whole different story, and may be a subject of a future tip, if Allen agrees.

The method proposed in the tip works well and is exactly what Mark asked for, but it requires four or three clicks: select the cell with the multiple choice (unless already selected), click on the down arrow, select one of options from the drop-down list, then click the hyperlink in another cell. I think the last click is not necessary, hence the use of the hyperlink is also not necessary. On the other hand, my suggestion is to use a macro that some users may not not like or even be able to do (company policies).
I suggest the macro be triggered by a change in the cell with the drop-down list.
The setup is the same as in the tip, up to the point where the validation list is created in the cell F1.
Instead of creating a hyperlink in a nearby cell, use VLOOKUP based on JumpTable to get the sheet name and the target cell into the neighbouring cells G1 and H1, respectively:
=VLOOKUP(F1,JumpTable,2,FALSE)
=VLOOKUP(F1,JumpTable,3,FALSE)
(see Figure 1 below)
You could also use XLOOKUP for this purpose. Please note that for simplicity I made sure that sheet names have no spaces.
Once you have all this in place add the following macro to your "Table of Contents" sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("F1")) Is Nothing Then
        sht = Range("G1").Value
        cel = Range("H1").Value
        Sheets(sht).Activate
        ActiveSheet.Range(cel).Select
        
    End If
    
End Sub

What is the purpose of the "#" in the formula:
=HYPERLINK("#"&TEXTJOIN("!",, XLOOKUP(F1, CHOOSECOLS(JumpTable,1), CHOOSECOLS(JumpTable,{2,3}))),"Go to Report")

I know it is important, because it does not work without it, but I have no idea why.