Why oh Y

[Bill Ridgeway]

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information from another
(external) worksheet. The information is a reference in the format
"AAAnumbersY". If the source file is open when the problem file is open it
will return the full reference. However if the source file is not open it
returns #NA. This happens ONLY when the last character of the reference is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Dave Peterson]

There are some worksheet functions that won't work with closed workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't close to the
problem.

 

[Bill Ridgeway]

Thanks Dave. The formula is a simple =[file and cell reference] of which
there are 23 other examples on the same worksheet that are OK. The problem
exists wherever I put this particular reference that ends in "Y". I have
already checked that all the cells (both source and target) are formatted
the same.

Regards.

Bill Ridgeway

 

[Dave Peterson]

You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the string coming
back is over 255 characters. Excel will chop the results to 255 if the sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get recalculated to
be #n/a during the final close/save?????

Thanks Dave. The formula is a simple =[file and cell reference] of which
there are 23 other examples on the same worksheet that are OK. The problem
exists wherever I put this particular reference that ends in "Y". I have
already checked that all the cells (both source and target) are formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't close to
the
problem.

 

[Bill Ridgeway]

Thanks Dave.

The cell in question is one of 24 in a column. All the other 23 cells
display correct information. The error occurs anywhere in the 24 cells in
which I put the "=[file and cell reference] and anywhere I put the
particular reference that ends in "Y" (without quotes) in the source file.
If I omit the "Y" or substitute another letter (I've tried them all) it
displays OK.

The error doesn't occur if I have the source file open at the time of
opening the file with the formula. It occurs only if I open the file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a correct
reference and b) it wouldn't answer the question why the heck is it doing
this.

Regards.

Bill Ridgeway

You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the string
coming
back is over 255 characters. Excel will chop the results to 255 if the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????

Thanks Dave. The formula is a simple =[file and cell reference] of which
there are 23 other examples on the same worksheet that are OK. The
problem
exists wherever I put this particular reference that ends in "Y". I have
already checked that all the cells (both source and target) are formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't close
to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information from
another
(external) worksheet. The information is a reference in the format
"AAAnumbersY". If the source file is open when the problem file is
open
it
will return the full reference. However if the source file is not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Dave Peterson]

I've never seen a problem like this. I don't have any other guesses.

But you are sure you're pointing to the correct workbook, worksheet, address?

(Yeah, you've already checked this 18 times...)

Thanks Dave.

The cell in question is one of 24 in a column. All the other 23 cells
display correct information. The error occurs anywhere in the 24 cells in
which I put the "=[file and cell reference] and anywhere I put the
particular reference that ends in "Y" (without quotes) in the source file.
If I omit the "Y" or substitute another letter (I've tried them all) it
displays OK.

The error doesn't occur if I have the source file open at the time of
opening the file with the formula. It occurs only if I open the file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a correct
reference and b) it wouldn't answer the question why the heck is it doing
this.

Regards.

Bill Ridgeway

You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the string
coming
back is over 255 characters. Excel will chop the results to 255 if the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????

Thanks Dave. The formula is a simple =[file and cell reference] of which
there are 23 other examples on the same worksheet that are OK. The
problem
exists wherever I put this particular reference that ends in "Y". I have
already checked that all the cells (both source and target) are formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't close
to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information from
another
(external) worksheet. The information is a reference in the format
"AAAnumbersY". If the source file is open when the problem file is
open
it
will return the full reference. However if the source file is not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Roger Govier]

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last character
in the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier

Thanks Dave.

The cell in question is one of 24 in a column. All the other 23 cells
display correct information. The error occurs anywhere in the 24
cells in which I put the "=[file and cell reference] and anywhere I
put the particular reference that ends in "Y" (without quotes) in the
source file. If I omit the "Y" or substitute another letter (I've
tried them all) it displays OK.

The error doesn't occur if I have the source file open at the time of
opening the file with the formula. It occurs only if I open the file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the heck
is it doing this.

Regards.

Bill Ridgeway

You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string coming
back is over 255 characters. Excel will chop the results to 255 if
the sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????

Thanks Dave. The formula is a simple =[file and cell reference] of
which
there are 23 other examples on the same worksheet that are OK. The
problem
exists wherever I put this particular reference that ends in "Y". I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem file
is open
it
will return the full reference. However if the source file is
not open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Bill Ridgeway]

