Drop down list

[RLPeloquin]

If two requirements are met them go to that sheet. I need help in creating a drop down list. There is only two requirements that need to be met.
1st requirement is select color. There is only 6 colors. Yellow, Blue, Green, Brown, Orange, and Red.
2nd requirement is select numbers 1 thru 200. Then go to that sheet.
example: If Blue is selected and 1 is selected then go to Sheet named “Blue 1“. Thanks so much for any help!

 

[Yongle]

In this solution changing the value in B2 or right-clicking on cell B2 takes user to the sheet represented by the combination in A2 and B2
The code does nothing if the selected combination of sheets does not exist

Create Named Range Number ...
Enter numbers from 1 to 200 in a range of your choosing
Select that range
Name that range Number by typing Number into the NameBox and then ENTER

A2 = colour dropdown
Source is the list of colours (place in a named range if preferred)

B2 dropdown
Source
=Number

Place code in sheet module (does not work if you put it in the wrong place)
(right click on sheet tab\ View Code \ paste code below into that window)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("B2")) Is Nothing Then Sheets(Range("A2") & " " & Range("B2")).Activate
    On Error GoTo 0
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Cancel = True
        Sheets(Range("A2") & " " & Range("B2")).Activate
    End If
    On Error GoTo 0
End Sub

 

[Yongle]

To avoid VBA, place this formula in cell C2
=HYPERLINK("#'"&A2&B2&"'!A1",A2&B2)

This requires 2 named ranges

Number - see above

Colour
Enter the 6 colours in a range of your choosing BUT insert a space after each colour

Select that range
Name that range Colour by typing Colour into the NameBox and then ENTER

Data validation source for
A2 is
=Colour
Data validation source for
B2 is
=Number

 

[RLPeloquin]

Wow! Both work great!!!!!

 

[Yongle]

Thanks for your feedback

 

[RLPeloquin]

To avoid VBA, place this formula in cell C2
=HYPERLINK("#'"&A2&B2&"'!A1",A2&B2)

This requires 2 named ranges

Number - see above

Colour
Enter the 6 colours in a range of your choosing BUT insert a space after each colour
View attachment 18512
Select that range
Name that range Colour by typing Colour into the NameBox and then ENTER

Data validation source for
A2 is
=Colour
Data validation source for
B2 is
=Number

View attachment 18511

 

[RLPeloquin]

What does the # do or what should it be.

 

[RLPeloquin]

I have the named ranges. That works fine! But how do I get the results to go to that sheet. For example if I select Red then 25. How to I make it go to sheet Red 25. I’m sorry but I’m new to this. Any help all all would be grateful appreciated!

 

[Yongle]

But how do I get the results to go to that sheet

The original (VBA) solution assumed that colours were listed without the space
Replacement VBA

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("B2")) Is Nothing Then Sheets(Range("A2") & Range("B2")).Activate
    On Error GoTo 0
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Cancel = True
        Sheets(Range("A2") & Range("B2")).Activate
    End If
    On Error GoTo 0
End Sub