I am using concatenate to create a link to a cell in a different spreadsheet.
I cannot find an easy way to activate the link.

I have a formula =("="&F$1&"!"&"$B"&$A60) where the content of strings and
referenced cells generates a string: =filename.xls!$B67

However I can't get it to display the value of the string. It just displays:
=filename.xls!$B67


If I copy the cell and paste as a value, I'm close, but to get it to work as
link, I have to edit the string...for example replace "=file" with "file"
and then replace "file" back to "=file" and then it works correctly. Is
there a better way to activate a link?

use indirect()
=indirect(F$1&"!$B"&$A60)

"Feesh" wrote:

I am using concatenate to create a link to a cell in a different spreadsheet.
I cannot find an easy way to activate the link.

I have a formula =("="&F$1&"!"&"$B"&$A60) where the content of strings and
referenced cells generates a string: =filename.xls!$B67

However I can't get it to display the value of the string. It just displays:
=filename.xls!$B67


If I copy the cell and paste as a value, I'm close, but to get it to work as
link, I have to edit the string...for example replace "=file" with "file"
and then replace "file" back to "=file" and then it works correctly. Is
there a better way to activate a link?

On 2 Jul, 17:10, Feesh wrote:
I am using concatenate to create a link to a cell in a different spreadsheet.
I cannot find an easy way to activate the link.

I have a formula =("="&F$1&"!"&"$B"&$A60) where the content of strings and
referenced cells generates a string: =filename.xls!$B67

However I can't get it to display the value of the string. It just displays:
=filename.xls!$B67

If I copy the cell and paste as a value, I'm close, but to get it to work as
link, I have to edit the string...for example replace "=file" with "file"
and then replace "file" back to "=file" and then it works correctly. Is
there a better way to activate a link?

Hi Feesh

Try =INDIRECT(F$1&"!"&"$B"&$A60)

But note that this will only reference an open workbook.

Regards

Steve

Thanks for your help. This works, though I reference too many other files to
make this practical. For now, I'll stick with the copy paste and double
replace.

Regards, Feesh


"Scoops" wrote:

On 2 Jul, 17:10, Feesh wrote:
I am using concatenate to create a link to a cell in a different spreadsheet.
I cannot find an easy way to activate the link.

I have a formula =("="&F$1&"!"&"$B"&$A60) where the content of strings and
referenced cells generates a string: =filename.xls!$B67

However I can't get it to display the value of the string. It just displays:
=filename.xls!$B67

If I copy the cell and paste as a value, I'm close, but to get it to work as
link, I have to edit the string...for example replace "=file" with "file"
and then replace "file" back to "=file" and then it works correctly. Is
there a better way to activate a link?

Hi Feesh

Try =INDIRECT(F$1&"!"&"$B"&$A60)

But note that this will only reference an open workbook.

Regards

Steve