Dynamic Sumproduct using external workbook without it being open

Hello

I have a sumproduct formula that dynamically checks for headers of data to compare if they have certain criteria.

Now this formula works but now I'm trying to make it look for data on a external workbook without it being open, so my customers can just replace the raw data with new data, so I have read a few website on how to make it read external workbook but it displaying #Value and when I close the raw data workbook it keeps showing unable to read file.

Any help on this would be great.

Sorry the "external" workbook will be on the same machine but will be in a different folder to the report workbook.
Are you asking me that sumproduct can work while internal workbook is closed? Or are you telling me that sumproduct can work while internal workbook is closed?

So I have a report workbook (reportemp.xlsx) which has the sumproduct formula which I would like to gather the data from incidentemp.xlsx, these workbooks will be stored in different location but on the same PC hard drive.

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.