Dynamic Sumproduct using external workbook without it being open

If you want to read while the file is closed ... you need to use VBA.

Hi thank you for responding.
Do you have any information on the VBA coding that is required to make this possible?

Sadly, I do not know VBA.

Excel Programming / VBA / Macros If you have questions on the board ... you can get help.

Originally Posted by chief_abound

Sadly, I do not know VBA.

Excel Programming / VBA / Macros If you have questions on the board ... you can get help.

Ok. I don't think this is entirely true, I have managed to read the excel spreadsheet using sumproduct but it seems that I'm having problems trying to find if the MONTH/Year in Created date is equal to the month/year provided by the user.

=SUMPRODUCT(--(MONTH(INDEX('C:\MonthlyReportTemp\Data\[incident.xls]Page 1'!$A$1:$M$227,,MATCH("Created",'C:\MonthlyReportTemp\Data\[incident.xls]Page 1'!$A$1:$M$1,0)))='Input Date'!$D$9))

So I'm able to use Index & Match but when I use MONTH or YEAR to retrieve the month/year of the created date it shows a unable to read file or #VALUE (when workbook is open)

Is there any other ways to take a full DATE with time and only extract the month and compare with the user value entered in sumproduct while working with a external workbook?

I have never worked with an external workbook ... I do not know, sorry for not helping.

Originally Posted by chief_abound

I have never worked with an external workbook ... I do not know, sorry for not helping.

It's ok. I found a VBA workaround by copying the contents of external workbook into a sheet on the report workbook.

Resolving this thread.