Hyperlink sheets dropdown list

**okelly** · 11-16-2007, 02:06 PM

hi everyone.

I have a file with approx 30 sheets in it.
I'm trying to develop a dropdown list that users can select a sheet from and automatically click on the dropdown list to go there. (either with VBA or hyperlinks). The sheet1, sheet2, sheet3..sheet30 are all contained in the same book1.xls file.

Does anybody have a working model they can share?

Thanks
Conor

**Ron Coderre** · 11-16-2007, 02:20 PM

Maybe something like this will work for you...

Z1: SheetList
Z2: Sheet1
Z3: Sheet2
etc
Z30: Sheet29

B2: Pick-a-sheet
B3: (use a Data Validation list here that is sourced to the Z2:Z30 sheet list)
C3: =HYPERLINK("#"&B3&"!A1","Click HERE to see that sheet")

Here's how to use that:
Selects a sheet from the dropdown list
Click on cell C3 to go to cell A1 of that sheet.

Note: if you need help with Data Validation, let us know.

Can you work with that?

**Leith Ross** · 11-16-2007, 02:31 PM

Hello Conor,

If you have already added a Forms Drop Down to your sheet, here are 2 macros you can use. One loads the Drop Down with all the sheet names in the workbook, and the other you would assign to the Drop Down to activate the selected sheet.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**okelly** · 11-17-2007, 05:33 AM

thanks both. works fine

**Supra2JZ** · 10-16-2013, 05:41 AM

I have an Indirect Drop down, should that matter? it comes up with Reference is not valid.. My Page names are the exact same as that is in the drop down output. Cheers

**arlu1201** · 10-16-2013, 05:49 AM

Supra

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.