The letter "y" appears as the last character in the cell of the source file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last character in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or closed.
Equally, with the word Summary in cell B3, it returns Summary to me wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier

Thanks Dave.

The cell in question is one of 24 in a column. All the other 23 cells
display correct information. The error occurs anywhere in the 24 cells
in which I put the "=[file and cell reference] and anywhere I put the
particular reference that ends in "Y" (without quotes) in the source
file. If I omit the "Y" or substitute another letter (I've tried them
all) it displays OK.

The error doesn't occur if I have the source file open at the time of
opening the file with the formula. It occurs only if I open the file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the heck is
it doing this.

Regards.

Bill Ridgeway

You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the string
coming
back is over 255 characters. Excel will chop the results to 255 if the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference] of
which
there are 23 other examples on the same worksheet that are OK. The
problem
exists wherever I put this particular reference that ends in "Y". I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information from
another
(external) worksheet. The information is a reference in the format
"AAAnumbersY". If the source file is open when the problem file is
open
it
will return the full reference. However if the source file is not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Roger Govier]

Hi Bill

Then I cannot re-create the problem.
If you want to mail the files to me direct, I will be quite happy to
take a look and see if I can see what's happening.
To mail direct, remove NOSPAM from my mail address.

--
Regards

Roger Govier

The letter "y" appears as the last character in the cell of the source
file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last
character in the contents of the cell in the (closed or open)
workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier

Thanks Dave.

The cell in question is one of 24 in a column. All the other 23
cells display correct information. The error occurs anywhere in the
24 cells in which I put the "=[file and cell reference] and anywhere
I put the particular reference that ends in "Y" (without quotes) in
the source file. If I omit the "Y" or substitute another letter
(I've tried them all) it displays OK.

The error doesn't occur if I have the source file open at the time
of opening the file with the formula. It occurs only if I open the
file containing the formula without having first opened the source
file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the
heck is it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string coming
back is over 255 characters. Excel will chop the results to 255 if
the sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference]
of which
there are 23 other examples on the same worksheet that are OK.
The problem
exists wherever I put this particular reference that ends in "Y".
I have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this
isn't close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem
file is open
it
will return the full reference. However if the source file is
not open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Dave Peterson]

And your sending workbook is a regular old normal .xls workbook?????

(If I use a .csv file, I got a different warning, though--not able to update
links.)

The letter "y" appears as the last character in the cell of the source file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last character in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or closed.
Equally, with the word Summary in cell B3, it returns Summary to me wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier

Thanks Dave.

The cell in question is one of 24 in a column. All the other 23 cells
display correct information. The error occurs anywhere in the 24 cells
in which I put the "=[file and cell reference] and anywhere I put the
particular reference that ends in "Y" (without quotes) in the source
file. If I omit the "Y" or substitute another letter (I've tried them
all) it displays OK.

The error doesn't occur if I have the source file open at the time of
opening the file with the formula. It occurs only if I open the file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the heck is
it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the string
coming
back is over 255 characters. Excel will chop the results to 255 if the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference] of
which
there are 23 other examples on the same worksheet that are OK. The
problem
exists wherever I put this particular reference that ends in "Y". I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information from
another
(external) worksheet. The information is a reference in the format
"AAAnumbersY". If the source file is open when the problem file is
open
it
will return the full reference. However if the source file is not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Bill Ridgeway]

Thanks Dave,

Both source and target files are normal Excel (.xls) files.

The problem (in summary) is that -
Source file is open- No problem

Source file not open-
Source cell information is AAA-(numbers)Y - target cell returns #NA
Source cell information is AAA-(numbers) - target cell returns AAA-(numbers)
This occurs in whatever cell in the source file the string AAA-(numbers)Y is
placed

The same string but with any other letter does not produce an error.

Regards.

Bill Ridgeway

And your sending workbook is a regular old normal .xls workbook?????

(If I use a .csv file, I got a different warning, though--not able to
update
links.)

The letter "y" appears as the last character in the cell of the source
file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last character
in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier


Thanks Dave.

The cell in question is one of 24 in a column. All the other 23 cells
display correct information. The error occurs anywhere in the 24
cells
in which I put the "=[file and cell reference] and anywhere I put the
particular reference that ends in "Y" (without quotes) in the source
file. If I omit the "Y" or substitute another letter (I've tried them
all) it displays OK.

