Dropdown list of hyperlinks

[psjpsjpsjpsj]

Hello,

I've been searching for a solution on this and other Excel forums. Some people describe quite similar problems, but I cannot exactly find the solution. So here is my problem:

I have a large amount of sheets in a workbook (, say 70). For example:


1 GrandMastersheet of pets

.....1 Mastersheet of dogs
.................1 sheet of little dogs
.................1 sheet of big dogs
.................1 sheet of blue dogs
......1 Mastersheet of cats
.................1 sheet of black cats
.................1 sheet of white cats
.....1 Mastersheet of snakes ()
.................1 sheet of cobra's
etc. etc. etc.


The names of the sheets in the lowest level are quite long. Creating a icon with a macro/hyperlink in it is not what I want. (Boxes are too big and there would be too many boxes.)

This is what I would like: A clickable dropdownlist in the Mastersheets, that only show the lower level sheets of that category. For example:

A dropdownlist on the dogs Mastersheet that only show the words 'little dogs', 'big dogs' and 'blue dogs'. These words should be clickable and insta-hyperlinked to the specific sheet.

Hope someone can help me out here!

Thanks,

Psj

Edit: After posting this, I notices where that smiley went that I clicked accidentally.. In the title :P

[Fotis1991]

Hi

Welcome to the forum.

You'll find two excellent solutions here.

[psjpsjpsjpsj]

Wow that's great. Especially the first one would be perfect. Following the post of Ron Coderre I ran into the problem of not knowing what to put in the data validation list's source code. Using his example I chose 'list' under 'allow' and the source would be: ='Sheet2:Sheet30'!

When I do this I get a formula error..

Thanks for your help so far

Psj

[Fotis1991]

See a small example of what Ron suggested there.

[psjpsjpsjpsj]

Getting a 'refference is not valid error' when I try to do the exact same thing.

[Fotis1991]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[psjpsjpsjpsj]

Hope that it is right this way.

Thanks

Hyperlink Dropdown.xlsx

[Fotis1991]

You need to have no spaces in Sheets Names!

[psjpsjpsjpsj]

Thank you! Finally working like a charm :D

[Fotis1991]

Glad to hear that! So:

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.