Question about referenced cells/formulas

[alexj]

Can someone explain to me how I can turn "Kanoodle" and "012605" into referenced cells (from a different workbook, obviously) in this formula?

='[AllTime-KanoodleROI-012605.xls] Summary'!$E$7

I figured something like this would do the trick, but I'm not too familiar with Excel:

='[AllTime-', 'Kanoodle', 'ROI-', '012605', '.xls] Summary'!$E$7

Apparently, it's harder to break text out of a formula than it is in PHP :x.

Thank you in advance.

 

[Andrew Poulsom]

If C3 contains Kanoodle and G3 (formatted as Text) contains 012605, this formula:

="'[AllTime-"&C$3&"ROI-"&G3&".xls]Summary'!$E$7"

returns

'[AllTime-KanoodleROI-012605.xls]Summary'!$E$7

which is exactly the same as the normal formula:

'[AllTime-KanoodleROI-012605.xls]Summary'!$E$7

Maybe it's the space before summary that's causing your problem. If the normal formula works, so should this:

=INDIRECT.EXT("'[AllTime-"&C$3&"ROI-"&G3&".xls]Summary'!$E$7")

but, of courese, the workbook must be open because you haven't included a path.