| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Useable Index
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! -- Thanks, Sharon |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating Useable Index
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 "SHARON" wrote in message ... 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! -- Thanks, Sharon |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating Useable Index
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" wrote: 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 "SHARON" wrote in message ... 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! -- Thanks, Sharon |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating Useable Index
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.) SHARON wrote: 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" wrote: 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 "SHARON" wrote in message ... 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! -- Thanks, Sharon -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating Useable Index
Dave,
Thank you so much...that's exactly what i was looking for! -- Thanks, Sharon "Dave Peterson" wrote: 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.) SHARON wrote: 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" wrote: 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 "SHARON" wrote in message ... 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! -- Thanks, Sharon -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenating 3 text columns into a useable date | Excel Worksheet Functions | |||
| Creating an Index | Excel Worksheet Functions | |||
| combining 3 columns into useable date | Excel Worksheet Functions | |||
| Creating and Index with worksheet names | Excel Discussion (Misc queries) | |||
| I'm stumped, splitting up a clump of text into useable information | Excel Discussion (Misc queries) | |||
Linear Mode
