INDIRECT(ADDRESS) inside a VLOOKUP

F2/Enter didn't work, neither did recalc....

To your formula ideas below, both of those options worked. With the file
open. But neither worked when the target was closed.

"Dave Peterson" wrote:

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)

Dave Peterson