The error doesn't occur if I have the source file open at the time of
opening the file with the formula. It occurs only if I open the file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the heck
is
it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string
coming
back is over 255 characters. Excel will chop the results to 255 if
the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference] of
which
there are 23 other examples on the same worksheet that are OK. The
problem
exists wherever I put this particular reference that ends in "Y". I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem file
is
open
it
will return the full reference. However if the source file is
not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Dave Peterson]

And if you build that same formula in a brand new workbook, does it work ok?

How about if you put it in a different cell in the same worksheet or a different
worksheet??

How about if you point at a different cell, a different worksheet or a different
workbook?

(Grasping at straws to find some sort of pattern--I can't believe that the Y
causes the problem--I don't want to believe that!)

Thanks Dave,

Both source and target files are normal Excel (.xls) files.

The problem (in summary) is that -
Source file is open- No problem

Source file not open-
Source cell information is AAA-(numbers)Y - target cell returns #NA
Source cell information is AAA-(numbers) - target cell returns AAA-(numbers)
This occurs in whatever cell in the source file the string AAA-(numbers)Y is
placed

The same string but with any other letter does not produce an error.

Regards.

Bill Ridgeway

And your sending workbook is a regular old normal .xls workbook?????

(If I use a .csv file, I got a different warning, though--not able to
update
links.)

The letter "y" appears as the last character in the cell of the source
file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last character
in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier


Thanks Dave.

The cell in question is one of 24 in a column. All the other 23 cells
display correct information. The error occurs anywhere in the 24
cells
in which I put the "=[file and cell reference] and anywhere I put the
particular reference that ends in "Y" (without quotes) in the source
file. If I omit the "Y" or substitute another letter (I've tried them
all) it displays OK.

The error doesn't occur if I have the source file open at the time of
opening the file with the formula. It occurs only if I open the file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the heck
is
it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string
coming
back is over 255 characters. Excel will chop the results to 255 if
the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference] of
which
there are 23 other examples on the same worksheet that are OK. The
problem
exists wherever I put this particular reference that ends in "Y". I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem file
is
open
it
will return the full reference. However if the source file is
not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Bill Ridgeway]

Thanks Dave.
<<And if you build that same formula in a brand new workbook, does it work
ok?>> No

<<How about if you put it in a different cell in the same worksheet or a
different worksheet??>>Neither in a different worksheet within the
spreadsheet or another spreadsheet.

<<How about if you point at a different cell, a different worksheet or a
different workbook?>>Pointing to a different cell, different worksheet and
different worksheet (containing data copied from the original cell, not
typed in) is OK.

<<(Grasping at straws to find some sort of pattern--I can't believe that the
"Y" [(without quotes)] causes the problem--I don't want to believe that!)>>
If I substitute ANY other letter it works OK. I have just tried
substituting "Y" with "_Y" and it also works OK. It doesn't seem possible
or, indeed, logical but that is how it is.

The plot just seems to be thickening! Have I stumbled on an undocumented
and secret facet of Excel??

Regards.

Bill Ridgeway

And if you build that same formula in a brand new workbook, does it work
ok?

How about if you put it in a different cell in the same worksheet or a
different
worksheet??

How about if you point at a different cell, a different worksheet or a
different
workbook?

(Grasping at straws to find some sort of pattern--I can't believe that the
Y
causes the problem--I don't want to believe that!)

Thanks Dave,

Both source and target files are normal Excel (.xls) files.

The problem (in summary) is that -
Source file is open- No problem

Source file not open-
Source cell information is AAA-(numbers)Y - target cell returns #NA
Source cell information is AAA-(numbers) - target cell returns
AAA-(numbers)
This occurs in whatever cell in the source file the string AAA-(numbers)Y
is
placed

The same string but with any other letter does not produce an error.

Regards.

Bill Ridgeway

And your sending workbook is a regular old normal .xls workbook?????

(If I use a .csv file, I got a different warning, though--not able to
update
links.)



Bill Ridgeway wrote:

The letter "y" appears as the last character in the cell of the source
file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last
character
in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier


Thanks Dave.

The cell in question is one of 24 in a column. All the other 23
cells
display correct information. The error occurs anywhere in the 24
cells
in which I put the "=[file and cell reference] and anywhere I put
the
particular reference that ends in "Y" (without quotes) in the
source
file. If I omit the "Y" or substitute another letter (I've tried
them
all) it displays OK.

The error doesn't occur if I have the source file open at the time
of
opening the file with the formula. It occurs only if I open the
file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the
heck
is
it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string
coming
back is over 255 characters. Excel will chop the results to 255
if
the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference]
of
which
there are 23 other examples on the same worksheet that are OK.
The
problem
exists wherever I put this particular reference that ends in "Y".
I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this
isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads
information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem
file
is
open
it
will return the full reference. However if the source file is
not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Dave Peterson]

