On Fri, 9 Sep 2005 04:30:03 -0700, dodger
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
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