Vlookup (indirect or some other method)

[aka_krakur]

I have a vlookup that I am trying to lookup a file on the network.
When the file is open it's no problem and the simple method works

=vlookup($a2,INDIRECT("'[Filename.xls]Q" & 
Roundup(month(today())/3,0) & "-" & 
text(today(),"YYYY") & "'!$A:B"),2,FALSE)

So I found some solutions online that I was hoping would work;
and I just can't seem to get it to work. The results of the below code give me a #REF! result

The results of the long network path is because I want it to update according to how the folders/files will update for future quarters/years.
so path of the file today is truly:
\\NETWORK1\folder1\2011\2011 BSI Folder\Q4 2011\Filename.xls with a worksheet tab being Q4-2011

=vlookup($a2, INDIRECT("\\NETWORK1\folder1\" & TEXT(TODAY(),"YYYY") & 
"\" &TEXT(TODAY(),"YYYY")&" BSI Folder\Q" & ROUNDUP(MONTH(TODAY())/3,0)
&" " & TEXT(TODAY(),"YYYY") &"\'[Filename.xls]Q" & ROUNDUP(MONTH(TODAY())/3,0) 
& "-" & TEXT(TODAY(),"YYYY") & "'!$A:G"),2,0)

Any help would be greatly appreciated. Not sure what I'm getting wrong here.

 

[Aladin Akyurek]

INDIRECT won't work with closed workbooks. You could invoke INDIRECT.EXT instead from the free morefunc.xll add-in or the PULL function, written in VBA by Harlan Grove.

By the way, try to construct the path and all that in a cell of its own and feed that cells address to INDIRECT.EXT.

 

[aka_krakur]

Thanks, I have downloaded and installed Morefunc.xll add-in.
I have also put the 2nd portion of my original formula into cell A1
so A1 =

="\\NETWORK1\folder1\" & TEXT(TODAY(),"YYYY") & 
"\" &TEXT(TODAY(),"YYYY")&" BSI Folder\Q" & ROUNDUP(MONTH(TODAY())/3,0)
&" " & TEXT(TODAY(),"YYYY") &"\'[Filename.xls]Q" & ROUNDUP(MONTH(TODAY())/3,0) 
& "-" & TEXT(TODAY(),"YYYY") & "'!$A:G"

I have attempted to use the Indirect.ext function; but am confused by how it works (or that maybe it is still not working for me or I have something wrong.

Do I still use the beginning portion of my formula? =vlookup($a2, and then put my Indirect.ext formula after that?t I g
If so, I have something wrong.
Or does the Indirect.ext act as a different kind of vlookup?
Here's what I obtained as far as a formula goes with indirect.ext

=indirect.ext(a1, false)

Shouldn't I be able to write:

=vlookup(a2,indirect.ext(a1,false),2,false)

 

[aka_krakur]

I figured it out; it does not work with vlookup. But I found a Pull macro that you mentioned earlier; and I think that's going to work; however, it is very slow. I think I will just write instructions to the user to copy/paste (a lot more simple).

 

[Aladin Akyurek]

I figured it out; it does not work with vlookup. But I found a Pull macro that you mentioned earlier; and I think that's going to work; however, it is very slow. I think I will just write instructions to the user to copy/paste (a lot more simple).

As far as I know, there is no reason why INDIRECT.EXT would not work with VLOOKUP, although I recall some issues involving network drives which were addressed in later versions of add-in...

=VLOOKUP(LookupValue,INDIRECT.EXT("'"&A1&"!$A:G"),2,0)

would succeed as long as A1 does contain a valid path.