Get Data From Closed Workbook

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Hey Biff,

Thanks for the reply.

And thanks for the very cool Add-In.

This looks like it will make my coding a lot easier.

-Minitman
 
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
 
Greetings,

I need to get data from a closed workbook. The problem is the name of
the workbook is variable according to what is in a date cell.

I have labeled my workbooks with dates in the format of:

yyyy-mm (eg. Feb. 2008 = 2008-02.xls)

B14 is a date that determines which workbook is being called and A3 is
the reference cell that the rest of the formula is looking for (didn't
copy that part of the formula here, not relevant)

Here is the problem area:

....INDIRECT("'["&TEXT(B14,"yyyy-mm")&".xls]Input'!$A$3")...

INDIRECT does not like to look into a closed workbook.

This works if the reference workbook is open. Opening the referenced
workbook is a problem due to memory restrictions.

Any help will be greatly appreciated.

-Minitman
 
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



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


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

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.
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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



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"), ...


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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
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


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

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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
You didn't include the drive and path in your formula.


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

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.
Click to expand...
Click to expand...
 
I know.

I've been slamming into that wall for a couple of years now and have
mostly resigned myself to having to open these workbooks and suffering
the slow down that results from lack of memory and going to the swap
file. I've got the memory but my Excel can't see but 500megs of it.
I'll get to Vista and Excel 2007 when my ship comes in. Until then,
I'll keep looking.

I was told that this Morefunc INDIRECT.EXT would get the data from
closed workbooks which is why I tried it. It doesn't seem to be able
to do that or I'm doing something wrong. I get the impression that
you are not all that impressed with this addin, would you be willing
to elaborate?

Otherwise, do you know of ANY possible work around's that might work?

-Minitman



If you're hoping to get =indirect() to work successfully with closed workbooks,
then you're going down the wrong path. It won't work with closed workbooks.
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

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

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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
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"), ...


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

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


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"

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.
Click to expand...
Click to expand...
Click to expand...
 
Hey Biff,

"OFFSET will not work on a closed file, either!"

I didn't know that this was going to be a 2 part question. Thank you,
Biff, for bringing the OFFSET limitation to my attention, I had no
idea!

