Prev: Complex AND OR Formula
Next: xdate in Windows 7
From: Phil on 5 Jun 2010 18:22
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.
From: Max on 5 Jun 2010 22:24
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,"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.
From: Gord Dibben on 6 Jun 2010 11:30
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 <Phil(a)discussions.microsoft.com>
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,"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.

 | 
Pages: 1
Prev: Complex AND OR Formula
Next: xdate in Windows 7