vlookup formula with dynamic cell reference

[sitewolf]

I'm building a spread that will pull information from another file on the network, but I'd like the file reference within the vlookup to be partially built with information in file containing the formula....not sure I'm saying that well.

i.e. say I'm wanting to pull in information from a file in a folder in my D drive- directly the reference would be something like =vlookup(A2,[D/Test/Info2018.xlsx/Sheet1]A:B,2,0)....but I have 2018 in cell A1 and want the formula to draw from there.

How would I build the vlookup formula so that the 2018 portion is derived from A1 so I can change years I'm getting information for simply by changing that cell, not editing all the vlookups?

 

[Johnny C]

You need (unfortunately) INDIRECT
=INDIRECT("VLOOKUP(A2,[D/Test/Info"&$A$1$".xlsx/Sheet1]A:B,2,0)"
This takes a string and turns it into a reference.

Unfortunately ... if you type something into A1 where there is no file (e.g.20188) it may lock your computer up for a long time. I haven't tried it for 10 years so they may have fixed it, but at one time it would lock your PC if you INDIRECTed to a closed file that didn't exist. That's why I avoid INDIRECT if at all possible for external references. But that's my personal preference

 

[sitewolf]

Yeah, I can't chance that, being on a government network drive and all.

I was hoping to be able to do this for ease of maintenance, but also because I could use it in creating a dashboard to move between sets of data.
No other way that doesn't chance locking up an entire network drive...or even just a user's puter?

 

[FDibbins]

You need (unfortunately) INDIRECT
=INDIRECT("VLOOKUP(A2,[D/Test/Info"&$A$1$".xlsx/Sheet1]A:B,2,0)"
This takes a string and turns it into a reference.

Unfortunately ... if you type something into A1 where there is no file (e.g.20188) it may lock your computer up for a long time. I haven't tried it for 10 years so they may have fixed it, but at one time it would lock your PC if you INDIRECTed to a closed file that didn't exist. That's why I avoid INDIRECT if at all possible for external references. But that's my personal preference

Both your formula and your claim are, unfortunately, incorrect

The formula would be something like...
=vlookup(A2,indirect("[D/Test/Info"&A1&".xlsx/Sheet1]A:B"),2,0)
However, I would avoid full-column references when using INDIRECT, it will slow your file down

INDIRECT will only work on open workbooks, it will return an error if the referenced WB is closed or does not exist, it will not lock the computer, nor will it take "ages" to run.

INDIRECT, when used carefully, can be a very useful tool for including text or some other reference in a formula - with the condition that the referenced file MUST be open.
If that is not practical, maybe take a look at the Morefunc add-in for excel, it lets you use INDIRECT on closed WB's
http://www.ashishmathur.com/tag/morefunc/

 

[Johnny C]

Lol well spotted.

They fixed INDIRECT then, I last used it in 2006 (XL2003?) and it let you reference unopened documents, and went AWOL if they didn't exist. First time it happened it took an hour. that's why I haven't used them since.