Change of File name within a cell reference
 

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!

Change of File name within a cell reference
 

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

Change of File name within a cell reference
 

Thank you very much. You saved me two days of work =). Kostiz you are a star!

"vezerid" wrote:

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

Change of File name within a cell reference
 

Glad to know it worked :)

Kostis