If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Select a worksheet
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,"De sktop",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. |
#2
|
|||
|
|||
Select a worksheet
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 -- Max Singapore --- "Phil" wrote: 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,"De sktop",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. |
#3
|
|||
|
|||
Select a worksheet
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 On Sat, 5 Jun 2010 15:22:00 -0700, Phil wrote: 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,"D esktop",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. |
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
Quote:
The following change to your existing formula will create a Hyperlink to cell A1 (change this if required) in either the worksheet "Desktop" or "Notebook" depending upon the result. You will need to change the "Book1" reference to the name of your workbook. Code:
=IF(VLOOKUP($J$2,Database!A:U,21,FALSE)=$X$2,HYPERLINK("[Book1] Desktop!A1","Desktop"),IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$5,HYPERLINK("[Book1] Notebook!A1","Notebook"),"Error!!!!!!!")) |
Thread Tools | |
Display Modes | |
|
|