Hyperlink associated with a list...

[Anthony G.]

Hello to all.

Once I set up DataValidation in an effort to provide the user with a List of Names in cells B1:B3:

John
Ron
Frank

...my goal is this..to have the name (once it is selected by the user) to be linked to another worksheet with that name. For example, if a user selects Ron, I would like to have the ability to select Ron from the cell, thus bringing me to a worksheet named Ron.

Make sense?

Any advice, as always, will be greatly appreciated.

Take Care,

Anthony

 

[Zack Barresse]

the only way i could do that is by creating 2 cells.

cell 1: data validation w/ a drop down list containing the ranges desired
cell 2: an if statement containing hyperlinks for the cooresponding choices.

of course this means you get no more than 7 nested if statements (8 overall choices) if that is the route to go. and looks aren't all that great.

but it did work for me. i can post the html script (or try) if you like?

good luck either way.

 

[Anthony G.]

Appreciate the feeback...if that's the only way, well, then I'll give it a shot - please post the script.

Thanks!

 

[Tom Urtis]

It's really simple, and you can have as many sheets as you want without actual hyperlinks. List your sheet names in B1:B3, name the range something like "Sheetz" (without the quotes), then validate your range of cells (example below is A1:A10) to Allow List, and enter =Sheetz in the Source field of the data validation dialog.

Then, right click on your sheet tab of the validated range (not necessarily the sheet where the sheets are listed), left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet. Modify for target range.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
Sheets(Target.Value).Activate
End Sub

 

[Zack Barresse]

here is what i did.

the bottom two cells are the ones I used. also on another sheet i inputed the list of names i wanted to see (i used abbreviated file names. i named the range of names on the second sheet (not shown) called "names".

hyperlink try.xls
	A	B	C	D
1	Choose	Clicktogo
2	name	openbook
3	tkn	tkn
Sheet1

i hope this helps.

regards.

btw: cell A3 is using data validation -> list w/ range "=names". I don't think it shows that in the code.

 

[Anthony G.]

Great suggestions....Thank You Both!