list and hyperlink

[Tanya]

Hi, I would like on the selection from a validation list to go to a
particular cell on another sheet [the same cell reference of each sheet for
each value in the list] is this possible.

Example
validation list would contain a list of student names -
Smith, Henry
Duck, Donald
Rabbit, Roger

Result-
Smith, Henry [hyperlink to C2 on sheet S1]
Duck, Rabbit [hyperlink to C2 on sheet S2]

Note: S1 and S2 are sheet names referring in brief to student 1, 2 and so on.

I hope this makes sense. I don't expect hyperlink will be the way to solve
this, perhaps a macro? or a vlookup?

Thanking you in advance.
Tanya

 

[excelent]

http://pmexcelent.dk/Tanya.xls


"Tanya" skrev:

 

[Tanya]

Thank you for this solution, it is rather ingenious, however it doesn't
completely solve my problem.

I am trying to set up a workbook for recording student individual learning
plans and each student has an individual sheet. My goal is to have a drop
down list whereby a mentor simple selects the student and then is taken to
that students worksheet. Your solution requires the naming of the cell C3,
whist this is not difficult for me, I need to set up the workbook for someone
with little experience with Excel and needs to be foolproof. In other words,
I don't want future teachers to have to name a cell and would rather identify
the cell by its contents.

Thank you anyway, your input is greatly appreciated.

Kind Regards
Tanya

http://pmexcelent.dk/Tanya.xls


"Tanya" skrev:

Hi, I would like on the selection from a validation list to go to a
particular cell on another sheet [the same cell reference of each sheet for
each value in the list] is this possible.

Example
validation list would contain a list of student names -
Smith, Henry
Duck, Donald
Rabbit, Roger

Result-
Smith, Henry [hyperlink to C2 on sheet S1]
Duck, Rabbit [hyperlink to C2 on sheet S2]

Note: S1 and S2 are sheet names referring in brief to student 1, 2 and so on.

I hope this makes sense. I don't expect hyperlink will be the way to solve
this, perhaps a macro? or a vlookup?

Thanking you in advance.
Tanya

 

[Tanya]

I've written the following macro but it won't activate the scheet after
finding the value.

Private Sub CommandButton1_Click()
Range("B5").Select
Sheets("Startup").Select
Cells.Find(What:=B2, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub

Any assistance greatly appreciated.

Kind Regards
Tanya

http://pmexcelent.dk/Tanya.xls


"Tanya" skrev:

Hi, I would like on the selection from a validation list to go to a
particular cell on another sheet [the same cell reference of each sheet for
each value in the list] is this possible.

Example
validation list would contain a list of student names -
Smith, Henry
Duck, Donald
Rabbit, Roger

Result-
Smith, Henry [hyperlink to C2 on sheet S1]
Duck, Rabbit [hyperlink to C2 on sheet S2]

Note: S1 and S2 are sheet names referring in brief to student 1, 2 and so on.

I hope this makes sense. I don't expect hyperlink will be the way to solve
this, perhaps a macro? or a vlookup?

Thanking you in advance.
Tanya

 

[Roger Govier]

Hi Tanya

I am assuming that you have created a Data Validation list in B2, to enable
selection of any name from column A of sheet startup.
Column B only requires the sheet name associated with the name in column A,
do not enter the cell reference C2.

If so, then the following event code should be copied to sheet Startup.
Copy code
Right click on startup tab>View Code>Paste
Alt+F11 to return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsname As String, name As String

If Target.Count > 1 Then Exit Sub
If Target.Column = 2 And Target.Row = 2 Then

name = Range("B2").Value

Range("A:A").Activate
Cells.Find(What:=name, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

wsname = ActiveCell.Offset(0, 1).Value
Range("B2").Activate ' put focus back to selection cell
Sheets(wsname).Select
Sheets(wsname).Range("C2").Activate
End If

End Sub

When you make any selection from the dropdown in cell B2, the code will
trigger and take you to the sheet name associated with that name from column
2

I would place a hyperlink on each sheet in say cell A1, which takes the user
back to Startup sheet.
--
Regards
Roger Govier

I've written the following macro but it won't activate the scheet after
finding the value.

Private Sub CommandButton1_Click()
Range("B5").Select
Sheets("Startup").Select
Cells.Find(What:=B2, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub

Any assistance greatly appreciated.

Kind Regards
Tanya

http://pmexcelent.dk/Tanya.xls


"Tanya" skrev:

Hi, I would like on the selection from a validation list to go to a
particular cell on another sheet [the same cell reference of each sheet
for
each value in the list] is this possible.

Example
validation list would contain a list of student names -
Smith, Henry
Duck, Donald
Rabbit, Roger

Result-
Smith, Henry [hyperlink to C2 on sheet S1]
Duck, Rabbit [hyperlink to C2 on sheet S2]

Note: S1 and S2 are sheet names referring in brief to student 1, 2 and
so on.

I hope this makes sense. I don't expect hyperlink will be the way to
solve
this, perhaps a macro? or a vlookup?

Thanking you in advance.
Tanya

 

[Tanya]

Thank you so much Roger,
You are wonderful, this has really been very helpful.

Kind Regards
Tanya

Hi Tanya

I am assuming that you have created a Data Validation list in B2, to enable
selection of any name from column A of sheet startup.
Column B only requires the sheet name associated with the name in column A,
do not enter the cell reference C2.

If so, then the following event code should be copied to sheet Startup.
Copy code
Right click on startup tab>View Code>Paste
Alt+F11 to return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsname As String, name As String

If Target.Count > 1 Then Exit Sub
If Target.Column = 2 And Target.Row = 2 Then

name = Range("B2").Value

Range("A:A").Activate
Cells.Find(What:=name, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

wsname = ActiveCell.Offset(0, 1).Value
Range("B2").Activate ' put focus back to selection cell
Sheets(wsname).Select
Sheets(wsname).Range("C2").Activate
End If

End Sub

When you make any selection from the dropdown in cell B2, the code will
trigger and take you to the sheet name associated with that name from column
2

I would place a hyperlink on each sheet in say cell A1, which takes the user
back to Startup sheet.
--
Regards
Roger Govier

I've written the following macro but it won't activate the scheet after
finding the value.

Private Sub CommandButton1_Click()
Range("B5").Select
Sheets("Startup").Select
Cells.Find(What:=B2, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub

Any assistance greatly appreciated.

Kind Regards
Tanya

http://pmexcelent.dk/Tanya.xls


"Tanya" skrev:

Hi, I would like on the selection from a validation list to go to a
particular cell on another sheet [the same cell reference of each sheet
for
each value in the list] is this possible.

Example
validation list would contain a list of student names -
Smith, Henry
Duck, Donald
Rabbit, Roger

Result-
Smith, Henry [hyperlink to C2 on sheet S1]
Duck, Rabbit [hyperlink to C2 on sheet S2]

Note: S1 and S2 are sheet names referring in brief to student 1, 2 and
so on.

I hope this makes sense. I don't expect hyperlink will be the way to
solve
this, perhaps a macro? or a vlookup?

Thanking you in advance.
Tanya