It doesn't sound like it's the extra Y that's causing the trouble if this worked
ok.

<<How about if you point at a different cell, a different worksheet or a
different workbook?>>

Pointing to a different cell, different worksheet and
different worksheet (containing data copied from the original cell, not
typed in) is OK.

Can you copy the two workbooks and then modify them so that the receiving file
contains two formulas--one that works and one that doesn't. And so the sending
workbook only has those two cells.

Maybe you could zip the pair and put them on www.savefile.com.

I'd like to see them and I bet others would, too.

Thanks Dave.
<<And if you build that same formula in a brand new workbook, does it work
ok?>> No

<<How about if you put it in a different cell in the same worksheet or a
different worksheet??>>Neither in a different worksheet within the
spreadsheet or another spreadsheet.

<<How about if you point at a different cell, a different worksheet or a
different workbook?>>Pointing to a different cell, different worksheet and
different worksheet (containing data copied from the original cell, not
typed in) is OK.

<<(Grasping at straws to find some sort of pattern--I can't believe that the
"Y" [(without quotes)] causes the problem--I don't want to believe that!)>>
If I substitute ANY other letter it works OK. I have just tried
substituting "Y" with "_Y" and it also works OK. It doesn't seem possible
or, indeed, logical but that is how it is.

The plot just seems to be thickening! Have I stumbled on an undocumented
and secret facet of Excel??

Regards.

Bill Ridgeway

And if you build that same formula in a brand new workbook, does it work
ok?

How about if you put it in a different cell in the same worksheet or a
different
worksheet??

How about if you point at a different cell, a different worksheet or a
different
workbook?

(Grasping at straws to find some sort of pattern--I can't believe that the
Y
causes the problem--I don't want to believe that!)

Thanks Dave,

Both source and target files are normal Excel (.xls) files.

The problem (in summary) is that -
Source file is open- No problem

Source file not open-
Source cell information is AAA-(numbers)Y - target cell returns #NA
Source cell information is AAA-(numbers) - target cell returns
AAA-(numbers)
This occurs in whatever cell in the source file the string AAA-(numbers)Y
is
placed

The same string but with any other letter does not produce an error.

Regards.

Bill Ridgeway

And your sending workbook is a regular old normal .xls workbook?????

(If I use a .csv file, I got a different warning, though--not able to
update
links.)



Bill Ridgeway wrote:

The letter "y" appears as the last character in the cell of the source
file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last
character
in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier


Thanks Dave.

The cell in question is one of 24 in a column. All the other 23
cells
display correct information. The error occurs anywhere in the 24
cells
in which I put the "=[file and cell reference] and anywhere I put
the
particular reference that ends in "Y" (without quotes) in the
source
file. If I omit the "Y" or substitute another letter (I've tried
them
all) it displays OK.

The error doesn't occur if I have the source file open at the time
of
opening the file with the formula. It occurs only if I open the
file
containing the formula without having first opened the source file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the
heck
is
it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string
coming
back is over 255 characters. Excel will chop the results to 255
if
the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference]
of
which
there are 23 other examples on the same worksheet that are OK.
The
problem
exists wherever I put this particular reference that ends in "Y".
I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this
isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads
information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem
file
is
open
it
will return the full reference. However if the source file is
not
open
it
returns #NA. This happens ONLY when the last character of the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Bill Ridgeway]

Yet more attempts-

Saved both files in new name and changed references to pull data from now
new source file name:
Same problem

Extracted (copy and paste) source data into a new file and changed
references to pull data from now new source file name:
Problem resolved

Copied and pasted data from the new file to the original source file
Same problem

This proves that it can be done (we knew that all the while) whilst
simultaneously proving and disproving that the last alpha character is,
quite illogically, causing a problem.

Any more thoughts whilst I go quietly mad please?

Regards.

Bill Ridgeway

Thanks Dave.
<<And if you build that same formula in a brand new workbook, does it work
ok?>> No

<<How about if you put it in a different cell in the same worksheet or a
different worksheet??>>Neither in a different worksheet within the
spreadsheet or another spreadsheet.

<<How about if you point at a different cell, a different worksheet or a
different workbook?>>Pointing to a different cell, different worksheet and
different worksheet (containing data copied from the original cell, not
typed in) is OK.

<<(Grasping at straws to find some sort of pattern--I can't believe that
the "Y" [(without quotes)] causes the problem--I don't want to believe
that!)>> If I substitute ANY other letter it works OK. I have just tried
substituting "Y" with "_Y" and it also works OK. It doesn't seem possible
or, indeed, logical but that is how it is.

The plot just seems to be thickening! Have I stumbled on an undocumented
and secret facet of Excel??

Regards.

Bill Ridgeway

And if you build that same formula in a brand new workbook, does it work
ok?

How about if you put it in a different cell in the same worksheet or a
different
worksheet??

How about if you point at a different cell, a different worksheet or a
different
workbook?

(Grasping at straws to find some sort of pattern--I can't believe that
the Y
causes the problem--I don't want to believe that!)

Thanks Dave,

Both source and target files are normal Excel (.xls) files.

The problem (in summary) is that -
Source file is open- No problem

Source file not open-
Source cell information is AAA-(numbers)Y - target cell returns #NA
Source cell information is AAA-(numbers) - target cell returns
AAA-(numbers)
This occurs in whatever cell in the source file the string
AAA-(numbers)Y is
placed

The same string but with any other letter does not produce an error.

Regards.

Bill Ridgeway

And your sending workbook is a regular old normal .xls workbook?????

(If I use a .csv file, I got a different warning, though--not able to
update
links.)



Bill Ridgeway wrote:

The letter "y" appears as the last character in the cell of the
source
file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last
character
in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier


Thanks Dave.

The cell in question is one of 24 in a column. All the other 23
cells
display correct information. The error occurs anywhere in the 24
cells
in which I put the "=[file and cell reference] and anywhere I put
the
particular reference that ends in "Y" (without quotes) in the
source
file. If I omit the "Y" or substitute another letter (I've tried
them
all) it displays OK.

The error doesn't occur if I have the source file open at the time
of
opening the file with the formula. It occurs only if I open the
file
containing the formula without having first opened the source
file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the
heck
is
it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string
coming
back is over 255 characters. Excel will chop the results to 255
if
the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference]
of
which
there are 23 other examples on the same worksheet that are OK.
The
problem
exists wherever I put this particular reference that ends in
"Y". I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this
isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads
information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem
file
is
open
it
will return the full reference. However if the source file
is
not
open
it
returns #NA. This happens ONLY when the last character of
the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why
is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Dave Peterson]

