Use drop down to load worksheet

[3G]

Hi there-

Looking for a non-VBA/macro way of allowing users to either type or select a value of cell to load a specific worksheet in the workbook. Users could type or select their department number, and, that would load their department specific sheet.


I'm hoping to not use VBA or a macro if possible.


Thanks!

3G

 

[Luke M]

You could do something like that with 2 cells. One to input name, one to hold the link.

After choosing/typing sheet name in cell A2, formula is:

=HYPERLINK("[My Book.xls]"&A2&"!A1","Take me to sheet: "&A2)

 

[3G]

Hey that's neat Luke!


However, where do I put the reference to where the link goes? So, if they type 23, where do I define "Department 23"?


Thanks!

3G

 

[Luke M]

The formula needs to contstruct the entire referece. In the formula I gave, A2 would contain the entire sheet name. If the sheet name is actually "Department 23", you can modify formula to this:


=HYPERLINK("[My Book.xls]Department "&A2&"!A1","Take me to Department: "&A2)


See how whatever is in A2 is plugged into the string in first arguement? BTW, feel free to change the 2nd arguement to whatever you'd like. I was simply showing an example. =)

 

[3G]

Luke-

I've tried this techinique, and, it says the address is not valid? Here's what I've got:


=HYPERLINK("[Book6.xls]Department "&A2&"!A1","Take me to Department: "&A2)


Where Book6 is my file, and, Department 23 is a sheet name in that book.


Any thoughts as to where I'm going wrong?

 

[Luke M]

Oops. I forgot, if there's a space in the sheet name, need to include single quotation marks:

=HYPERLINK("[Book6.xls]'Department "&A2&"'!A1","Take me to Department: "&A2)


How's that?