Nested Cell Reference within External Workbook Reference

Hello,

I'm new to this forum, and i'm having some trouble with a formula that references data from a closed external workbook.

I currently have this formula which works just fine:

='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[24-Mar-09.xls]Sheet1'!$D$8

However, I want to create a system where the date is inputed into a different cell, (so cell E2 has the text 24-Mar-09) and have the formula reference that cell.

Essentially I want something like this (if it were only that simple)

='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[=E2&".xls"]Sheet1'!$D$8

I tried doing a nested concatenate formula which also didn't work:

='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[=concatenate(E2,".xls")]Sheet1'!$D$8

Basically, how can I get this nested formula to work, so that I have the external workbook shell reference, which uses the date in a different cell to specify which workbook to retrieve the data from.

I have been struggling with this for too long now, so any help would be much appreciated.

Thank you!

Thank you, I appreciate the response, but the formula did not work. Also the date formatting is a non-issue because I already converted it into text using the text function.

I just need a way to reference a closed external workbook, with a cell reference within that reference.

The function below works fine:
='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[24-Mar-09.xls]Sheet1'!$D$8

I just need a function similar to this:
='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[=E2.xls]Sheet1'!$D$8

Where cell E2 has 24-Mar-09 inputted as text .

Thanks!