vlookup and indirect functions

is the workbook open? indirect only works on open workbooks

codeslozer, did you read Martin's post?

Originally Posted by martindwilson

is the workbook open? indirect only works on open workbooks

for it to work on CLOSED workbooks - as the OP said they were doing - they need to install the Morefunc add-in

edit: is there an echo in here, Jerry? lol

Yes of course. But for that, the file needs to be locally available. If shared path then should be mapped to a network drive. I've tried performing it with "\\170.21.4.3..." kind of links but haven't been successful so far. So, can't guarantee if there's a way for that or not. For this, you'll need the path in three pieces to do a VLOOKUP.

1. Path without Filename but ending with a backslash. Say B2 stores this path. Then B2 = C:\Users\cOdEsLiZeR\Desktop\
2. File name. Say C2 stores file name. Then C2 = TestParent
3. Sheet name and range. Say D2 stores this details then D2 = Sheet1'!$B$2:$C$10
** Please note, the single quote after sheet name but its not available before it.

Then if the referencing value is available in E2, the VLOOKUP formula would come up something like this:

=VLOOKUP(E2,INDIRECT(CONCATENATE("'",B2,"[",C2,"]",D2),TRUE),2,0)

Here, you must note: the single quote as the first element in CONCATENATE as the path passed is like

'C:\Users\cOdEsLiZeR\Desktop\[TestParent]Sheet1'!$B$2:$C$10

@Codeslizer, I think the OP is asking is there any way to use INDIRECT() to get a value from a CLOSED workbook. INDIRECT() as you've demonstrated does work if the referenced workbook is open, else it does not.


Here's a similar thread that provided a VBA solution to accomplish this.


There's also an ADD-IN on the internet called MOREFUNC that adds 100s of new functions to your Excel, one of which is INDIRECT.EXT(), a replacement for INDIRECT(), and this one is purported to work on closed workbooks.

Hey Guys, with all due respect.. I was only trying to share what I experienced. Dunno, for what reason that INDIRECT formula was fetching me the data, I tried looking up for.. .. and yes, I did read Martin's post so I closed the main file and reopened it with making sure the source file with table array is closed. Still the number showed up. Refreshed the formula pressing F2 and Enter. Still gave me the numbers.. Yes, calculations were automatic. So, thought maybe the formula works on closed workbooks too.. :D

I must say.. things didn't work out now when I tried again in a new workbook.. :p searched and found yeah.. It never works too..!!

Once the workbook is open, the data will appear. Even if you close the workbook the prior answer may remain, that's proabably just luck. In general, as you've discovered, it does not.

See post #5 for some alternate avenues you can explore.