SUM function (with variable?)

[nhbcai]

Hi there,
I am at the end of my wits with this problem which seems like it should be an easy one.

I want to populate information from a workbook that's not open using the SUM function and a name in the B1 cell:

Cell B1 = fileName.xlsx

Function = :
=SUM('C:\Folder\[B1]Sheet1'!A10:A100)

I haven't got much "advanced" excel knowledge but coming from a bit of programming experience this looks like it should work. B1 is just a variable and I am plugging in the variable.

My Google-fu has resulted in macros and VB stuff I was really hoping to avoid.

Any thoughts?

Thanks!

 

[Misca]

There's a major problem with working with closed workbooks. Not all functions can handle them:

Closed workbooks are not much of a problem with SUM or SUMPRODUCT but the only function that I know of that can turn cell values into a range reference would be INDIRECT and it can't handle closed workbooks.

If Excel Addins are not out of the question you could try installing the Morefunc -addin (http://xcell05.free.fr/morefunc/english/) to your Excel. It comes with an advanced INDIRECT-function that should be able handle closed workbooks as well.