files

Posted by Vincent Edwards on November 20, 2001 9:29 AM

I hope you can help me!
column A: employees' initials
column B: hours worked extracted from their timesheet. The timesheets always include the person's initials in the file name (ex: 2001-11-VE.xls refers to novembre 2001 for Vincent Edwards)

How can I refer to a filename that depends on the value in another cell? For example if A1="VE", B1 will lookup a value in file 2001-11-VE.xls and if I change A1 to "JFK" it will lookup in file "2001-11-JFK.xls"

Thanks for your help!

Vincent Edwards

Posted by Juan Pablo on November 20, 2001 9:43 AM

Use the Indirect function

=INDIRECT("'[2001-11-" & A1 & ".xls]Sheet1!G5")

This will look in the contents of Sheet1, G5, in the specified Book by A1.

Juan Pablo