Linking to protected workbooks -- #N/A {Please help}
Posted by Jennifer on February 06, 2001 12:38 PM
I have an Excel file that summarizes the information in many other smaller Excel files. It gets the information through links and lately I am randomly getting #N/A errors. The only thing I have changed is that I protected the structure of the source workbooks. The #N/A errors only occur where a person has entered a character value in a specific cell (D41) in the source file, but the other linked values come through normal. Has anyone heard of problems regarding linking to protected workbooks (Tools->protection->protect workbook)? Any help would be greatly appreciated. Thanks!
Posted by Dave Hawley on February 06, 2001 12:46 PM
Hi Jennifer
You cannot have a link to a Password protected workbook. If you could there would not be much use in protecting it as anybody could type a reference formula to it and retrieve the data from within.
However, when Excel recalculates any one of the formulas refrencing the Protected workbook you will be prompted for the Password.
Sorry to be the bringer of bad news :-(
Dave
OzGrid Business Applications
Posted by Jennifer on February 27, 2001 12:13 PM
Dave,
The structure of the workbook was protected not the value of the contents. This is actually a quirk in Excel in that when you link to user entered "text" values, many times you will get the #N/A! error. To get around this, I created a hidden row equal to the user entered text row and then linked to this hidden row. That way the contents was a formula and the link works. Through this project, I have ran into numerous Excel quirks and am really losing my faith in the product. Thanks.
