Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jeffry61
 
Posts: n/a
Default new sheets by this pc cannot calculate formulas

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.
  #4   Report Post  
Jeffry61
 
Posts: n/a
Default

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.

  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.


--

Dave Peterson


  #6   Report Post  
Jeffry61
 
Posts: n/a
Default

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.


--

Dave Peterson

  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

It sounds like that user on the other pc is formatting the cell (all the
cells???) as Text.

It could be a choice by the user.
It could be that the user copied another worksheet that was set up that way.
It could be that they created a book.xlt template workbook that was set up for
text.

Difficult to know without asking that other user.

I think I'd look at the book.xlt (in XLStart) first. Verify that the cells are
all formatted as general.

Jeffry61 wrote:

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.


--

Dave Peterson


--

Dave Peterson
  #8   Report Post  
Jeffry61
 
Posts: n/a
Default

Dave,
This seems to happen because the initial format always automatically comes
up text. I searched for all the template .xlt files and replaced them with
known good ones that include general format, but that made no difference. If
you click on new spreadsheet or open Excel by its icon, the new spreadsheet
comes up that way only on this PC.
If you change the Syle Format from text to general it works, but the next
new spreadsheet defaults to text again.

"Dave Peterson" wrote:

It sounds like that user on the other pc is formatting the cell (all the
cells???) as Text.

It could be a choice by the user.
It could be that the user copied another worksheet that was set up that way.
It could be that they created a book.xlt template workbook that was set up for
text.

Difficult to know without asking that other user.

I think I'd look at the book.xlt (in XLStart) first. Verify that the cells are
all formatted as general.

Jeffry61 wrote:

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.

--

Dave Peterson


--

Dave Peterson

  #9   Report Post  
Jeffry61
 
Posts: n/a
Default

IT's FIXED! Turns out that if you search for Book.xlt and sheet.xlt, you
will find all of them EXCEPT the ones in C:\
documents and settings\%username%\Application Data\Microsoft\Templates. I
overwrote the ones there and the problem is fixed.

Thanks!

"Jeffry61" wrote:

Dave,
This seems to happen because the initial format always automatically comes
up text. I searched for all the template .xlt files and replaced them with
known good ones that include general format, but that made no difference. If
you click on new spreadsheet or open Excel by its icon, the new spreadsheet
comes up that way only on this PC.
If you change the Syle Format from text to general it works, but the next
new spreadsheet defaults to text again.

"Dave Peterson" wrote:

It sounds like that user on the other pc is formatting the cell (all the
cells???) as Text.

It could be a choice by the user.
It could be that the user copied another worksheet that was set up that way.
It could be that they created a book.xlt template workbook that was set up for
text.

Difficult to know without asking that other user.

I think I'd look at the book.xlt (in XLStart) first. Verify that the cells are
all formatted as general.

Jeffry61 wrote:

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.

--

Dave Peterson


--

Dave Peterson

  #10   Report Post  
Jeffry61
 
Posts: n/a
Default

Sorry, the location I mention below is one that made no difference. The
location that worked after changing the templates is c:\Program
Files\Microsoft Office10\XLstart. There were 5 sets of templates in 5
different locations, but this one is the one that matters.

"Jeffry61" wrote:

IT's FIXED! Turns out that if you search for Book.xlt and sheet.xlt, you
will find all of them EXCEPT the ones in C:\
documents and settings\%username%\Application Data\Microsoft\Templates. I
overwrote the ones there and the problem is fixed.

Thanks!

"Jeffry61" wrote:

Dave,
This seems to happen because the initial format always automatically comes
up text. I searched for all the template .xlt files and replaced them with
known good ones that include general format, but that made no difference. If
you click on new spreadsheet or open Excel by its icon, the new spreadsheet
comes up that way only on this PC.
If you change the Syle Format from text to general it works, but the next
new spreadsheet defaults to text again.

"Dave Peterson" wrote:

It sounds like that user on the other pc is formatting the cell (all the
cells???) as Text.

It could be a choice by the user.
It could be that the user copied another worksheet that was set up that way.
It could be that they created a book.xlt template workbook that was set up for
text.

Difficult to know without asking that other user.

I think I'd look at the book.xlt (in XLStart) first. Verify that the cells are
all formatted as general.

Jeffry61 wrote:

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.

--

Dave Peterson


--

Dave Peterson



  #11   Report Post  
Dave Peterson
 
Posts: n/a
Default

Glad you found it.

Personally, I'd get rid of all the extra versions of book.xlt and sheet.xlt.

And I'd keep the real ones in the folder that excel likes:

Open excel
hit alt-f11 (to get to the vbe)
hit ctrl-g (to see the immediate window)
type this and hit enter
?application.startuppath
For me (winXP and xl2003), I get:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART



Jeffry61 wrote:

Sorry, the location I mention below is one that made no difference. The
location that worked after changing the templates is c:\Program
Files\Microsoft Office10\XLstart. There were 5 sets of templates in 5
different locations, but this one is the one that matters.

"Jeffry61" wrote:

IT's FIXED! Turns out that if you search for Book.xlt and sheet.xlt, you
will find all of them EXCEPT the ones in C:\
documents and settings\%username%\Application Data\Microsoft\Templates. I
overwrote the ones there and the problem is fixed.

Thanks!

"Jeffry61" wrote:

Dave,
This seems to happen because the initial format always automatically comes
up text. I searched for all the template .xlt files and replaced them with
known good ones that include general format, but that made no difference. If
you click on new spreadsheet or open Excel by its icon, the new spreadsheet
comes up that way only on this PC.
If you change the Syle Format from text to general it works, but the next
new spreadsheet defaults to text again.

"Dave Peterson" wrote:

It sounds like that user on the other pc is formatting the cell (all the
cells???) as Text.

It could be a choice by the user.
It could be that the user copied another worksheet that was set up that way.
It could be that they created a book.xlt template workbook that was set up for
text.

Difficult to know without asking that other user.

I think I'd look at the book.xlt (in XLStart) first. Verify that the cells are
all formatted as general.

Jeffry61 wrote:

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #12   Report Post  
Jeffry61
 
Posts: n/a
Default

Application Startup path =C:\Documents and Settings\CALHOJ\Application
Data\Microsoft\Excel\XLSTART

Path which takes precedence and does not deliver search file results =
C:\Program Files\Microsoft Office\Office10\XLStart . This is also the path
I discovered via Excel Help. Template files here do not show up in search
results if you do a search for them. If template .Xlt files are in both
paths, the ones found here will override the files in the Application Startup
path! However, if template files are only in the Application Startup path
then they work fine.


"Dave Peterson" wrote:

Glad you found it.

Personally, I'd get rid of all the extra versions of book.xlt and sheet.xlt.

And I'd keep the real ones in the folder that excel likes:

Open excel
hit alt-f11 (to get to the vbe)
hit ctrl-g (to see the immediate window)
type this and hit enter
?application.startuppath
For me (winXP and xl2003), I get:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART



Jeffry61 wrote:

Sorry, the location I mention below is one that made no difference. The
location that worked after changing the templates is c:\Program
Files\Microsoft Office10\XLstart. There were 5 sets of templates in 5
different locations, but this one is the one that matters.

"Jeffry61" wrote:

IT's FIXED! Turns out that if you search for Book.xlt and sheet.xlt, you
will find all of them EXCEPT the ones in C:\
documents and settings\%username%\Application Data\Microsoft\Templates. I
overwrote the ones there and the problem is fixed.

Thanks!

"Jeffry61" wrote:

Dave,
This seems to happen because the initial format always automatically comes
up text. I searched for all the template .xlt files and replaced them with
known good ones that include general format, but that made no difference. If
you click on new spreadsheet or open Excel by its icon, the new spreadsheet
comes up that way only on this PC.
If you change the Syle Format from text to general it works, but the next
new spreadsheet defaults to text again.

"Dave Peterson" wrote:

It sounds like that user on the other pc is formatting the cell (all the
cells???) as Text.

It could be a choice by the user.
It could be that the user copied another worksheet that was set up that way.
It could be that they created a book.xlt template workbook that was set up for
text.

Difficult to know without asking that other user.

I think I'd look at the book.xlt (in XLStart) first. Verify that the cells are
all formatted as general.

Jeffry61 wrote:

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

  #13   Report Post  
Dave Peterson
 
Posts: n/a
Default

All this confusion is caused by excel trying to respect your existing
files/folders when you go through different upgrades.

