Change of File name within a cell reference

Z

Zuzana

I am trying to create a spreadsheet looking like:
date1 date 2
date
name =vlookup(name,file name date1 ,2,false) vlookup(name,file name
date2...)

I need to Vlookup data from different files within same folder, the files
names contain a date within : eg. New format 1.12.2005.xls, next New format
2.12.2005.xls.

The spreadsheet I am trying to create has each column heading with a
different date and based on the date I would like to reference the file whose
name contains the date and return the value from that file. How can I do
this? Can anybody help me?

Thank you!
 
V

vezerid

Zuzana,
You need something like:
=VLOOKUP($A2, INDIRECT("'[New format "&B$1&".xls]sheetname'!A:B"), 2,
0)

The INDIRECT() function builds dynamic addresses. $A2 is "dollared" so
that the lookup value is always in column A:A. B$1 is dollared so that
the date is always in row 1:1. Hopefully all referenced workbooks will
have the data in a sheet named identically. Otherwise you might need to
have a nested VLOOKUP(), with the sheet names per date.

Write back if you need details.

HTH
Kostis Vezerides
 
Top