Cell Reference inside External Reference Inside "sumproduct"

[CoBIUS]

Ok here is the structure of what I am trying to achieve:

Top Level Spreadsheet (Report.xls)
This spreadsheet will look at an individual cell in all other spreadsheets in this folder say the cell is B5. If there is a date in B5 on the other spreadsheets it will indicate this even though other spreadsheets might be closed. This top level spreadsheet would Ideally look like this:
=SUMPRODUCT('H:\ECR Test\[080101.xls]Form'!$B$5)
=SUMPRODUCT('H:\ECR Test\[080102.xls]Form'!$B$5)
=SUMPRODUCT('H:\ECR Test\[080103.xls]Form'!$B$5)

Example of one of many spreadsheets read by Top Level ('[080101.xls]Form!'$B$5)
The only item that will change in the address form spreadsheet to spreadsheet is the name of the spreadsheet "080101". It will always be sheet "Form!" and cell B5.

Question
I would like to not have to type "080101" and all the others could there be hundreds.....can I refer to spreadsheet name inside the Top Level spreadsheet some how?

Thanks so much for any assistance!
CoBIUS

 

[RoryA]

Why are you using SUMPRODUCT if you are just returning the value of a cell? You would need INDIRECT to build up the reference using variables, but that won't work if the other workbook is closed. If you have Laurent Longre's Morefunc.xll add-in then there is an INDIRECT.EXT function that works with closed workbooks.