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

 

[Jythier]

Put the single quote in front of the filename, instead of after.

 

[mahmed1]

Hi, i have done that but i still get the same error message

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

 

[Jythier]

Have you tried using the full path of the file?

 

[mahmed1]

Have you tried using the full path of the file?

Hi, I have tried that..still no luck

=HYPERLINK("'[Main Copy.xls]"&F36&"'!B2",F36)

 

[T. Valko]

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

Try it like this:

=HYPERLINK("#'"&D3&"'!B2",D3)

 

[Jythier]

I'll have to remember that symbol.

 

[mahmed1]

Try it like this:

=HYPERLINK("#'"&D3&"'!B2",D3)

Biff.... In the most nicest way possible. I just want to say I love you woman.

You always help me. Thank You

Can you advise why my original formula did not work and what is the syntax "#"'? That seems to be the whole trick for this to work.

Just for more info...In cell D3 i have a data validation (I have this formula...
Indirect(B3). Whatever is in B3 is Defined as named range

Thank You once again

 

[Jythier]

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

 

[T. Valko]

Biff.... In the most nicest way possible. I just want to say I love you woman.

You always help me. Thank You

Can you advise why my original formula did not work and what is the syntax "#"'? That seems to be the whole trick for this to work.

Just for more info...In cell D3 i have a data validation (I have this formula...
Indirect(B3). Whatever is in B3 is Defined as named range

Thank You once again

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.