I am having issues trying to link to other password encrypted spreadsheets. My formula is below but unless I have the other two spreadsheets open I get "#VALUE!" in the cell instead of the actual value.



When I first open the "master" spreadsheet, I enter that password and then it asks if I want to update links (which I do), so I click update. Then it wants the password for both the other two spreadsheets (which I enter). It does not open the other two spreadsheets, but I do expect it to put in the value instead of showing #VALUE!. Below is my formula. I even tried to go into Excel Options and choosing everything that protects you from opening bad spreadsheets and I still don't get the updated value. The cell value still shows as percentage, so I don't think that is the problem. Any assistance would be greatly appreciated.

=(SUMIF([Testcase_No_2PW_Protect.xlsm]Sheet1!$A:$A,$B12,[Testcase_No_2PW_Protect.xlsm]Sheet1!$E:$E))+(SUMIF([Testcase_No_3PW_Protect.xlsm]Sheet1!$A:$A,$B12,[Testcase_No_3PW_Protect.xlsm]Sheet1!$E:$E))

This has nothing to do with the use of passwords, but with the fact that SUMIF references to external workbooks only work when those workbooks are open. You would get the same outcome without the passwords.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Use SUMPRODUCT instead of SUMIF.

And by the way - never use full column references in array- or array like formulas (such as SUMPRODCT). Some may find it easy and elegant, but it is really a bad habit.

Thanks, I didn't know I couldn't use sumif outside of the workbook. I'll go with your advice.

Please remember that SUMPRODUCT has different syntax from SUMIF.
I think the formula should be something like:

SUMPRODUCT(([Testcase_No_2PW_Protect.xlsm]Sheet1!$A:$A =$B12)*([Testcase_No_2PW_Protect.xlsm]Sheet1!$E:$E))+SUMPRODUCT(([Testcase_No_3PW_Protect.xlsm]Sheet1!$A:$A=$B12)*([Testcase_No_3PW_Protect.xlsm]Sheet1!$E:$E))

You should use only the required range in the formulas (for example A5:A1000, E5:E1000) instead of full column references (A:A, E:E).