IIRC, =offset() is another function that won't work with closed workbooks.

Maybe you could replace it with:

=INDIRECT.EXT("'P:\Financial Planning & Analysis\SmartPage2\[SMP2Data.xls]"
&Test!C$1&"'!A1:a10")

Or some sort of =index()???



QuietMan wrote:

Dave,

Here is the syntax I'm using for the indirect.ext fromula

=OFFSET(INDIRECT.EXT("'P:\Financial Planning &
Analysis\SmartPage2\[SMP2Data.xls]"&Test!C$1&"'!$A$1",0),0,0,10,1)

Let me know if it's not right

Thanks
--
Helping Is always a good thing

"Dave Peterson" wrote:

The =indirect.ext() has worked for lots of people. I'd bet you built the
formula incorrectly.

But I don't know anything you can do to improve the speed.

QuietMan wrote:

Thanks, Dave

But the function dosent't work it gives me #value when the file is closed
and the remarks about the function being extremely slow if coppied into many
cells precludes me from using it even if it worked

Thanks again

--
Helping Is always a good thing

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

QuietMan wrote:

Does anyone mknow how I would reference "Sheet2" to a cell in a worksheet
Need to be able to change the worksheet name based on a cell

Thanks

='C:\Documents and Settings\FP&A\Templates\[SMP2Data.xls]Sheet2'!$A$1

--
Helping Is always a good thing

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson