Evaluating results of a concatenate formula, as a formula

I have this formula in cell A2.
="='[Register " & VarName & ".xls]Monthly'!$D$20"

Cell B1 has a range name of "VarName" and it contains "2005a".

The result I get is a string:
='[Trust Register 2005a.xls]Monthly'!$D$20

Rather than displaying the string, I want the string to evaluate as a fomula.

Any ideas ?

On Thu, 8 Sep 2005 19:46:02 -0700, dodger <[email protected]>
wrote:

>="='[Register " & VarName & ".xls]Monthly'!$D$20"

So long as the 'Register 2005a' workbook is open, you can use the INDIRECT
worksheet function:

=INDIRECT("'[Register " & VarName & ".xls]Monthly'!$D$20")


--ron

Hi

Copy cell A2, and PasteSpecial Values
From Edit menu, Replace '=' with '='


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"dodger" <[email protected]> wrote in message
news:[email protected]...
>I have this formula in cell A2.
> ="='[Register " & VarName & ".xls]Monthly'!$D$20"
>
> Cell B1 has a range name of "VarName" and it contains "2005a".
>
> The result I get is a string:
> ='[Trust Register 2005a.xls]Monthly'!$D$20
>
> Rather than displaying the string, I want the string to evaluate as a
> fomula.
>
> Any ideas ?

Thanks for the reply. I'm going to have 60 of these spreadsheets that pull
data from different files and there will be several formulas similar to this
one in each file. I'm trying to make the formulas dynamic enough that I
don't have to edit each of those formulas.

"Arvi Laanemets" wrote:

> Hi
>
> Copy cell A2, and PasteSpecial Values
> From Edit menu, Replace '=' with '='
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvil<at>tarkon.ee )
>
>
> "dodger" <[email protected]> wrote in message
> news:[email protected]...
> >I have this formula in cell A2.
> > ="='[Register " & VarName & ".xls]Monthly'!$D$20"
> >
> > Cell B1 has a range name of "VarName" and it contains "2005a".
> >
> > The result I get is a string:
> > ='[Trust Register 2005a.xls]Monthly'!$D$20
> >
> > Rather than displaying the string, I want the string to evaluate as a
> > fomula.
> >
> > Any ideas ?
>
>
>

Thanks for your reply. I tried that and the result was #Ref!. Also, because
there will be several of these formulas in this workbook referencing
different files, having them all open will not be practical.

"Ron Rosenfeld" wrote:

> On Thu, 8 Sep 2005 19:46:02 -0700, dodger <[email protected]>
> wrote:
>
> >="='[Register " & VarName & ".xls]Monthly'!$D$20"
>
> So long as the 'Register 2005a' workbook is open, you can use the INDIRECT
> worksheet function:
>
> =INDIRECT("'[Register " & VarName & ".xls]Monthly'!$D$20")
>
>
> --ron
>

On Fri, 9 Sep 2005 04:30:03 -0700, dodger <[email protected]>
wrote:

>Thanks for your reply. I tried that and the result was #Ref!. Also, because
>there will be several of these formulas in this workbook referencing
>different files, having them all open will not be practical.
>
>"Ron Rosenfeld" wrote:
>
>> On Thu, 8 Sep 2005 19:46:02 -0700, dodger <[email protected]>
>> wrote:
>>
>> >="='[Register " & VarName & ".xls]Monthly'!$D$20"
>>
>> So long as the 'Register 2005a' workbook is open, you can use the INDIRECT
>> worksheet function:
>>
>> =INDIRECT("'[Register " & VarName & ".xls]Monthly'!$D$20")
>>
>>
>> --ron
>>

That means that either there was no workbook open with the name

'Register 2005a.xls' or that there was not worksheet named Monthly in that
workbook.

But if you cannot have the workbook open, then you cannot use the INDIRECT
function.

However, you could download Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ and use the INDIRECT.EXT function. This will allow
referencing closed workbooks.

You may need to add more information to "Register" to more fully define the
path name. See HELP for that function to decide if you need to do that.


--ron