That might explain the #VALUE! error message I'm getting after I added
the full path to this formula (was #REF!).

Here is that formula again:

=SUM(OFFSET(INDIRECT.EXT("'\\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))

With B12 being a date that is converted into the number of the day or
how far to go down from the reference cell to get to area of interest
<...27*(DAY(B12)-1)+2-2*ROW($A$1)...> and the name of the closed
workbook in question <...TEXT(B12,"yyyy-mm")&".xls...>.

Please note this is a network location <...\\Media\...>.

I was getting the #REF! error until I inserted the entire path. And
now I am getting the #VALUE! error instead! Yes, if both workbooks
were open, there were no errors.

Does this look like an OFFSET error (with a closed workbook)?

If so, does anyone know of any work around for this OFFSET limitation?

Any comments are welcomed.

-Minitman
 
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.
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

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"), ...


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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
If you're hoping to get =indirect() to work successfully with closed workbooks,
then you're going down the wrong path. It won't work with closed workbooks.
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

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

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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
It's not that I'm unimpressed with the addin. It's very nice. I just don't
have much call to retrieve data from closed workbooks when I have to build the
path/name/sheet and address.

My last suggestion still stands.

Try getting a smaller version of the formula to work. If you have trouble,
share what you tried.


I know.

I've been slamming into that wall for a couple of years now and have
mostly resigned myself to having to open these workbooks and suffering
the slow down that results from lack of memory and going to the swap
file. I've got the memory but my Excel can't see but 500megs of it.
I'll get to Vista and Excel 2007 when my ship comes in. Until then,
I'll keep looking.

I was told that this Morefunc INDIRECT.EXT would get the data from
closed workbooks which is why I tried it. It doesn't seem to be able
to do that or I'm doing something wrong. I get the impression that
you are not all that impressed with this addin, would you be willing
to elaborate?

Otherwise, do you know of ANY possible work around's that might work?

-Minitman

If you're hoping to get =indirect() to work successfully with closed workbooks,
then you're going down the wrong path. It won't work with closed workbooks.
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

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

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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
I can assure you that the Morefunc add-in is a high quality add-in that
includes *many* useful functions.

I have used INDIRECT.EXT on *stand-alone* machines with no problem. I don't
have a network to test it on.

There are some situations where it doesn't work but I don't remember what
those specific situations are. I've read about them here in this newsgroup
but reports of problems are rare.

If INDIRECT.EXT doesn't work then your only other option (other than opening
the source file) is a VBA utility called "Pull" by Harlan Grove. Basically,
it does the same thing as INDIRECT.EXT. I have never used the Pull utility
so I don't how robust it might be.

See this thread:

http://tinyurl.com/2grhzv


--
Biff
Microsoft Excel MVP


Minitman said:
I know.

I've been slamming into that wall for a couple of years now and have
mostly resigned myself to having to open these workbooks and suffering
the slow down that results from lack of memory and going to the swap
file. I've got the memory but my Excel can't see but 500megs of it.
I'll get to Vista and Excel 2007 when my ship comes in. Until then,
I'll keep looking.

I was told that this Morefunc INDIRECT.EXT would get the data from
closed workbooks which is why I tried it. It doesn't seem to be able
to do that or I'm doing something wrong. I get the impression that
you are not all that impressed with this addin, would you be willing
to elaborate?

Otherwise, do you know of ANY possible work around's that might work?

-Minitman



If you're hoping to get =indirect() to work successfully with closed
workbooks,
then you're going down the wrong path. It won't work with closed
workbooks.
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

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

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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
Do you not need a drive letter in front of your path?

Pete

I know.  

I've been slamming into that wall for a couple of years now and have
mostly resigned myself to having to open these workbooks and suffering
the slow down that results from lack of memory and going to the swap
file.  I've got the memory but my Excel can't see but 500megs of it.
I'll get to Vista and Excel 2007 when my ship comes in.  Until then,
I'll keep looking.

I was told that this Morefunc INDIRECT.EXT would get the data from
closed workbooks which is why I tried it.  It doesn't seem to be able
to do that or I'm doing something wrong.  I get the impression that
you are not all that impressed with this addin, would you be willing
to elaborate?

Otherwise, do you know of ANY possible work around's that might work?

-Minitman

If you're hoping to get =indirect() to work successfully with closed workbooks,
then you're going down the wrong path.  It won't work with closed workbooks.
Click to expand...
Minitman wrote:
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
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 notthe
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 founda
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
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*(D­AY(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
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*(D­AY(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"
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.- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
Hmmm...

I just noticed this:
=SUM(OFFSET(INDIRECT
Click to expand...

OFFSET will not work on a closed file, either! So, if you get the INDIRECT
syntax straightened out you'll just get another error with OFFSET.


--
Biff
Microsoft Excel MVP


T. Valko said:
I can assure you that the Morefunc add-in is a high quality add-in that
includes *many* useful functions.

I have used INDIRECT.EXT on *stand-alone* machines with no problem. I
don't have a network to test it on.

There are some situations where it doesn't work but I don't remember what
those specific situations are. I've read about them here in this newsgroup
but reports of problems are rare.

If INDIRECT.EXT doesn't work then your only other option (other than
opening the source file) is a VBA utility called "Pull" by Harlan Grove.
Basically, it does the same thing as INDIRECT.EXT. I have never used the
Pull utility so I don't how robust it might be.

See this thread:

http://tinyurl.com/2grhzv


--
Biff
Microsoft Excel MVP


Minitman said:
I know.

I've been slamming into that wall for a couple of years now and have
mostly resigned myself to having to open these workbooks and suffering
the slow down that results from lack of memory and going to the swap
file. I've got the memory but my Excel can't see but 500megs of it.
I'll get to Vista and Excel 2007 when my ship comes in. Until then,
I'll keep looking.

I was told that this Morefunc INDIRECT.EXT would get the data from
closed workbooks which is why I tried it. It doesn't seem to be able
to do that or I'm doing something wrong. I get the impression that
you are not all that impressed with this addin, would you be willing
to elaborate?

Otherwise, do you know of ANY possible work around's that might work?

-Minitman



If you're hoping to get =indirect() to work successfully with closed
workbooks,
then you're going down the wrong path. It won't work with closed
workbooks.

Minitman wrote:

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

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

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

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"

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.
Click to expand...
Click to expand...
Click to expand...
 
=SUM(OFFSET(INDIRECT.EXT("'\\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))
Click to expand...

This is the only thing I can get to work. Obviously, I can't reproduce your
exact path.

=SUM(INDIRECT.EXT("'C:\TV\["&TEXT(B12,"yyyy-mm")&".xls]Input'!G"&n&":G&n"))

Where n = calculated row to define the range.

In your formula above, the offset from A3 by 6 columns ends up at G3. So,
something like this:

....G"&3+27*(DAY(B12)-1)+2-2*ROW($A$1)

Then for the height (27), you'd need to do something like this:

....G"&3+27*(DAY(B12)-1)+2-2*ROW($A$1)+27

All of this aside, this still may not help the situation that you're trying
to avoid, consuming resources. I don't know for certain how INDIRECT.EXT
works but it's my suspicion that it opens another instance of Excel and the
source file hidden in the background thus consuming resources.



--
Biff
Microsoft Excel MVP


Minitman said:
Hey Biff,

"OFFSET will not work on a closed file, either!"

I didn't know that this was going to be a 2 part question. Thank you,
Biff, for bringing the OFFSET limitation to my attention, I had no
idea!

That might explain the #VALUE! error message I'm getting after I added
the full path to this formula (was #REF!).

Here is that formula again:

=SUM(OFFSET(INDIRECT.EXT("'\\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))

With B12 being a date that is converted into the number of the day or
how far to go down from the reference cell to get to area of interest
<...27*(DAY(B12)-1)+2-2*ROW($A$1)...> and the name of the closed
workbook in question <...TEXT(B12,"yyyy-mm")&".xls...>.

Please note this is a network location <...\\Media\...>.

I was getting the #REF! error until I inserted the entire path. And
now I am getting the #VALUE! error instead! Yes, if both workbooks
were open, there were no errors.

Does this look like an OFFSET error (with a closed workbook)?

If so, does anyone know of any work around for this OFFSET limitation?

Any comments are welcomed.

-Minitman



Hmmm...

I just noticed this:


OFFSET will not work on a closed file, either! So, if you get the INDIRECT
syntax straightened out you'll just get another error with OFFSET.
Click to expand...
Click to expand...
 
Back
Top