Quick Hyperlink Function Question

[mahmed1]

Hi guys

I have this formula to hyperlink to the sheet which is in D3 but its not working. Please advise where i am going wrong.
I have multiple sheets and have a data validation in cell D3 exactly the same names as the sheet names. I am trying to hyerlink to the sheet depending on what i select in D3

=HYPERLINK("[Main Copy]'"&D3&"'!B2",D3)

I get this message (Cannot open the specified File)..

Is this error because i have spaces in my sheet names? If yes how can i get round this without having to delete the spaces?

Thank You

 

[T. Valko]

Good thing Biff was here, I would never have gotten that... thanks for cleaning up!

You're welcome!

 

[Jythier]

It seems it lets Excel know that the hyperlink is within the file it's already in. I tried it with using the entire path (down to the drive) and that worked, as well.

=HYPERLINK("[C:\Book2.xlsx]'Sheet1'!A1", "Eeeee!")

So either you need a full path, or a #... I'll choose the # thanks.

 

[mahmed1]

You're welcome!

About that "pound sign"...

I know that it means to go to a specific location but I don't know why it doesn't work without it.

It's similar to a URL hyperlink.

Consider these 2 examples:

http://contextures.com/xlFunctions02.html

http://contextures.com/xlFunctions02.html#Trouble

The first one takes you to the whole page and the second one with the "pound sign" takes you to a specifc location on the same page.

Thank You

 

[T. Valko]

It seems it lets Excel know that the hyperlink is within the file it's already in. I tried it with using the entire path (down to the drive) and that worked, as well.

=HYPERLINK("[C:\Book2.xlsx]'Sheet1'!A1", "Eeeee!")

So either you need a full path, or a #... I'll choose the # thanks.

Yeah, that seems to be what it means.

See, we both learned something from this!

 

[mahmed1]

You're welcome!

Hi,

I have run into another problem..My hyperlinks don't work when the sheets are hidden. Can i get round this?

Thank You

 

[T. Valko]

Hi,

I have run into another problem..My hyperlinks don't work when the sheets are hidden. Can i get round this?

Thank You

All I can think of is unhide the sheets.

 

[mahmed1]

All I can think of is unhide the sheets.

Thank You..The reason why i have hidden the sheets is because i have so many sheets however i need to keep some sheets unhidden therefore rather than having multiple sheets i thought it would have been neater to hyperlink to the unhidden sheets so that way i would not have many sheets unhidden.

Thank you for your help