Hyperlink with Indirect Formula not working

[ThomasB]

Hi

Using the INDIRECT function am trying to reference a different worksheets with in a worbook called Foods

my formula looks like this:

=HYPERLINK(INDIRECT("'[" & C3 & "]" & C4 & "'!" & C2),"Apples")

C3 = Workbook name is called Foods
C4 = Worksheet name is called Apples
C2 = cell reference is A2

I intend to change C4 and C2

Formula is not working anyone know what I am doing wrong?

Kind Regard

Thomas

 

[RoryA]

Hyperlink takes a string, not a cell ref, so you don't need INDIRECT at all:
=HYPERLINK("'[" & C3 & "]" & C4 & "'!" & C2,"Apples")

 

[ThomasB]

Hi Rory

Ahh did not know that? when I use the formula you provided with the indirect removed it unfortunately still returns an error any ideas?

Rgs

Thomas

 

[RoryA]

Works for me - does C3 contain "Foods" or "Foods.xls"?

 

[ThomasB]

Got it to work although in a round about way

using the folling link http://www.mvps.org/dmcritchie/excel/indirect.htm

came accross this formula:

=HYPERLINK("#" & "'" & C4 & "'!$A$1","Foods")

am unsure how it works but it provided the desired results ie
I can reference any worksheet I want by changing the string in C4

Kind Regards

Thomas

 

[mjsheen]

You can further shorten the formula like this.


=HYPERLINK("#'" & C4 & "'!$A$1","Foods")

Got it to work although in a round about way

using the folling link http://www.mvps.org/dmcritchie/excel/indirect.htm

came accross this formula:

=HYPERLINK("#" & "'" & C4 & "'!$A$1","Foods")

am unsure how it works but it provided the desired results ie
I can reference any worksheet I want by changing the string in C4

Kind Regards

Thomas

 

[CluaseTony]

Hi

Using the INDIRECT function am trying to reference a different worksheets with in a worbook called Foods

my formula looks like this:

=HYPERLINK(INDIRECT("'[" & C3 & "]" & C4 & "'!" & C2),"Apples")

C3 = Workbook name is called Foods
C4 = Worksheet name is called Apples
C2 = cell reference is A2

I intend to change C4 and C2

Formula is not working anyone know what I am doing wrong?

Kind Regard

Thomas

Although an old one change the INDIRECT to CONCATENATE and remove the apostrophe's
=HYPERLINK(CONCATENATE("[" & C15 & "]" & C16 & "!" & C17),"Apples")