Concatenate only shows formula not result

Hello everyone,

I was trying to use the concatenate function to pull information from multiple cells to create an IF statement that would then return a value from another report, except it keeps displaying the IF statement instead of the value. I'm not sure how to fix this. Is it a problem with my formula?

The reason I am using Concatenate, is because the report I'm pulling my values from changes daily. Those reports are monthly with each sheet being a Day. So I needed to locate the correct report, calucate which Day it was to grab the correct sheet and then grab the right cell.

The formula I am trying to use is:

=CONCATENATE("=IF('C:\Report Location\",TEXT(A10,"MMMM YYYY"),"\[",$E$1," - ",TEXT(A10,"MMMM YYYY"),".xls]",B10,"'!$C$8,'C:\Report Location\",TEXT(A10,"MMMM YYYY"),"\[",$E$1," - ",TEXT(A10,"MMMM YYYY"),".xls]",B10,"'!$C$8,","""-""",")")

Where:
Column A = Date (so A10 would be something like 5/6/2011)
Column B = Day (there is a formula in column B to caluate which day of the month the date is... from Day 1 to Day 23 - we don't count weekends) (so 5/6/2011 would be Day 5)
Row 1 = Site names (which are city names, like Miami, etc)
C8 = the cell value I need displayed from the other report

It displays the result:

=IF('C:\Report Location\May 2011\[Miami - May 2011.xls]Day 5'!$C$8, 'C:\Report Location\Mat 2011\[Miami - May 2011.xls]Day 5'!$C$8,"-")

which makes the IF statement I was looking for, but it should have either pulled the value from the other report located at C8 (which would be a number) or display a "-" to indicate no value.

I've included a sample excel doc of what I have.

Any help would be greatly appreciated.
Thanks

This works. Thank you.

My only problem now is that INDIRECT only seems to work if I have the other reports open.

Is there a way to pull the values from other workbooks when those workbooks are closed?