I think you're seeing that the formula hasn't reevaluated. With the sending
workbook closed, select that cell with the =indirect() formula and hit F2
followed by enter.

What happens?

===========
I don't use =indirect.ext(), but maybe...

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT(("'\\server\share\folder\[Target File.xls]TCMetadata'!A2:G" &
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!A2")), 7)

(Untested.)

(I hardcoded the G into the formula instead of using =address(..., 7). And I
dropped the $ signs from the strings (since it's in a string, it won't matter).

One suggestion...

If the used range of that sending worksheet isn't too much (you'll have to test
it), maybe you can drop the count_tc stuff and just use the entire column.

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!A:G"), 7)




JG wrote:

Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.
Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.

I have this formula in Work.xls which uses indirect.ext() by itself, and it
is pointing to a single cell reference:
=INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!$A$2")

This returns 1049, and I see that value whether Target File.xls is open or
closed.

In this formula, indirect.ext() is inside a vlookup:

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!$A$2:" &
ADDRESS((INDIRECT.EXT("'\\server\share\folder\[Target
File.xls]Count_TC'!$A$2")+1), 7)), 7)

I get a value returned just fine when Target File.xls is open, but not when
it's closed. Can I not use indirect.ext() to get data from a closed
spreadsheet to drive a vlookup()?

Oh, and =indirect("rc1",false) was cool, thanks for that.

"Dave Peterson" wrote:

First,
=INDIRECT(ADDRESS(ROW(), 1))
can be replaced with:
=indirect("rc1",false)
(same row, column 1)

But =indirect() won't work if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.


JG wrote:

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

...only to end up with #REF. Is this only going to work within the same file?

Thanks.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)

--

Dave Peterson


--

Dave Peterson