Evaluating results of a concatenate formula, as a formula

[Guest]

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 ?

 

[Ron Rosenfeld]

="='[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

 

[Arvi Laanemets]

Hi

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

 

[Guest]

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.

Hi

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


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

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 ?

 

[Guest]

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.

="='[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

 

[Ron Rosenfeld]

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.

="='[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