Select a worksheet

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

I'm using the below formula to determine the specific type of equipment from
a list named: database

=IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$2,"Desktop",IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$5,"Notebook","Error!!!!!!!"))

value of X2 = DSK and value of X5 = NBK

What I need help with is a way of using the value returned to automatically
open the relevant worksheet, ie: If "Desktop" is returned the Desktop
worksheet needs to open and if "Notebook" is returned the notebook worksheet
needs to open automatically. All worksheets are in the same workbook.
 
Assuming your vlookup formula as posted is in cell X1,
you could place this in say, Y1:
=IF(X1<>"Error!!!!!!!",HYPERLINK("#"&CELL("address",INDIRECT("'"&X1&"'!A1")),X1),"")
to produce a clickable hyperlink to the particular sheet returned by your
formula
Any worth? hit the YES below
 
Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A1")
If .Value = "Desktop" Then
Sheets("Desktop").Select
ElseIf .Value = "Notebook" Then
Sheets("Notebook").Select
End If
End With
stoppit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Edit the "A1" to suit.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP
 
Back
Top