Evaluating results of a concatenate formula, as a formula

D

dodger

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 ?
 
R

Ron Rosenfeld

="='[Register " & VarName & ".xls]Monthly'!$D$20"
Click to expand...

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

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


--ron
 
A

Arvi Laanemets

Hi

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

dodger

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 said:
Hi

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


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


dodger said:
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 ?
Click to expand...
Click to expand...
 
D

dodger

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 said:
="='[Register " & VarName & ".xls]Monthly'!$D$20"
Click to expand...

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

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


--ron
Click to expand...
 
R

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.

Ron Rosenfeld said:
="='[Register " & VarName & ".xls]Monthly'!$D$20"
Click to expand...

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

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


--ron
Click to expand...
Click to expand...

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
 
Top