When I upgraded, I found all my templates and made sure that the only ones I
kept were in the startuppath folder. Even though excel can handle this, it was
too confusing for me.

Jeffry61 wrote:

Application Startup path =C:\Documents and Settings\CALHOJ\Application
Data\Microsoft\Excel\XLSTART

Path which takes precedence and does not deliver search file results =
C:\Program Files\Microsoft Office\Office10\XLStart . This is also the path
I discovered via Excel Help. Template files here do not show up in search
results if you do a search for them. If template .Xlt files are in both
paths, the ones found here will override the files in the Application Startup
path! However, if template files are only in the Application Startup path
then they work fine.

"Dave Peterson" wrote:

Glad you found it.

Personally, I'd get rid of all the extra versions of book.xlt and sheet.xlt.

And I'd keep the real ones in the folder that excel likes:

Open excel
hit alt-f11 (to get to the vbe)
hit ctrl-g (to see the immediate window)
type this and hit enter
?application.startuppath
For me (winXP and xl2003), I get:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART



Jeffry61 wrote:

Sorry, the location I mention below is one that made no difference. The
location that worked after changing the templates is c:\Program
Files\Microsoft Office10\XLstart. There were 5 sets of templates in 5
different locations, but this one is the one that matters.

"Jeffry61" wrote:

IT's FIXED! Turns out that if you search for Book.xlt and sheet.xlt, you
will find all of them EXCEPT the ones in C:\
documents and settings\%username%\Application Data\Microsoft\Templates. I
overwrote the ones there and the problem is fixed.

Thanks!

"Jeffry61" wrote:

Dave,
This seems to happen because the initial format always automatically comes
up text. I searched for all the template .xlt files and replaced them with
known good ones that include general format, but that made no difference. If
you click on new spreadsheet or open Excel by its icon, the new spreadsheet
comes up that way only on this PC.
If you change the Syle Format from text to general it works, but the next
new spreadsheet defaults to text again.

"Dave Peterson" wrote:

It sounds like that user on the other pc is formatting the cell (all the
cells???) as Text.

It could be a choice by the user.
It could be that the user copied another worksheet that was set up that way.
It could be that they created a book.xlt template workbook that was set up for
text.

Difficult to know without asking that other user.

I think I'd look at the book.xlt (in XLStart) first. Verify that the cells are
all formatted as general.

Jeffry61 wrote:

Dave, your fix works, but it must be applied to every new spreadsheet.
Formulas is not checked, but the new spreadsheets do not have general
formatting to begin with. If new spreadsheet is changed to general
formatting before use, replacing = with = can be skipped.

Don't know why the replacing = with = corrects bad sheets after changing
their format, but more importantly don't know why I have to change the format
for each new sheet.
Thanks.

"Dave Peterson" wrote:

Try formatting all the cells with formulas as General (all the cells on the
worksheet???).

Select your range to fix (ctrl-a (twice in xl2003) will select all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Don's guess was the equivalent of
Tools|Options|View tab|unchecking formulas
(You may want to double check that before you start)

Jeffry61 wrote:

New info: according to the Formula watch window, the cells with formulas are
not seen as formulas, but as data. Typing = in a cell starts the formula
assistant, but when done it is seen as data instead of a formula.

"Jeffry61" wrote:

Any Excel sheet created by another PC is ok. Any sheet created by this PC
will not calculate regardless of the PC used. The formulas display, but not
the results. Comparing every option of Tools Protection, Tools Options,
Format cells or style between 2 pcs when each is loaded with their own
created spreadsheets, show no difference. No errors are detected. It does
not matter what kind of formula or how simple.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Links between sheets Jack Excel Discussion (Misc queries) 2 June 21st 05 11:17 PM
How Excel & ACCPAC 6.1 calculate formulas???? Bass Mama1 Excel Worksheet Functions 1 February 9th 05 04:25 PM
Problem with named formula's nathan Excel Worksheet Functions 0 January 21st 05 05:07 PM
Formulas Stan Excel Worksheet Functions 3 January 21st 05 03:58 PM
calculating formulas for all workbooks in a folder Chad Excel Worksheet Functions 3 November 13th 04 06:22 PM


All times are GMT +1. The time now is 10:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"