Dynamic Sumproduct using external workbook without it being open

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.