Hyperlink in custom list drop-down list under data validation

Not sure you can hyperlink directly in Drop-Down lists.

Couple of alternatives which you may want to consider;


There is a handy sheet-selector build in. 'Right-Click' in the sheet navigation box at the left-bottom corner. You get a list with all sheets. Click on the one you want to navigate to... (see screen-shot).



Another option (sample file attached) with a 'ComboBox' used instead of the Drop-down list, then a Command Button to get to the selected sheet.

The ComboBox will be refreshed each time you select the sheet where the ComboBox is, so you always have a list with all current sheets.
Select the sheet you want, then click the command button.


The code below goes into the Sheet where the ComboBox is (not a standard module);

Make sure the name of the ComboBox and CommandButtons in the code match your file names.

Option Explicit

Private Sub Worksheet_Activate()
' When selecting this sheet, All Sheet Nsames in the workbook
' will be added into the ComboBox1
    Dim ws As Worksheet
    
' Clear ComboBox so we get as fresh up-to-date-list
    ComboBox1.Clear
    
' Loop through sheets and add sheet-names in ComboBox
    For Each ws In Worksheets
        ComboBox1.AddItem ws.Name
    Next ws
End Sub


Private Sub CommandButton1_Click()
' Read the value of the ComboBox1 and go to that sheet

Dim mySheet As Worksheet
    For Each mySheet In ActiveWorkbook.Worksheets
        If mySheet.Name = ComboBox1 Then
            mySheet.Select
            Exit For
        End If
    Next mySheet

End Sub

Finally, There is a link which may give you some ideas.
There is also a link at the top of the article to download the sample workbook.

http://www.atlaspm.com/toms-tutorial...rop-down-list/

Here is another sample. Please refer to the attached file.

Whenever the 'Index Sheet' is activated, a list of all sheets is listed in column A (plus a hyperlink to that sheet in column B, if this is not necessary, just remove that line from the code).

Option Explicit

Private Sub Worksheet_Activate()

Dim ws As Worksheet
Dim x As Integer
    x = 1
    Sheets("Index Sheet").Range("A:A").Clear
        For Each ws In Worksheets
             Sheets("Index Sheet").Cells(x, 1) = ws.Name
             Sheets("Index Sheet").Cells(x, 2).Formula = "=HYPERLINK(""#'""&RC[-1]&""'!A1"",""Go To Sheet ""&RC[-1])"
             x = x + 1
        Next ws
        
End Sub

We then have a Defined Named Range which lists the number of sheets listed in column A. This is feeding the Dropdown boxes. That way they are always up-to-date with the current sheets in your workbook.
Next to the Dropdown boxes (shaded light green) is a formula to generate a hyperlink for the sheet selected in the dropdown box.

Copy the Dropdown box AND the cell to the right with the hyperlink formula, then past it wherever you need them in the workbook.
I have added 3 dropdown boxes on the 'Index Sheet' and one on the 'Apple' sheet.

Let us know if that is working for you.

Thanks for the response.

Does it work in xlsb format instead of xlsm as I have to reduce the size of the file.

I will let you know after testing in my file.

Listing the 40 sheet names on the Index Sheet should not make a difference. It is only 40 cells with simple text (no formulas).
If you don't need the hyperlinks there in column 'B', delete them and remove that line from the code so only column 'A' with the sheet names is populated.

If the 40 sheets don't change, then you could just have a list of them. Remove the macro so it won't run and update the list each time you select the sheet. Although you did not mention this to be the issue.


For the dropdown boxes, then we could use a simple reference to the static list; A1:A40 (instead of the formula calculating the length of the list for the dropdown lists). Again, this would save some time when calculating, not really affect the saving of the file.

Yes you can save the file as an .xlsb file.

Yes you can have specific lists of sheets for different dropdown boxes.
IF the list of sheets is specific for each dropdown, there is no need to use Defined Name ranges, just the reference to the cells as you did in your file works perfectly.
Defined Names ranges are good if the values change (adding, deleting, renaming sheets..). Another option if thge list often changes is to use tables for each list and refer in the dropdown list setting to the table name...

On the 'Dashboard' sheet in cell 'D3' use the formula;

Formula:

 =HYPERLINK("#'"&C3&"'!A1","Go To Sheet:"&C3) 

then pull.

Use the same formula to the right of each of the dropdown boxes. You may need to correct the cell references (in red) to refer to the cell with the dropdown value.

Trust this helps.

Let me see.As sheet name will also be renamed,deleted as well.

Thanx for the help.


Also does matching by lookup and automatically creating dropdown is possible or not which is mentioned in C and G of Dashboard by matching B and F with that of B column of index sheet.

Columns 'A' & 'B' fill each time this sheet is selected. This way you always have a list with all the current sheet names (if you don't want the links in column B, we can remove that from the macro code).

Column 'C' will show if a sheet has been allocated to a Defined Name group. If a sheet has not been allocated, there will be a ' - - ' or if a sheet has been allocated to multiple groups, it will state that. Duplicated sheets are also highlighted in the MATRIX where you make the sheet allocations.

Range F3:I20 is your MATRIX to allocate sheets to your Defined Name groups. This is where you make your changes, allocating sheets to dropdown groups. Changes here will automatically update the dropdown box values for the Defined Names.


Please refer to the attached file.

Trust this helps

Glad to hear it works for you.
The sheet list is now excluding the 'Dashboard' and 'Index Sheet' from the list and is sorted alphabetically.
I have updated the code so that the formula in column 'C' on the 'Index Sheet' is updated automatically based on the range of sheets and Dropdown Groups you have. So it will work when you add more sheets under a group, or add more groups in column J, K etc...
The formula, together with the sheet name list is refreshed each time the sheet is selected.

To add more defined name ranges, go to the Formulas tab at the top ribbon.
Click; Name Manager
Select one on the already defined names: Heaven, Moon, Star, Sun.
At the bottom is the formula. Copy it.
Click; New (at the top of the Name Manager window)
Give it a name, and paste the formula in the 'Refers To' field at the bottom.
Now you need to update the formula to the column in the 'Sheet Index' where you add your sheet list for the new dropdown option. The first free column would be J ...

Formula:

=OFFSET('Index Sheet'!$G$4, 0, 0, COUNTA('Index Sheet'!$G:$G), 1)

I have added a couple of screenshots in 'Screen Shots' in the attached file.

Trust this helps.

Thanx for your help.I got it. I am marking this as solved.

To all contributors are welcome not compulsory as it would add value

"Can it be possible the sheet selected from dropdown itself be in hyperlink as it would reduce the requirement of additional column for adding formula for hyperlink"