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!
Bookmarks