Hi all,
I'm hoping that I explain this one properly.
Basically I'm trying to do a vlookup on a file that sits on a network drive, that is NOT open on my computer. Basically a 'live' lookup.
The filename changes daily based on what day it is, in a YYYYMMDD format, but I can't get the formula right, or there's a larger issue.
I'm having issues with a value returning though. I've ended up with a #VALUE tag rather than a #REF tag.
WITHOUT VARIABLE FILENAME (working fine)
Returns that information I requested
=VLOOKUP($AH4,'\\xxx.xxx\xxx\xxx\xxx\[Filename_20190625.xlsx]Sheet1'!$P$8:$T$5826,2,0)
WITH VARIABLE FILENAME (not working)
Returns #VALUE
where AK1 =TEXT(TODAY(),"YYYYMMDD")
=VLOOKUP($AH4,"\\xxx.xxx\xxx\xxx\xxx\[Filename_"&$AK$1&".xlsx]Sheet1!$P$8:$T$5826",2,0)
NOTES
- I've read that INDIRECT cannot work to a closed file? I have tried wrapping it in INDIRECT tags but can't get the formula correct to even test it.
- The excel file has 10,000 odd rows and since I've implemented the formula above it's slowed down A LOT! The server is quite local.
Bookmarks