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 <F...@discussions.microsoft.com> 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 <F...@discussions.microsoft.com> 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
>
>