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))