Thanks Dave,

I am having second thoughts about his addin myself. My original
formula with INDIRECT works if the file is open. I was hoping to get
it to work without having to open each file.

Thanks for the replies and advice.

-Minitman


On Mon, 17 Mar 2008 15:19:40 -0500, Dave Peterson
wrote:

I don't use Laurent's addin.

You may want to try a simple formula that retrieves a value from a file on your
C:\ folder. If you can't get that to work, then post the formula that you
tried.

Someone who uses that addin may see the problem and give you the solution.

Minitman wrote:

Hey Dave,

Thanks for the syntax help. Your sample got me back to the original
problem.

Since both files are in the same directory, the formula should not
need a full path to work. When I tried my path it errored-out
regardless if the source workbook was open or not (my syntax was
incorrect). Your syntax fixed that problem, so I was back to the
original problem which is that I can't get the data from a closed
workbook.

The INDIRECT.EXT from the morefunc addin is supposed to work with
closed workbooks unlike MS INDIRECT which can't look inside closed
workbooks. I just can't seem to make that part of INDIRECT.EXT work,
even after adding the full path. So I assume that the path is not the
problem.

I went back to the site where I downloaded the morefunc addin from
(see earlier post in this thread for URL) to ask for support, but I
don't speak French and could not find a way to ask any questions (the
newsgroup is in French and I am not sure where it is located outside
of the web site). I did read the questions posted there and found a
couple of question that were related to my problem, but they were not
answered!!! Maybe the morefunc addin is not the answer.

Any assistance in solving this INDIRECT not looking into closed
workbook problem will be greatly appreciated.

-Minitman

On Mon, 17 Mar 2008 11:49:22 -0500, Dave Peterson
wrote:

I built a simple formula to a different workbook with that sending workbook
open.

Then I closed the sending workbook and excel modified my formula:

='C:\My Documents\Excel\[book 1.xls]Sheet1'!$A$1

Note the positions of the apostrophes and []'s.

So my untested guess:

...INDIRECT.ext("'\\Media\400_B (E)\Transfer Items\Recovered Schedules\["
&TEXT(B12,"yyyy-mm")&".xls]Input'!$A$3"), ...



Minitman wrote:

Hey Dave,

Thanks for the reply.

Your right, I didn't. I was under the assumption that if the target
file was in the same directory that it was not necessary. But it is a
valid observation, so I went ahead and inserted the network, drive and
directory path into the formula like so:

=SUM(OFFSET(INDIRECT("'[\\Media\400_B (E)\Transfer Items\Recovered
Schedules\"&TEXT(B12,"yyyy-mm")&".xls]Input'!$A$3"),27*(DAY(B12)-1)+2-2*ROW($A$1),6,27,1))

Unfortunately, I discovered that I don't know how to insert that
information!

So, if I start with the original formula:

=SUM(OFFSET(INDIRECT.EXT("'["&TEXT(B12,"yyyy-mm")&".xls]Input'!$A$3"),27*(DAY(B12)-1)+2-2*ROW($A$1),6,27,1))

How do I add this path to it?

\\Media\400_B (E)\Transfer Items\Recovered Schedules\

Any help or samples would be greatly appreciated.

-Minitman

On Mon, 17 Mar 2008 07:15:51 -0500, Dave Peterson
wrote:

You didn't include the drive and path in your formula.



Minitman wrote:

Hey Biff,

I can't seem to get the INDIRECT.EXT to work any differently then
Micro$oft's INDIRECT. I get a #REF# error until I open the requested
workbook.

Here is the formula that I converted to INDIRECT.EXT:

=SUM(OFFSET(INDIRECT.EXT("'["&TEXT(B12,"yyyy-mm")&".xls]Input'!$A$3"),27*(DAY(B12)-1)+2-2*ROW($A$1),6,27,1))

It is supposed to return the value of 31.50. Which it does when the
referenced workbook is open and #REF! when it is not.

It is acting like it is not loaded.

How can I check to see if it is loaded?

-Minitman

On Mon, 17 Mar 2008 00:49:25 -0400, "T. Valko"
wrote:

The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:

http://xcell05.free.fr/morefunc/english/index.htm

It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.