Getting data from external worksheets

[steve.g]

Hi,
I hope you can help me.
I have a spreadsheet of test results that I need to run calculations on using data from sheets in other workbooks.

The setup is:
Col A: 1m Average
Col B: 1m Peak
Col C: 1M Min
Col D: Cal File Used
Col E: Corrected Average
Col F: Corrected Min

I want column E to display the average (A) minus (the contents of cell H40 in workbook "Acoustic nnn.xls" minus 99.6), where nnn is the value in Col D.

Column F should do the same for the minimum readings (using Cell H39 in the same sheet). All of the workbooks, Acoustic 001.xls to Acoustic 999.xls are in their own sub-directory( X:\Acoustic\Results, with the main spreadsheet in X:\ Acoustic)

I hope this explanation makes sense..

Thanks in advance for any help
Steve

[steve.g]

Hi,
I've tried various permutations along the lines of
=IF(ISNUMBER(D1),A1-((INDIRECT('Test1.xls'&"X:\Acoustic\Results\"&"Acoustic "&"Cal File used"&".xls"Sheet1!$H$40")-96.9)),"")
with no joy, so any help would be appreciated
Thanks

[JBeaucaire]

Maybe:

=IF(ISNUMBER(D1), A1 - INDIRECT("'X:\Acoustic\Results\[" & "Acoustic " & D1 & ".xls]Sheet1'!$H$40") - 96.9, "")

[steve.g]

Hi JBeaucaire,
I still get the '#REF!' error up in the rows with an entry in column D, unless the target file is open, in which case it works fine! Unfortunately, the #REF! comes back if I close the target file. But it's still a big step forward, thanks!
Not wanting to keep all of the calibration files open all the time, (there are hundreds), I'm sure there must be a way round this, isn't there??
Many thanks
Steve

[JBeaucaire]

INDIRECT doesn't work on closed workbooks. You've discovered that already.

If this is a MUST, then you'll have to install more functionality into your Excel program. Only people with the same installed ADD-IN will be able to use your sheet.

The ADD-IN most common is called MoreFunc...read all about it here:
http://xcell05.free.fr/morefunc/english/

Download and install it from here:
http://download.cnet.com/Morefunc/30...-10423159.html

Go into TOOLS > ADDINS and activate MoreFunc.

Now you have many, many new functions available to you. Any place you used INDIRECT(), now use INDIRECT.EXT() instead and it will work on closed workbooks.

[steve.g]

Brilliant!!
Thank you so much!
Steve