1

i have a folder with .xls files which are all named like xxx_day_month_year.xls and i want to look up for some values in this files by selected date.

I came to next solution:

=VLOOKUP(B3;INDIRECT("'G:\xxxxxxx..\"&G3&"."&G4&".2014_test.xls]HL'!$C:$D");2;FALSE)

Where cells G3 and G4 are desired day and month.

But this only works if I have opened documents in which I look up for data. Is there a way that this formula is going to work in independent document?

klement
  • 11
  • 3
  • There is a similar, but not identical, post about external links and open/closed workbooks [here](http://stackoverflow.com/questions/19635020/why-do-some-excel-documents-have-to-be-open-for-a-vlookup-and-not-others). This may be useful. Also, have you tried running the `VLOOKUP` function without the nested `INDIRECT` function? I know for a fact that `VLOOKUP` will work when referencing a closed workbook, but I am not sure if the `INDIRECT` fcuntion will do the same. –  Mar 25 '14 at 19:38
  • `INDIRECT()` doesn't work with closed workbooks. If you want a similar solution to this problem then have a look in [**HERE**](http://stackoverflow.com/questions/18637376/query-my-excel-worksheet-with-vba/18640226#18640226) and learn to use ADODB to query a closed workbook –  Apr 28 '14 at 07:36
  • Also a look in [**HERE**](http://vba4all.wordpress.com/category/vba-macros/various-ways-to-pull-data-from-another-workbook-closedopened/) –  Apr 28 '14 at 07:37

1 Answers1

0

I did a bit of digging on my own, and it looks like the INDIRECT function will not work with a closed workbook. However, you could use VBA to accomplish your task.

Take a look at this previous post which explains a VBA workaround for a similar issue. You may be able to adapt it to your specific situation.

Community
  • 1
  • 1