Correction:

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

Should be:

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



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=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))

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



On Mon, 17 Mar 2008 22:37:04 -0400, "T. Valko"
wrote:

Hmmm...

I just noticed this:

=SUM(OFFSET(INDIRECT

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.