Vlookup using Hyperlink

0

From a drop down list (Jan to Dec) I am trying to hyperlink to monthly sheets in the same workbook.  Tried a vlookup but will not return the hyperlink location. Any assistance would be appreciated.

Answer
Discuss

Discussion

What do you mean by "hyperlink to monthly sheets"? If you wish to select a month from the dropdown and have that month's sheet open up you need VBA. You can use VLOOKUP to import specific data from the sheet selected in the dropdown into the sheet of the dropdown and make those data change as you flip through the dropdown list.
To supply an answer to either problem more detail is needed, most likely, access to the workbook itself.
Variatus (rep: 1433) Aug 31, '17 at 5:19 am
Thanks for the reply. I have sheets named by month. I wish to place an option for the user to select the relevant month using a drop down sheet to select from. This in turn will vlookup the month and return with a hyperlink address.
ACilia2017 Aug 31, '17 at 8:56 am
Add to Discussion

Answers

0

Why not try some Anchors instead?  They're a lot like hyperlinks...see attached file.  I use shortcut Ctrl + K, then use 'Place in this document' to insert reference to each tab.  I also place a 'HOME' link to go back to first tab. Feel free to modify to suit your needs.  I haven't tried it, but you may be able to make a Combo or list box out of it too?

Discuss
0

Install the following code on the code sheet of the worksheet on which you want to have your dropdown. Then save the workbook as macro enabled (XLSM format) workbook. Be sure to specify the cell in which you want your dropdown in the second line of the code below.

Option Explicit
    ' specify the cell to contain the validation:
    Const DropDownAddress As String = "C3"
    ' the following list must contain the names of your worksheets
    ' as shown on their tabs, comma-separated (not case sensitive):
    Const DropList As String = "Jan,Feb,Mar,Apr,May,Jun," & _
                               "Jul,Aug,Sep,Oct,Nov,Dec"
    
Private Sub Worksheet_Activate()
    ' 01 Sep 2017
    
    Dim n As Long
    
    With ActiveSheet.Range(DropDownAddress)
        On Error Resume Next
        n = .SpecialCells(xlCellTypeSameValidation).Count
        If Err Then
            n = 1                       ' there is no validation
        Else
            ' the list has changed:
            n = StrComp(.Formula1, DropList, vbTextCompare)
        End If
        
        ' reset the validation if the list has changed
        ' or if there was no validation:
        If n Then
            With .Validation
                On Error Resume Next
                .Delete
                On Error GoTo 0
                .Add Type:=xlValidateList, _
                     AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, _
                     Formula1:=DropList
                .InCellDropdown = True
                ' to suppress an input message set its value to ""
                .InputTitle = "Select"
                .InputMessage = "Choose a sheet to open"
                .IgnoreBlank = True
                .ShowError = True
                .ErrorTitle = "Invalid entry"
                .ErrorMessage = "Please select a month from the list"
            End With
            Application.EnableEvents = False
            .Value = Split(DropList, ",")(0)
            Application.EnableEvents = True
        End If
    End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 01 Sep 2017
    
    With Target
        If .Address = Range(DropDownAddress).Address Then
            On Error Resume Next
            Worksheets(.Value).Activate
            If Err Then
                MsgBox "There is no sheet by the name of """ & .Value & """.", _
                       vbInformation, "Missing worksheet"
            Else
                ' you can choose any cell to activate
                ' or delete this line of code entirely
                  ' (along with the preceding "Else"):
                ActiveSheet.Cells(3, "A").Select
            End If
        End If
    End With
End Sub

To find the correct code sheet press Alt+F11. That opens the VB Editor. On the left top you should see the 'Project Explorer' window with the name of your workbook listed as "VBAPrtoject ([Name of workbook])". If this window isn't open press Ctl+R to open it. If you don't see all of your worksheets listed click on the + signs next to the "VBAProject" and "Microsoft Excel Objects" to open the list. Double-click on the name of the worksheet you want to have your dropdown on. That name will now be underlaid light grey (to show that it is selected), and you can paste the code in the empty pane on the top right of your screen. If the pane isn't empty, make sure that "Option Explicit" appears only once after you paste the code.

The code has two parameters at the top which you must set in accordance with the instructions written next to them. It also has two procedures, both of them so-called event procedures, meaning they will run automatically when something happens in your workbook.

The first procedure will run every time you select the sheet with the dropdown. It will install the dropdown in its designated cell. Never try to amend the dropdown in the cell. Make modifications in the code. To force the code to update your validation, delete the validation in the cell, select another sheet and then select the one with the dropdown.

The second procedure runs every time you make any change on that sheet. If the change was in the dropdown it will open the worksheet specified in the dropdown. If a worksheet by that name doesn't exist it will tell you that there is no such sheet.

Discuss

Discussion

Variatus - nice way to 'Play' off of my ideas....this isn't the first time!
queue (rep: 353) Sep 1, '17 at 9:02 am
Great minds think alike, and there are many ways to skin a cat. If you go one way I will try to find a different method. In this way we can both enrich the experience visitors to this forum have. Rest assured that I will never play off your ideas. It isn't what I do, and it isn't what I have done, here or elsewhere.
Variatus (rep: 1433) Sep 1, '17 at 10:51 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login