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
|
|
|
|
Reply
|
 Utf
|
6/5/2010 10:22:00 PM |
|
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.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
6/6/2010 2:24:30 AM
|
|
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.
Alt + q to return to Excel.
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
|
|
|
|
Reply
|
Gord
|
6/6/2010 3:30:06 PM
|
|
|
2 Replies
472 Views
(page loaded in 0.41 seconds)
Similiar Articles: Reference the previous Worksheet in a Formula - microsoft.public ...Select a worksheet - microsoft.public.excel.worksheet.functions ... Reference the previous Worksheet in a Formula - microsoft.public ..... WeekStep = InputBox("Number of ... Printing a selected worksheet - microsoft.public.excel.programming ...I have multiple worksheets to select from, but the user will only need to print one of those sheets. How can I in code through an input box functio... Link one worksheet in a workbook to another worksheet in same work ...I am trying to setup a workbook that has a master sheet and many subsequent sheets. On the master sheet, I would like to be able to select a cell th... Using VBA to find a value and select a range - microsoft.public ...I am trying to write a macro that will let me find a specific vale in a specific column, then select part of the row that the value is in, beginnign... Sheet selected, automatic return to top of sheet - microsoft ...Sheet selected, automatic return to top of sheet - microsoft ... Alt + q to return to Excel. Gord Dibben MS ... Unprotect the sheet. Select all cells and Format>Cells ... Macro - onAction arguments - microsoft.public.excel.worksheet ...Something like .OnAction = Worksheet.Select or a separate macro .... I am not sure how to do it so i need some advice. -- version83 Select 'used range' except header row in excel 2007 ...Hello How can I use code to select populated cells in a worksheet but exclude the top row which is my header row? The spreadsheet has formulae do... selecting every other data point in a column - microsoft.public ...Assuming your data starts in A2 put in C2 =A2 then select C2 and C3 together Click and drag the handle down for the 100 rows, and he formula will only be in every ... Copy Worksheet to New Workbook - microsoft.public.mac.office.excel ...Move or copy a worksheet - Excel - Office.com Click new book to move or copy the selected sheets to a new ... select the data that you want to move or copy. Cannot print both-sided by selecting multiple worksheets ...Any batch or 3rd-party program can fix this problem?? When printing multiple worksheets, it print one sheet as one file... I must select "page fr... How to: Select Worksheets - Microsoft Corporation: Software ...The Select method in Microsoft Office Excel 2003 selects the specified object, which moves the user's selection to the new object. Use the Activate method if you want ... How to: Select Worksheets - Microsoft Corporation: Software ...The Select method selects the specified object, which moves the user's selection to the new object. Use the Activate method if you want to bring focus to the object ... Select one or multiple worksheets - Excel - Office.comBy clicking the tabs of worksheets (or sheets) at the bottom of the window, you can quickly select a different sheet. If you want to enter or edit data on several ... Automate Excel ยป VBA: Select a Worksheet by Tab NameCan't get the tutorial to work for you? Need help with your code? Get answers right away at our AE Excel Support Forums! Worksheets in VBA for ExcelYou cannot select a sheet that is hidden so you will need to write: Sheets("Balance").Visible= True Sheets("Balance").Select and then if you want to hide the sheet again: 7/27/2012 2:28:21 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
39 followers. 