• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT with closed workbook

A

Anar

Member
Hello,


I have a problem with SUMPRODUCT formula. I have searched internet, including this website for help, but could not find anything.


I have a file (destination file) where I put Sumproduct formula from another files (source files).

Formulas work in sheet1 in destination file, but not in sheet2. Formulas in Sheet1 and Sheet2 are almost the same, just source files linked are different.

When source files are closed sumproduct formulas in Sheet1 retrieve data, however in Sheet2 Sumproduct formula returns #N/A error.

Sumproduct formula in Sheet2 returns what it should return only when source file is open.

Note that there are not any other function nested in Sumproduct formula in Sheet2.


So, what could be the reason for an error in Sheet2?

Can it be due to formula length or source file data volume limitations.
 
Here is the formula while source file is closed:


=(SUMPRODUCT(--('Link to source file[Source file name.xls]Source file sheet'!$I$3:$I$100=$A4),--('Link to source file[Source file name.xls]Source file sheet'!$C$3:$C$100=C$2),('Link to source file[Source file name.xls]Source file sheet'!$J$3:$J$100)))*1.18
 
I know that SP reads from closed workbook. I have just tested it again with my files.

It is strange that sometimes it works, sometimes it does not.


I think there is a limitation issue here, since Excel has limitations for data retrieved from closed files.
 
xld, as I said, sometimes it does, sometimes it does not.

I would say that in most of the cases it does not retrieve data and returns #N/A error.
 
You must log in or register to reply here.
Back
Top