MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Select a worksheet

• Follow

```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.
```
 0

```Assuming your vlookup formula as posted is in cell X1,
you could place this in say, Y1:
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.
```
 0

```Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents =3D False
With Me.Range("A1")
If .Value =3D "Desktop" Then
Sheets("Desktop").Select
ElseIf .Value =3D "Notebook" Then
Sheets("Notebook").Select
End If
End With
stoppit:
Application.EnableEvents =3D 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.

Gord Dibben  MS Excel MVP

On Sat, 5 Jun 2010 15:22:00 -0700, Phil <Phil@discussions.microsoft.com>
wrote:

>I'm using the below formula to determine the specific type of equipment =
from=20
>a list named: database
>
>=3DIF(VLOOKUP(\$J\$2,Database!\$A:\$U,21,FALSE)=3D\$X\$2,"Desktop",IF(VLOOKUP(=
\$J\$2,Database!\$A:\$U,21,FALSE)=3D\$X\$5,"Notebook","Error!!!!!!!"))
>
>value of X2 =3D DSK and value of X5 =3D NBK
>
>What I need help with is a way of using the value returned to =
automatically=20
>open the relevant worksheet, ie: If "Desktop" is returned the Desktop=20
>worksheet needs to open and if "Notebook" is returned the notebook =
worksheet=20
>needs to open automatically. All worksheets are in the same workbook.

```
 0

2 Replies
472 Views

Similiar Articles:

7/27/2012 2:28:21 PM