Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
try holding down the control key and touching the tilde key to the left of
the 1 key at the top -- Don Guillett SalesAid Software "Jeffry61" wrote in message ... 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. |
#3
|
|||
|
|||
That did not help any, but thanks.
"Don Guillett" wrote: try holding down the control key and touching the tilde key to the left of the 1 key at the top -- Don Guillett SalesAid Software "Jeffry61" wrote in message ... 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links between sheets | Excel Discussion (Misc queries) | |||
How Excel & ACCPAC 6.1 calculate formulas???? | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
Formulas | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |