Interesting =TExt issue

[Guest]

I am having trouble with Excel not letting me change text that is in a
function. Try this to reproduce the issue.

1. Open a blank workbook
2. Type this into a cell =TEST and hit enter
3. You should now see #NAME? in that cell
4. Go back into the cell and change the formula to =test and hit enter
5. Go back into the cell and you will see that the formula has changed back
to =TEST

Essentially any text string entered will remain with its case in memory so
if you make a mistake, good luck changing it. I've confirmed this behavior
with a colleague who knows Ecxel pretty well and he has never noticed this
before.

Is it a bug or a feature? If it is a feature, how do I turn it off?

 

[Bernie Deitrick]

It's not a feature - it's just that way Excel remembers names.

To reset TEST, simply use Insert / Name / Define test (with your prefered cap/lower case mix) and
refer to any cell. Then do Insert/Name/Define again, and delete test from the name list... and the
next time, it will use your prefered case...

HTH,
Bernie
MS Excel MVP

 

[Guest]

How does this cause a problem? You have made a reference to a named range
that does not exits. Try this. Add =TEST in cell A1. Now in Cell A2 add the
number 1. Now add a named range called Test which references cell A2. Cell A1
will now also display the value 1 and the case of your formula will have
changed to match the case of the the named range...

I guess I just don't see the problem... It is kinda odd but certainly not a
problem.

 

[Guest]

This causes a problem because I'm not trying to use a named reference, but
requesting data from a dde link that has a symbol that is case sensitive.
This is the dde formula:

='@recombiner!TA_SRV'|LIVEQUOTE!'LIVEQUOTE;1022;1003={''+TOLKF''}'

I made a mistake and typed +TOLKf and then could not change it. This is a
case of an application trying to be smart and simply being annoying.

 

[Bernie Deitrick]

Pat,

I cannot replicate your problem: within the double quotes, Excel should ignore whatever you type....

HTH,
Bernie
MS Excel MVP