Formula display errors

[Bill Ridgeway]

I recently posted a query regarding a spreadsheet which displayed "#N/A" in
error from a reference to a worksheet in another spreadsheet - but only when
the source spreadsheet wasn't open. Suffice to say the formula is a
straight =(a cell in another worksheet in another spreadsheet) which works
OK in other cells.

The formats in the source worksheet were copied down but the error persisted
in only the one cell. Formats in both the source and target cells were
copied down again resulting in the error persisting in only the one cell. I
found that the problem resolved by deleting just one character in the source
cell.

Several people responded to that thread which, as it unfortunately didn't
lead anywhere, I compromised by just omitting the 'rogue' character.
Unfortunately the problem has re-emerged. Now, not only does the original
cell return "#N/A" but also, intermittently do two others and a further cell
returns part information and a string of "squares....squares".

There seems no rhyme or reason for this error and I would appreciate any
insight as to a possible cause.

Regards.

Bill Ridgeway

 

[Roger Govier]

Hi Bill

As Dave and I said originally, to see a copy of the tow files would be
best.
I appreciate confidentiality issues, but perhaps you could just strip
out any names, and all the columns that are not involved in the problem.
Otherwise, I'm not sure there is much to add to what was posted before.

 

[Bill Ridgeway]

Thanks Roger. There isn't a problem with stripping out 'unwanted' data.
However, there is a problem with camouflaging data in relevant cells. Even
if the last (alpha) character in a reference (which something like AAAnnnA)
is omitted the problem goes away. So far the problem occurs when the last
character is "Y" and now also "V" but with no other letters: I have tried
them all. This may not be much to go on but I hope someone can help. This
is driving me potty!!

Regards.

Bill Ridgeway