Fetching Data from Multiple Name Changing Sheets with VlookUp

[29sandesh]

Hi team,

I am trying to fetch a data from the sheet titled:

Sellerwise Live Listings_20131003.xlsx The sheet will change its name next week to : Sellerwise Live Listings_20131010.xlsx and so on. The vlookup that works is =VLOOKUP(A10,'[Sellerwise Live Listings_20131003.xlsx]Sheet1'!$A:$C,2,FALSE) I am looking for a vlookup, so that I dont need to open the excel sheet every week. I am trying concatenating. Chk attached pic

[martindwilson]

you cant just concatenate you need indirect to build the path/reference BUT that does not work with closed workbooks

[29sandesh]

Ah darn it means I will have to open the other sheet to get values into my current sheet. Ok can you help me with the INDIRECT formula. Now the path looks like:

=VLOOKUP(A8,'G:\Marketing\Category_Management\Eswar\Daily Live Listings\Sellerwise Live Listings\[Sellerwise Live Listings_20131003.xlsx]Sheet1'!$A:$B,2,FALSE)

[29sandesh]

Ok concatenation successful, but I have heard we can extract data from closed files, check out this post:

ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

[martindwilson]

yes but that needs the morefunc add in which can be a bit hit or miss and uses indirect.ext() not indirect() there is a link in point 4 of that page to download it

[Hercules1946]

There is a way to get information from a closed workbook, using an Excel4 Macro. To make it work you need to set up VBA code in your open workbook, that can be run from a button on the worksheet. (The later versions of Excel still allow the use of these Macros).

Take a look at this thread, particularly post #8:

http://www.excelguru.ca/forums/showt...erent-Workbook