Linking formulas to password protected workbooks

Hi all,

I wondered if anyone could solve a problem I have?

I've got three separate workbooks, lets call them A, B and C. A and B are for data entry and are password protected so that only trusted members of staff can make entries. C is an overview of both A and B and has various links to them.

When I open C I get a pop up box about updating links, I click yes, then it asks for the passwords for A and B which I enter, but any formula linking to A and B is then displayed as #VALUE!

If I open A and B first, then open C, everything works fine and I do not get #VALUE!

Is there anything I can do so I don't have to open A and B first?

Many thanks

Originally Posted by Special-K

What is an example of the #VALUE formulas?
It could well be that the formulas fail because they need the files open first.
Some formulas won't work with closed workbooks.
What I'm saying is this may not be password related at all.

I see - here is one of the formulas:

=SUMIFS('S:\New Business Register\[New Business Register.xlsx]2019'!N$2:N$1001,'S:\New Business Register\[New Business Register.xlsx]2019'!P$2:P$1001,"",'S:\New Business Register\[New Business Register.xlsx]2019'!A$2:A$1001,"*DR*")-(SUMIFS('S:\New Business Register\[New Business Register.xlsx]2019'!Q$2:Q$1001,'S:\New Business Register\[New Business Register.xlsx]2019'!A$2:A$1001,"*DR*"))