Just to skinny down that pair of workbooks and share them via savefile.com.

Maybe someone will download them and figure out the problem.

Yet more attempts-

Saved both files in new name and changed references to pull data from now
new source file name:
Same problem

Extracted (copy and paste) source data into a new file and changed
references to pull data from now new source file name:
Problem resolved

Copied and pasted data from the new file to the original source file
Same problem

This proves that it can be done (we knew that all the while) whilst
simultaneously proving and disproving that the last alpha character is,
quite illogically, causing a problem.

Any more thoughts whilst I go quietly mad please?

Regards.

Bill Ridgeway

Thanks Dave.
<<And if you build that same formula in a brand new workbook, does it work
ok?>> No

<<How about if you put it in a different cell in the same worksheet or a
different worksheet??>>Neither in a different worksheet within the
spreadsheet or another spreadsheet.

<<How about if you point at a different cell, a different worksheet or a
different workbook?>>Pointing to a different cell, different worksheet and
different worksheet (containing data copied from the original cell, not
typed in) is OK.

<<(Grasping at straws to find some sort of pattern--I can't believe that
the "Y" [(without quotes)] causes the problem--I don't want to believe
that!)>> If I substitute ANY other letter it works OK. I have just tried
substituting "Y" with "_Y" and it also works OK. It doesn't seem possible
or, indeed, logical but that is how it is.

The plot just seems to be thickening! Have I stumbled on an undocumented
and secret facet of Excel??

