As you said with formula the file should be open.
Here is a small formula example:
=SUMPRODUCT('C:\Documents and Settings\Owner\Own Documents\WorkFiles\Customer\Evaluations\["&$A$1&G$3&".xls]NewCustom_Active'!AC1)
But you could use VBA to read data from a closed workbook.
How To Import / Copy Data From Closed Workbook Into Current Workbook?
Standard disclaimer: none Microsoft site.
If I may also recommend, always add your Excel version, operating system, storage medium and other necessary information on your topic as information.
This way you will get a solution proposal much faster and more accurately.
Hope I could help you with these information / links.
I know I don't know anything (Socrates)