write a formula including text on it

[rrporto]

Hello all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I want to write a formula that part of it is written, as a text, in another cell.<o></o>
I common circumstances I should use the “indirect” but I am referring to a closed workbook … so no way to use it.<o></o>
<o> </o>
Basic I need the same functionalities as when a simple formula is written. E.g.:<o></o>
='C:\Documents and Settings\ ....\[CITY AUDIT REPORT.xls]Capa'!$C$5<o></o>
So that every time I open this workbook, excel asks me if I want to update the formulas…<o></o>
<o> </o>
The thing is: I don’t want to write a new formula (referring to another workbook) every single time, instead I want the formula to change every time I change the name in a cell. e.g:<o></o>
='C:\Documents and Settings\ ....\[“CellA10”.xls]Capa'!$C$5<o></o>
<o> </o>
I need something with the same functionalities as indirect, but using part of the path that is written in cell A10. Is it possible? Other than VBA?<o></o>
<o> </o>
Thanks a lot<o></o>

 

[MrKowz]

I think you can use INDIRECT.EXT. Check out http://xcell05.free.fr/morefunc/english/indirect.ext.htm which I beleive requires the Morefunc addin which you can download here: http://xcell05.free.fr/morefunc/english/index.htm

Otherwise, a VBA Worksheet_Change macro would be required.