How to link different worksheet from the list of drop down menu

[sudeepkm73]

Dear Sir,

I am familiar to excel but not the VB script / macro in excel. I have an issue that how to link a name from the list of a drop down menu to different worksheet of the same workbook.

Example : I have a workbook which contain different vendors name from A3 : A22 and each vendor name is having its own respective worksheet. I have already given the hyperlink to each name as per their respective worksheet, but in future our vendor list will increase so I would like to make a drop down list which contain the name of all the vendors and when I select a vendor's name and press some GO button it should go the respective worksheet of the selected vendor. Can we do it without using VB / macros.

Kindly do the favor to sort out the issue.

Regards,
Sudeep

[sourabhg98]

Hi Sudeep,
One way you can do this is using a macro to create a list of all sheets.
The macro is as follows-

Sub ListSheetNames()
    Dim rng As Range
    Dim wkSht As Worksheet
   Set rng = Range("B2") 'B2 is the first cell from where the list would begin, you can change it to anything you want
    rng.Select
    For Each wkSht In Worksheets
       Selection = wkSht.Name
       ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    Next wkSht
Dim x As Long
x = rng.End(xlDown).Row - rng.Row + 1
rng.Offset(, 1).Resize(x).FormulaR1C1 = "=IF(RC[-1]="""","""",HYPERLINK(""#"" & RC[-1] & ""!A1"",""Go To "" & RC[-1]))"
End Sub

Now insert a new shape and assign the above macro to it. Then click on the shape to run the macro each time.

This macro would create a list of names of all the sheets present in the workbook, starting from cell B2. Then it would create hyperlinks in the adjacent column i.e. column C.

See if this helps.
Do ask for any other query you may have.

Hope this helps!!

Regards
Sourabh

[sudeepkm73]

Thanks Sourav, it may work as I am not much familiar with macros. It will be nice of you if you can suggest any formula without using macros / VB script to get the result. Hyperlink from drop down menu to different respective worksheets.

[sourabhg98]

If you want it is drop down form they try this method.
Create a list ff sheets using this macro-

Sub ListSheetNames()
    Dim rng As Range
    Dim wkSht As Worksheet
   Set rng = Range("B2") 'B2 is the first cell from where the list would begin, you can change it to anything you want
    rng.Select
    For Each wkSht In Worksheets
       Selection = wkSht.Name
       ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    Next wkSht
End Sub

Now in Cell C2 in this case, Go to Data>>Data Validation>>List and create a drop down. The range for it should be column C in this case.
Now assign the following macro to a shape/button.

Sub hyperlinkdropdown()
Sheets(Range("C2").Value).Select
End Sub

Now is cell C2 select the sheet you want to go to, and click on the shape/button (you have assigned the macro to), to jump to the sheet specified in cell C2.

Hope this helps!!

[sourabhg98]

Sorry, but this is not possible with macros...
You can create formula for hyperlinking, but first we need the names of all the sheets in a list form which is possible only through macro as far as I know.
If you are having trouble using those macros I can guide you, but believe me they are quite easy, once you get it.