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]

You would need to use INDIRECT like this:

=INDIRECT("'[AllTime-"&"Kanoodle"&"ROI"&"012605"&".xls]Summary'!$E$7")

You can replace "Kanoodle" and "012605" with cell references.


The source workbook must be open.

 

[alexj]

Thanks, Andrew.

However, this document needs to be viewable by anyone in my dept. at any given time. Do you know if there's a way to pull those references from closed workbooks?

Thanks again,

Alex

 

[Smitty]

Laurent Longre's Indirect.EXT will reference closed workbooks. It's part of the Morefunc add-in, which you'll find here.

You can also use Harlan Grove's PULL function.

Hope that helps,

Smitty

 

[Andrew Poulsom]

You can use INDIRECT.EXT from MOREFUNC here:

http://xcell05.free.fr/english/

 

[alexj]

^ Thanks, I'll try that out. Meanwhile, can someone explain why this keeps returning the dreaded "#REF!" error?

=INDIRECT("'[AllTime-"&"C3"&"ROI-"&"F3"&".xls]Summary'!$E$7")

 

[Andrew Poulsom]

^ Thanks, I'll try that out. Meanwhile, can someone explain why this keeps returning the dreaded "#REF!" error?

=INDIRECT("'[AllTime-"&"C3"&"ROI-"&"F3"&".xls]Summary'!$E$7")

Remove the quotes around your range references "C3" and "F3".

 

[alexj]

I have the morefunc thing installed...yet it seems as though Excel isn't recognizing it. I tried pulling the data from a closed workbook and I'm getting a "#VALUE!" error.

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

 

[Andrew Poulsom]

In a spare cell enter:

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

In another cell enter the formula as it should appear without the = sign. Do they look the same?

 

[alexj]

No, the first one looks like this:

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

...and the second one looks like this:

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

Thanks,

Alex