Referencing Cells in Embedded MS Excel files

[lewisfiecke]

I am using Win7, MS Xcel 2007.

I currently have a template for doing cost calculations for products. I am trying to have one summary Xcel file with multiple embedded Xcel files, the embedded files being the calculator mentioned above.

My goal is: A summary file would contain multiple embedded (not linked) Xcel files. The summary file would update if one of the embedded calculator files are modified. I wish to use embedded files for ease of sharing and moving the files when needed, and because links tend to break over time.

I can open the embedded object, then in the Summary file, I type "=" and select the cell in my embedded object. The value shows up in the summary, and the formula reads "='[Worksheet in Embedded Test]Cost Overview'!$C$8"

The problem I have is: When I try to have another formula tied to a value in another embedded file, I get the exact same formula. So when I update embedded file 2, the value that was linked to embedded file 1 changes to that in embedded file 2. There is no distinction between embedded objects in the formula bar. However, he object numbers are different, and I can select the objects by number in a macro, I do not know how to use this to my advantage in a simple formula.

I have tried using different named ranges in the different embedded files, but this simply results in an "#NAME?" error.

I've started down the macro path, but I'm having some difficulty accessing the embedded objects with a macro. This is less desired, since it's more work.

Any tips, suggestions, or other forms of assistance would be greatly appreciated.

Thanks!