Vlookup not working with closed files

Hi.

I have a spreadsheet (destination) that in various places links to ten other spreadsheets (sources) via the VLOOKUP function.

FOUR of those ten work fine. The sheet receives the values from the sources via the VLOOKUP without opening the source files.
SIX of the VLOOKUPS will not work unless I open the source files. I can immediately close them again, but that isn't convienient and I am sure sucks up the computers limited resources.

This is one of the VLOOKUP formulas that work just fine. I DO NOT have to open the source to see the updated data in the destination file.

Please Login or Register  to view this content.

This is one of the VLOOKUP formulas that do not work unless I open the source file after opening the destination file.

Please Login or Register  to view this content.

I'm sort of bummed. I learned all about index/match and used it in the second formulas place. Then discovered that the source had to be open. So I rebuilt the formulas with vlookups. Now I have somehow messed things up.

Thanks in Advance.

Originally Posted by FDibbins

INDEX/MATCH, like VLOOKUP, should have no problem reading from closed files.

It never did for me. A google search took me to a page that stated index/match only works with the data source file open.

Originally Posted by FDibbins

If those are the same exact formulas that you are using, try removing the IFERROR() from teh 2nd formula and adding it to the 1st formula (I dont see how this would make a difference, but try)
Are you sure the path is correct for the 2nd 1? (grasping at straws here,

I took out and added the iferror() portion and nothing changed. The path is correct since the formula works fine once the source files are opened.

Another question: How do you tell how many instances of excel are going on? I did read that if there was more than one instance open then the lookup would fail until the file was opened in the other instance.

I boot up then double click on the destination file. It opens. The rest of the day I either right click on the excel icon at the taskbar on my desktop and select a file, or I do a cntrl+O if I am working in the file. So, to my laymans brain that sounds like ONE instance of excel. Is that right?

I appreciate the time and thought. I can live with it. I just don't like being confused....

I agree with Ford, to the best of my knowledge, both VLOOKUP and INDEX/MATCH work just fine accross closed workbooks.

If you have 10 of them, 4 DO work, 6 DON'T, logic should tell us that it's not simply because vlookup doesn't work on a closed workbook.
If that was the reason, 0 of them would have worked.

There must be another reason.

Can you clarify what "doesn't work" means ?
Do you get an error? What error?
Do you get the wrong result? What result DO you get? And in what way is that different from the result you expected?