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

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.


--
Biff
Microsoft Excel MVP


"Minitman" wrote in message
...
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 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



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.


--
Biff
Microsoft Excel MVP

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.

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

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.

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