Cells display function's text rather than its results
 

noob here...

Just created a simple CONCATENATE function combining two text strings
on two different worksheets (within same .xls document). The result is
a URL. No empty gaps, all formatted as General.

Everything worked just fine the first several times I opened, saved and
reopened the document.

Then it started getting weird: dates are suddenly displayed as 5-digit
numbers (tried formatting cells as dates, won't change), and the
formula is displayed rather than the result. I've tried reformatting,
re-launching, etc... and I just can't get it back to the way it was.
It's like a huge, irreversible reformatting took place of its own
accord. Any tips?

Many thanks in advance.

TRL

(office 2000, win XP)


--
tonerowlabs
------------------------------------------------------------------------
tonerowlabs's Profile: http://www.excelforum.com/member.php...o&userid=36138
View this thread: http://www.excelforum.com/showthread...hreadid=559127

Cells display function's text rather than its results
 

function =
=CONCATENATE(Sheet2!A1,A2)

text displayed used to be:
[the correct text string, combining text from 2 fields)

it is now:
"=CONCATENATE(Sheet2!A1,A2)"

date =
07/06/2006

date displayed =
38904

thanks....


--
tonerowlabs
------------------------------------------------------------------------
tonerowlabs's Profile: http://www.excelforum.com/member.php...o&userid=36138
View this thread: http://www.excelforum.com/showthread...hreadid=559127

Cells display function's text rather than its results
 

Make sure that the cell is formatted as General and then reenter the formula.

And try something like:

=concatenate(sheet2!a1,text(a2,"mm/dd/yyyy"))
or simply
=sheet2!a1 & text(a2,"mm/dd/yyyy")



tonerowlabs wrote:

function =
=CONCATENATE(Sheet2!A1,A2)

text displayed used to be:
[the correct text string, combining text from 2 fields)

it is now:
"=CONCATENATE(Sheet2!A1,A2)"

date =
07/06/2006

date displayed =
38904

thanks....

--
tonerowlabs
------------------------------------------------------------------------
tonerowlabs's Profile: http://www.excelforum.com/member.php...o&userid=36138
View this thread: http://www.excelforum.com/showthread...hreadid=559127

--

Dave Peterson

Cells display function's text rather than its results
 

thanks, that definitely worked for the dates.

But there are two separate issues:

1. Dates inputed AS TEXT are appearing as 5-digit numbers.
2. Functions displayed rather than their results.

I mention them together, because I think they are related. They both
occurred at the same time (I have no idea what I did), and they both
involved text being displayed as formulae. All cells are formatted as
General.

Let me ask another way: is there a way to have the CONCATENATE results
displayed as the function formula? Lets say you want to look at an
entire worksheet and see only the functions/formulae in each cell,
rather than the results of them. Is this possible? If so, maybe I
accidentally did this, and I want to make it go back?

BTW, the results of the concatenate forumla will simply be a URL. The
function grabs the first 20 characters, and then adds the PRODUCT_CODE
from another cell, which takes you to a URL for that specific product
online. No special characters, all alphanumeric.

THANKS in advance!!!!


--
tonerowlabs
------------------------------------------------------------------------
tonerowlabs's Profile: http://www.excelforum.com/member.php...o&userid=36138
View this thread: http://www.excelforum.com/showthread...hreadid=559127

Cells display function's text rather than its results
 

I'm not sure what entering dates as text means, but...

Maybe you're looking at formulas.

Tools|options|view tab|uncheck formulas.

There's a shortcut key that toggles this setting that may have been hit in
error.

Ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard)

And this can be a quick way to look at the formulas, too.

If you want that URL to be a hyperlink, you may want to do something like:

=hyperlink(yourformulahere,"Click Me!")



tonerowlabs wrote:

thanks, that definitely worked for the dates.

But there are two separate issues:

1. Dates inputed AS TEXT are appearing as 5-digit numbers.
2. Functions displayed rather than their results.

I mention them together, because I think they are related. They both
occurred at the same time (I have no idea what I did), and they both
involved text being displayed as formulae. All cells are formatted as
General.

Let me ask another way: is there a way to have the CONCATENATE results
displayed as the function formula? Lets say you want to look at an
entire worksheet and see only the functions/formulae in each cell,
rather than the results of them. Is this possible? If so, maybe I
accidentally did this, and I want to make it go back?

BTW, the results of the concatenate forumla will simply be a URL. The
function grabs the first 20 characters, and then adds the PRODUCT_CODE
from another cell, which takes you to a URL for that specific product
online. No special characters, all alphanumeric.

THANKS in advance!!!!

--
tonerowlabs
------------------------------------------------------------------------
tonerowlabs's Profile: http://www.excelforum.com/member.php...o&userid=36138
View this thread: http://www.excelforum.com/showthread...hreadid=559127

--

Dave Peterson