Indirect Function

[ingleg]

I have used this when linking to sheets in the same WorkBook but how do I link to different workbooks?

I have the formula:

=SUMIF(INDIRECT("'"&F$3&"'!$A:$A"),Summary!$A5,INDIRECT("'"&F$3&"'!$d:$d"))

Cell F3 is also the name of the External WorkBook and is in Directory:

V:\Data\Excel\Test

Any ideas?

Thanks in advance.

[DonkeyOte]

INDIRECT will only work with external files if they are open... if the target file is closed you will need to think of using INDIRECT.EXT which forms part of Laurent Longre's morefunc.xll add-in

info: http://xcell05.free.fr/morefunc/english/
download from: http://download.cnet.com/Morefunc/30...-10423159.html

[ingleg]

The external file will be open.

The sumif will be used to bring the data into a master sheet, and then the formulas will be set as values.

[DonkeyOte]

The best way to determine the required syntax is to create a SUMIF manually to the file in question... this gives you the basics to recreate.

In short if the target file is open and:

F3: [file.xls]Sheetname

Then:

=SUMIF(INDIRECT("'"&F$3&"'!$A:$A"),Summary!$A5,INDIRECT("'"&F$3&"'!$D:$D"))

You do not need the file path etc... just file & sheet names.