Note that INDIRECT only works with open files. If you want to work
with closed files (which is implied by your use of the path) then you
will need to get the free add-in morefunc and use INDIRECT.EXT.

Note also that you need square brackets around the filename, and the
TEXT function in Tom's reply should probably be:

TEXT(A3,"m-d-yy")

Also, there is a ! missing in the formula.

Hope this helps.

Pete

On Oct 22, 4:14*pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote:
If your string is evaluated to the right result then just put an INDIRECT
around it...

For example if A1 has the string Sheet2!A1
=INDIRECT(A1) will evaluate to =Sheet2!A1 and give you the value in Sheet 2 A1



"Michael" wrote:
I am attempting to concatenate a cell value (date) with a file name to create
a data reference to a value within an Excel file. *If I use the absolute file
name the function works but if I build the file name by concatenating the
date the only thing the spreadsheet does is display the file name an not
capture the referenced data cell.

The dates are represented in cells A3, A4, A5, A6, etc as 2/2/08, 2/3/08,
2/4/08, 2/5/08, etc.

The formula in cells B3, B4, B5, B6, etc. is:

=CONTATENATE("='C:\ConsolPeriod Report
",MONTH(A$3)&"-"&DAY(A$3)&"-"&RIGHT(YEAR(A$3),2),".xls'"&"!SData")

Where the reports are stored as "Period Report 2-2-08", "Period Report
2-3-08", *"Period Report 2-4-08", etc.

Any suggestions on how to get the formula to grab the data in the cell named
"SData" in each of the reports instead of just displaying the name
of the report?

Thanks for the brain power of the group!- Hide quoted text -

- Show quoted text -