Regards.

Bill Ridgeway

And if you build that same formula in a brand new workbook, does it work
ok?

How about if you put it in a different cell in the same worksheet or a
different
worksheet??

How about if you point at a different cell, a different worksheet or a
different
workbook?

(Grasping at straws to find some sort of pattern--I can't believe that
the Y
causes the problem--I don't want to believe that!)

Bill Ridgeway wrote:

Thanks Dave,

Both source and target files are normal Excel (.xls) files.

The problem (in summary) is that -
Source file is open- No problem

Source file not open-
Source cell information is AAA-(numbers)Y - target cell returns #NA
Source cell information is AAA-(numbers) - target cell returns
AAA-(numbers)
This occurs in whatever cell in the source file the string
AAA-(numbers)Y is
placed

The same string but with any other letter does not produce an error.

Regards.

Bill Ridgeway

And your sending workbook is a regular old normal .xls workbook?????

(If I use a .csv file, I got a different warning, though--not able to
update
links.)



Bill Ridgeway wrote:

The letter "y" appears as the last character in the cell of the
source
file.

Regards.

Bill Ridgeway

Hi Bill

I'm not quite understanding where the "y" is.

Is it the last character in the source file name, or the last
character
in
the contents of the cell in the (closed or open) workbook?
I have tried it with
='C:\Documents and Settings\Roger Govier\My
Documents\[summary.xls]Sheet1'!$B3
and 50 in cell B3 and my result is 50 either with the file open or
closed.
Equally, with the word Summary in cell B3, it returns Summary to me
wither
with the file open or closed.

Perhaps I am not understanding the problem you are describing.
--
Regards

Roger Govier


Thanks Dave.

The cell in question is one of 24 in a column. All the other 23
cells
display correct information. The error occurs anywhere in the 24
cells
in which I put the "=[file and cell reference] and anywhere I put
the
particular reference that ends in "Y" (without quotes) in the
source
file. If I omit the "Y" or substitute another letter (I've tried
them
all) it displays OK.

The error doesn't occur if I have the source file open at the time
of
opening the file with the formula. It occurs only if I open the
file
containing the formula without having first opened the source
file.

The simple answer would be to omit the "Y" but a) it wouldn't be a
correct reference and b) it wouldn't answer the question why the
heck
is
it doing this.

Regards.

Bill Ridgeway
You sure that the column is wide enough <bg>???

The only time I've seen anything like this truncated is when the
string
coming
back is over 255 characters. Excel will chop the results to 255
if
the
sending
workbook is closed. (And that doesn't return an #n/a error.)

And you're sure that the formula the cell points to doesn't get
recalculated to
be #n/a during the final close/save?????



Bill Ridgeway wrote:

Thanks Dave. The formula is a simple =[file and cell reference]
of
which
there are 23 other examples on the same worksheet that are OK.
The
problem
exists wherever I put this particular reference that ends in
"Y". I
have
already checked that all the cells (both source and target) are
formatted
the same.

Regards.

Bill Ridgeway

There are some worksheet functions that won't work with closed
workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

You may want to share the formula that you're using if this
isn't
close to
the
problem.

Bill Ridgeway wrote:

I've just come across a very weird oddity with Excel 2003.

I have a spread sheet which has formula which reads
information
from
another
(external) worksheet. The information is a reference in the
format
"AAAnumbersY". If the source file is open when the problem
file
is
open
it
will return the full reference. However if the source file
is
not
open
it
returns #NA. This happens ONLY when the last character of
the
reference
is
"Y". (Yes, I've tried them all!)

This seems to be very strange behaviour indeed. Why oh why
is
this
happening? Any ideas please?

Regards.

Bill Ridgeway

 

[Bill Ridgeway]

Thanks for all your help on this subject. I've got round this problem by
the simple expedient of deleting the letter "Y" which seemed to be the
cause. It would be nice to find out what the heck was happening but life is
just too short.

Bill Ridgeway

 

[Dave Peterson]

You don't want to share a skinnied down version of both files?

 

[Bill Ridgeway]

I have produced a slimmed down version into which I put dummy data.
However, it is in the nature of the problem that even changing one character
resolves the issue so a demonstration spreadsheet isn't possible. Thanks
for the offer.

Regards.

Bill Ridgeway
Computer Solutions