Referencing a closed workbook

[in_need_of_excel_help]

Hi all,

I have two spreadsheets. One which contains the data and the second one is a report which I've created.

In the spreadsheet where I've created a report, i have the following formula in one of the columns:

=IF(COUNTIFS('C:\Documents\[Workings.xlsx]Payments'!$A$2:$A$103,A2,'C:\Documents\[Workings.xlsx]Payments'!$B$2:$B$103,B2)=2,"Yes"," ")

So the report looks at the data, and if the values in A2 and B2 appear more than twice in the other spreadsheet from the range specified, the value returned is 'yes".

This formula works fine when the workbook is opened, but unfortunately not when it's closed.

Is there an alternative formula which will work regardless if the workbook is closed or not?

Thanks!


P.s. i would install the add-on, but I don't think I can do it on our server

 

[RoryA]

Try:

=IF(SUMPRODUCT(('C:\Documents\[Workings.xlsx]Payments'!$A$2:$A$103=A2)*('C:\Documents\[Workings.xlsx]Payments'!$B$2:$B$103=B2))=2,"Yes"," ")

 

[in_need_of_excel_help]

Worked like a charm! Thank you.