Creating Useable Index

  • Thread starter Thread starter SHARON
  • Start date Start date
S

SHARON

I would like to create a "useable" index on sheet 1,

ex. Part Number - info on sheet 2
Mat'l - info on sheet 3
Supplier - info on sheet 4

I want to type that index in column b, and click on the word "Part Number"
and have excel open up, or jum p to sheet 2, or if i click on Mat'l, excel
opens, or jumps over to sheet 3 etc...

I hope this is clear.

Thank you!
 
Sharon,

In Row 1 of each sheet, enter the corresponding Sheet name for that column of data. Then copy the
code below, right-click the sheet tab, select "View Code" and paste the code into the window that
appears.

Double click on the sheet name in row 1 to activate the sheet - double click on any value in the
column to activate the sheet and select the cell with that part number or Material code value.

HTH,
Bernie
MS Excel MVP



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim myVal As Variant

On Error GoTo ErrHandler

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
myVal = Target.Value
Worksheets(Cells(1, Target.Column).Value).Activate
ActiveSheet.Cells.Find(myVal).Select

ErrHandler:
Application.EnableEvents = True
End Sub
 
Thanks for your reply, however, i couldn't get that to work...Just to clarify.

I will have on sheet 1 and index:

Column b:

P/N -------this data will be on sheet 2
Mat'l --------this data will be on sheet 3, etc...
Supplier
etc..

I want to be able to click on the word "P/N" and have excel open/go to sheet
2, or click on "Mat'l" and have excel open/go to sheet 3
--
Thanks,
Sharon


Bernie Deitrick said:
Sharon,

In Row 1 of each sheet, enter the corresponding Sheet name for that column of data. Then copy the
code below, right-click the sheet tab, select "View Code" and paste the code into the window that
appears.

Double click on the sheet name in row 1 to activate the sheet - double click on any value in the
column to activate the sheet and select the cell with that part number or Material code value.

HTH,
Bernie
MS Excel MVP



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim myVal As Variant

On Error GoTo ErrHandler

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
myVal = Target.Value
Worksheets(Cells(1, Target.Column).Value).Activate
ActiveSheet.Cells.Find(myVal).Select

ErrHandler:
Application.EnableEvents = True
End Sub
Click to expand...
 
Put this in the cell that contains the P/N text:

=HYPERLINK("#"&CELL("address",'Sheet2'!A1),"P/N")

Change Sheet2 to the name of the sheet you need.

This creates a hyperlink to A1 of that sheet. (Change A1 to the cell's address
that you want to go to.)
Thanks for your reply, however, i couldn't get that to work...Just to clarify.

I will have on sheet 1 and index:

Column b:

P/N -------this data will be on sheet 2
Mat'l --------this data will be on sheet 3, etc...
Supplier
etc..

I want to be able to click on the word "P/N" and have excel open/go to sheet
2, or click on "Mat'l" and have excel open/go to sheet 3
Click to expand...
 
Back
Top