Using names referencing a cell in another workbook

[jimk]

Cell A1 contains the name (Book) of the source workbook. Cell A2 contains
the name (Sheet) of the worksheet in Book containing the desired info.
What do I put in cell A3 to get cell B51 from sheet Sheet in workbook Book?

 

[ryguy7272]

Not sure what you're doing over there, but try this:
=[Book1.xls]Sheet2!$B$51

 

[Dave Peterson]

Using Ryan's formula as a starting point:
=[Book1.xls]Sheet2!$B$51

This is the function you want to try:
="'["&a1&"]"&a2&"'!$B$51"

With Book1.xls in A1
and sheet2 in A2
(The apostrophes aren't always necessary, but they won't hurt if you don't need
them.)

But this won't work. It only builds a string that looks like a formula.

If that sending file is open in the same instance of excel, then you could use:
=indirect("'["&a1&"]"&a2&"'!$b$51")

But as soon as you close that sending workbook (and after excel recalculates),
you'll get an error.

=indirect() needs the sending workbook open.

But...

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.