Formula switches to Text
 

I have a workbook with 60 or so sheets. The first sheet is my "base sheet"
and data from the other 59 are summarized on the base sheet (ie all the names
, addresses, phone# etc)

I am manually selecting data for the first "base data" located at
=FirstDataSheet!$F$16 to base sheet cell A1 which yields "John Doe"
=FirstDataSheet!$F$17 to base sheet cell A2 which yields "123
Main" etc

I completed the first row and wanted to copy Row A to Rows B, C, D etc

When I drag and drop or copy and paste - the formula itself (text) is
placed in the copied to cell. ie instead of "Joe Blow" from the second
datasheet I get =FirstDataSheet!$F$16.

I've done this successfully in the past and don't understand why it fails me
now???
The cell that becomes "corrupted" remains like that and I cannot edit it to
fix it. I just end up with text of what I edited in. The only way to get it
to work correctly is to select the cell, type "=" and then select the cell
data I want from the proper data sheet which would take forever.

HELP !



--
Ray Tweedale
All-around-nice-guy

Formula switches to Text
 

Format your cells as General rather than Text before copying down.

In article ,
Tweedy wrote:

I have a workbook with 60 or so sheets. The first sheet is my "base sheet"
and data from the other 59 are summarized on the base sheet (ie all the names
, addresses, phone# etc)

I am manually selecting data for the first "base data" located at
=FirstDataSheet!$F$16 to base sheet cell A1 which yields "John Doe"
=FirstDataSheet!$F$17 to base sheet cell A2 which yields "123
Main" etc

I completed the first row and wanted to copy Row A to Rows B, C, D etc

When I drag and drop or copy and paste - the formula itself (text) is
placed in the copied to cell. ie instead of "Joe Blow" from the second
datasheet I get =FirstDataSheet!$F$16.

I've done this successfully in the past and don't understand why it fails me
now???
The cell that becomes "corrupted" remains like that and I cannot edit it to
fix it. I just end up with text of what I edited in. The only way to get it
to work correctly is to select the cell, type "=" and then select the cell
data I want from the proper data sheet which would take forever.

HELP !

Formula switches to Text
 

JE,

I neglected to mention I've done that and to no avail. I select the range
and then format as general. Am I possibly ding that wrong? Possible I have
a virus of some sort?
--
Ray Tweedale
All-around-nice-guy


"JE McGimpsey" wrote:

Format your cells as General rather than Text before copying down.

In article ,
Tweedy wrote:

I have a workbook with 60 or so sheets. The first sheet is my "base sheet"
and data from the other 59 are summarized on the base sheet (ie all the names
, addresses, phone# etc)

I am manually selecting data for the first "base data" located at
=FirstDataSheet!$F$16 to base sheet cell A1 which yields "John Doe"
=FirstDataSheet!$F$17 to base sheet cell A2 which yields "123
Main" etc

I completed the first row and wanted to copy Row A to Rows B, C, D etc

When I drag and drop or copy and paste - the formula itself (text) is
placed in the copied to cell. ie instead of "Joe Blow" from the second
datasheet I get =FirstDataSheet!$F$16.

I've done this successfully in the past and don't understand why it fails me
now???
The cell that becomes "corrupted" remains like that and I cannot edit it to
fix it. I just end up with text of what I edited in. The only way to get it
to work correctly is to select the cell, type "=" and then select the cell
data I want from the proper data sheet which would take forever.

HELP !

Formula switches to Text
 

Hi Ray,
Make sure that automatic calculation is turned on
Tools, Options, Calculation (tab), calculation: [x] Automatic

You may have had a macro that terminated and did not turn
calculation back on. Provision should be made in a macro
to exit turning calculation back on or restoring to what it was
even upon abnormal termination. Also applies to addons.

Another possibility is that you have extra characters such
as space or nonbreaking space before or after the forumula.
Check that with =ISTEXT(a1)
Also see descriptions at the following and also the topic above
the following reference.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tweedy" wrote in message ...
JE,

I neglected to mention I've done that and to no avail. I select the range
and then format as general. Am I possibly ding that wrong? Possible I have
a virus of some sort?
--
Ray Tweedale
All-around-nice-guy


"JE McGimpsey" wrote:

Format your cells as General rather than Text before copying down.

In article ,
Tweedy wrote:

I have a workbook with 60 or so sheets. The first sheet is my "base sheet"
and data from the other 59 are summarized on the base sheet (ie all the names
, addresses, phone# etc)

I am manually selecting data for the first "base data" located at
=FirstDataSheet!$F$16 to base sheet cell A1 which yields "John Doe"
=FirstDataSheet!$F$17 to base sheet cell A2 which yields "123
Main" etc

I completed the first row and wanted to copy Row A to Rows B, C, D etc

When I drag and drop or copy and paste - the formula itself (text) is
placed in the copied to cell. ie instead of "Joe Blow" from the second
datasheet I get =FirstDataSheet!$F$16.

I've done this successfully in the past and don't understand why it fails me
now???
The cell that becomes "corrupted" remains like that and I cannot edit it to
fix it. I just end up with text of what I edited in. The only way to get it
to work correctly is to select the cell, type "=" and then select the cell
data I want from the proper data sheet which would take forever.

HELP !

Formula switches to Text
 

When you use absolute addresses (the dollar signs in $f$16), then excel _knows_
that you don't want these formulas adjusted when you copy them.

Try using:
=FirstDataSheet!F16

And then copy down.

And if the formulas stay text, here are a couple of things to try:
1. Make sure you're not viewing formulas
(tools|Options|view tab|uncheck formulas)

2. Make sure you don't have any leading spaces before your = sign

3. Ungroup any sheets (if you see [Group] in the titlebar. (You could be
picking up the text format from one of the other grouped sheets.

4. Make sure that the cells are formatted as General before you copy down. (As
suggested by J.E.)

If all this fails...
If the formulas adjust F16, F17, F18, F19, ...., you can fix it later.

Select the range
Format it as general
Edit|replace
what: = (equal sign)
with: = (equal sign)
replace all





Tweedy wrote:

I have a workbook with 60 or so sheets. The first sheet is my "base sheet"
and data from the other 59 are summarized on the base sheet (ie all the names
, addresses, phone# etc)

I am manually selecting data for the first "base data" located at
=FirstDataSheet!$F$16 to base sheet cell A1 which yields "John Doe"
=FirstDataSheet!$F$17 to base sheet cell A2 which yields "123
Main" etc

I completed the first row and wanted to copy Row A to Rows B, C, D etc

When I drag and drop or copy and paste - the formula itself (text) is
placed in the copied to cell. ie instead of "Joe Blow" from the second
datasheet I get =FirstDataSheet!$F$16.

I've done this successfully in the past and don't understand why it fails me
now???
The cell that becomes "corrupted" remains like that and I cannot edit it to
fix it. I just end up with text of what I edited in. The only way to get it
to work correctly is to select the cell, type "=" and then select the cell
data I want from the proper data sheet which would take forever.

HELP !


--
Ray Tweedale
All-around-nice-guy

--

Dave Peterson

Formula switches to Text
 

--
Ray Tweedale
All-around-nice-guy

Dave,

Thanks for the comments. I use "CTRL ~" to switch between formula and data.
That was the first thing I thought of. Further examining the problem I see
that when I do copy down that the formatting goes from general to text. To
fix it I need to change the formatting back and then physically do a
"reenter" as a previous post mentioned. For now I'll do what I wanted to do
by "copying down", changing the sheet # and then when they are all done i'll
change the format back to general and use the reenter macro. It doesn't
answer my root cause question of why the formatting is changing but it will
get me done with this.

Thanks for your help.


"Dave Peterson" wrote:

When you use absolute addresses (the dollar signs in $f$16), then excel _knows_
that you don't want these formulas adjusted when you copy them.

Try using:
=FirstDataSheet!F16

And then copy down.

And if the formulas stay text, here are a couple of things to try:
1. Make sure you're not viewing formulas
(tools|Options|view tab|uncheck formulas)

2. Make sure you don't have any leading spaces before your = sign

3. Ungroup any sheets (if you see [Group] in the titlebar. (You could be
picking up the text format from one of the other grouped sheets.

4. Make sure that the cells are formatted as General before you copy down. (As
suggested by J.E.)

If all this fails...
If the formulas adjust F16, F17, F18, F19, ...., you can fix it later.

Select the range
Format it as general
Edit|replace
what: = (equal sign)
with: = (equal sign)
replace all





Tweedy wrote:

I have a workbook with 60 or so sheets. The first sheet is my "base sheet"
and data from the other 59 are summarized on the base sheet (ie all the names
, addresses, phone# etc)

I am manually selecting data for the first "base data" located at
=FirstDataSheet!$F$16 to base sheet cell A1 which yields "John Doe"
=FirstDataSheet!$F$17 to base sheet cell A2 which yields "123
Main" etc

I completed the first row and wanted to copy Row A to Rows B, C, D etc

When I drag and drop or copy and paste - the formula itself (text) is
placed in the copied to cell. ie instead of "Joe Blow" from the second
datasheet I get =FirstDataSheet!$F$16.

I've done this successfully in the past and don't understand why it fails me
now???
The cell that becomes "corrupted" remains like that and I cannot edit it to
fix it. I just end up with text of what I edited in. The only way to get it
to work correctly is to select the cell, type "=" and then select the cell
data I want from the proper data sheet which would take forever.

HELP !


--
Ray Tweedale
All-around-nice-guy

--

Dave Peterson