Formulae attaching external workbook with named reference

[andyhofer]

I am trying to reference an external workbook via a formulae.

=SUM('G:\express1\WORKDATA\EXCEL\USERS\Becks\WEEKLY\FUEL\[Ne
Fuel1.xls]FUEL CHARGES'!$O12:$P12)

The above works fine but I want the 1 in the file name to be picked u
from a cell rather then be hard coded into the formulae.

Therefore if column B is week 1, column C week 2, D week 3, and so on
when I copy the formulae that refers to week 1 across columns, it wil
automatically change the workbook reference to the new week number.

I have tried the below
=SUM('[="New Fuel"&C2&".xls]FUEL CHARGES"'!$O11:$P11)
but it returns #REF!

Tricky for me to explain, but I hope someone can helps from the above

Thank

Attachment filename: fuelconsumption.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=49464

 

[Hans]

Hi

The following site might help:
http://j-walk.com/ss/excel/usertips/tip015.htm


regards
Hans

-----Original Message-----
I am trying to reference an external workbook via a formulae.

=SUM('G:\express1

\WORKDATA\EXCEL\USERS\Becks\WEEKLY\FUEL\[New
Fuel1.xls]FUEL CHARGES'!$O12:$P12)

The above works fine but I want the 1 in the file name to be picked up
from a cell rather then be hard coded into the formulae.

Therefore if column B is week 1, column C week 2, D week 3, and so on,
when I copy the formulae that refers to week 1 across columns, it will
automatically change the workbook reference to the new week number.

I have tried the below
=SUM('[="New Fuel"&C2&".xls]FUEL CHARGES"'!$O11:$P11)
but it returns #REF!

Tricky for me to explain, but I hope someone can helps from the above

Thanks

Attachment filename: fuelconsumption.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=494641

 

[Frank Kabel]

Hi
normally INDIRECT would do but this would work only if the other
workbook is open. Seeing your existing formula I assume the other
workbook is closed. So you may try the following alternatives:

Have a look at the Add-In MOREFUNC.XLL
(http://longre.free.fr/english)

use the function INDIRECT.EXT.e.g.
=SUM(INDIRECT.EXT("'G:\express1\WORKDATA\EXCEL\USERS\Becks\WEEKLY\FUEL\
[New Fuel" & C2 & &".xls]FUEL CHARGES"'!$O11:$P11"))
not tested.

Could be generating an error as you want a range (O1111) as return.
If this does not work you may also have a look at the following thread
(describing further alternatives for accessing closed workbooks):
http://tinyurl.com/2c62u