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.
--
Dave Peterson