You would need an add-in, the easiest would probably be to download and
install Laurent Longre's Morefunc
http://xcell05.free.fr/english/
INDIRECT.EXT will work with closed workbooks
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
wrote in message
ups.com...
Peo Sjoblom wrote:
Actually you don't have to remove the leading apostrophe in B42
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
"Peo Sjoblom" wrote in message
...
It gets hung up on the apostrophe since it is both part of
workbook/sheet
naming and also a text precedent, remove the leading apostrophe in B42
like this
[Award Stats.xls]Stats'!B304
then in B43 use
=INDIRECT("'"&B42)
also note that if you close the source workbook indirect will not work
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
wrote in message
oups.com...
Two situations:
1) Create two new workbooks
2) In one workbook (Workbook1) enter value of 200 in cell A1
3) In second workbook (Workbook2) enter [Workbook1]Sheet1!A1 into cell
A1
4) In cell A2 in second workbook, enter =Indirect(A1)
5) I get back 200
Situation 2
1) I have two large workbooks
2) In one workbook, called [Award Stats], in worksheet 2006 Stats, in
cell B304, I have value of
2,000,000
3) In another workbook, in cell B42, I have the following: '[Award
Stats]2006 Stats'!B304
4) In cell B43 in second workbook, I have =Indirect(B42)
5) I get back #REF!
Why the difference?
Thanks - is there no way to do this w/o having the other worksheet
open?
The idea here is for a user to type in (or select from a validated
list) the name of a file.
The file will always be of a similar format (2006 Stats, 2005 Stats,
etc.). In some cases
it would be monthly rather than annually - not that that matters.
I'd want to limit the user's work to just picking the file name w/o
having to open all the other files as that sort of defeats the purpose.