Link Protected WorkBook ... and Get #N/A for Text Values!

G

Guest

Hello;

Sheet1, Book1.xls contains text and numerical values. Sheet1 is hidden.
Book2.xls is linked to Book1.xls (Sheet1).
Open Book2, click Update, and ALL values are updated correctly.
Now, protect Book1. Open Book2, Update, and the numerical values are updated
correctly, but SOME of the text values are updated as #N/A.
Remove Book1 protection, and ALL text and numerical values are updated
correctly in Book2.

Does the above make sense to anyone?

I would very much appreciate your insight into the possible cause.

Thank you kindly.
 
G

Guest

Hello;
Here's another scenario:
Book1.xls: Sheet1 is hidden. w/b is protected.
Book2.xls: automatically linked to Book1. w/b is not protected.
NO cells or w/s are protected in either workbooks.
Open Book2.xls. click Update. Some text values are displayed as #N/A.
Now, open Book1.xls while Book2.xls is open. The #N/A are replaced by the
correct updated values !!!!!!

Does the above help in identifying the problem ??
Thank you.


monir said:
No cells are protected.
Click to expand...
 
G

Guest

Open Book2.xls. click Update. Some text values are displayed as #N/A.

When I replace those text values in Sheet1 Book1.xls by numerical values and
again protect the w/b, the values are updated correctly in Book2.xls !!!

There's NO code, conditional formatting, events, etc. on Sheet1.

Could the problem be associated with memory ?

Thank you.

monir said:
Hello;
Here's another scenario:
Book1.xls: Sheet1 is hidden. w/b is protected.
Book2.xls: automatically linked to Book1. w/b is not protected.
NO cells or w/s are protected in either workbooks.
Open Book2.xls. click Update. Some text values are displayed as #N/A.
Now, open Book1.xls while Book2.xls is open. The #N/A are replaced by the
correct updated values !!!!!!

Does the above help in identifying the problem ??
Thank you.
Click to expand...
 
G

Guest

Here's a workaround. It solves the #N/A error in Book2.xls when the w/b is
linked to a protected Book1.xls.
It does not however shed any light onto the cause of the problem!
1. In Sheet1 Bookk1.xls, replace those text values (which produced #N/A in
Book2.xls) by referenced values.
2. For example, if cell C9 Sheet1 Book1.xls is one of those text cells and
has the value Test, then store Test in a different cell C10, and in cell C9
make reference to C10.
3. Hide the w/s. Protect & Save & Close the w/b.
4. Open Book2.xls and click Update. Now, all the text and numerical values
are updated correctly from the protected w/b Book1.xls.

I was a bit surprised that there were no takers among you experts! I
probably did not explain the problem clear enough.


monir said:
Open Book2.xls. click Update. Some text values are displayed as #N/A.
Click to expand...

When I replace those text values in Sheet1 Book1.xls by numerical values and
again protect the w/b, the values are updated correctly in Book2.xls !!!

There's NO code, conditional formatting, events, etc. on Sheet1.

Could the problem be associated with memory ?

Thank you.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update data without opening file? 1
3 workbooks linked 1
Macro - Inserting text to a cell already containg text 4
link a local spreadsheet to a network shared spreadsheet 1
Update data wihout opening the file? 1
defign a function in one workbook and use it in another workbook 3
How to determine the worksheet that a calculate event gets initiated on when the workbook is not act 3
Macro - Inserting text to a cell already containg text 5

Top