Referencing external file with Index/Match functions

**valeous** · 09-15-2010, 02:03 PM

I am trying to run a simple Index/Match query in one excel file where it indexes from an external file. Now the file gets renamed every other day. So I want a way to refer to "any" or * file in a particular folder. Here's the query I have right now:

=INDEX('http://....../[ADV_Project_List_09-08-10.xls]Project Mapping Data'!$B$2:$B$128,MATCH(K14,'http://....../[ADV_Project_List_09-08-10.xls]Project Mapping Data'!$W$2:$W$128,0))

Now you can guess that the date is what changes about the filename. The old file gets moved out of the folder so at all times there's only one file in that folder. All I need to do is refer to just that one file somehow and be able to do that without having to change the date in my query every day.

Is there a way I could do something [*.xls] instead of using the complete filename?

Please let me know if this doesn't make sense. Thanks for your help.

**valeous** · 09-16-2010, 02:16 PM

bump! The first of I imagine many.

**NBVC** · 09-16-2010, 02:27 PM

Once you start playing with "indirectly" referring to a outside workbook.. you will need to introduce the INDIRECT() function.. but the catch is that it only works if the other workbook is opened.

There is a free addin called Morefunc.xll that has a INDIRECT.EXT function that works with closed workbooks and it would be applied like this:

=INDEX(INDIRECT.EXT("'http://....../[ADV_Project_List_"&TEXT(A1,"dd-mm-yy")&".xls]Project Mapping Data'!$B$2:$B$128"),MATCH(K14,INDIRECT.EXT("'http://....../[ADV_Project_List_"&TEXT(A1,"dd-mm-yy")&".xls]Project Mapping Data'!$W$2:$W$128"),0))

Where you would enter the date in cell A1 of the active workbook that corresponds to the date in the file name to get.

Note: You may need to change the TEXT(A1,"dd-mm-yy") parts to TEXT(A1,"mm-dd-yy") depending on how you use dates.

**valeous** · 09-16-2010, 04:00 PM

Now would this still work if this spreadsheet was uploaded on a Sharepoint site?

And would it still display the matched items on PCs that do not have this excel add on?

Thanks

**NBVC** · 09-16-2010, 04:25 PM

About the Sharepoint... I do not know.. I don't have that experience with Sharepoint.

You can embed the Morefunc.xll addin into your XL2003 workbook (it will end up in Tools|Morefunc), and then it will be available to the other users so they don't need to physically install it.

Please don't use the Quote button.. Use the Reply button. Thanks.

**valeous** · 09-20-2010, 04:45 PM

Now if I wanted to forget about the whole date thing and simply use asterisk to refer to the excel file since its the only file in that folder, how would I do that?

Thanks