Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Worksheet Issues
I am working with a customer who is encountering issues that I cannot fix.
She is using 2k and the workbook itself is huge: 16.5 MB's. Does anyone have any idea what the issues might be abd how to fix it? The explanation of what is occurring is below: Wendy says: It's the same old problem... As you know, the equal sign - when it's the first character entered to a cell - should tell Excel that a formula or function will be following it...and to perform the function. However, this often doesn't happen in my Excel workbooks. After entering an equal sign, I will often see the formula itself typed out as TEXT. This doesn't apply to all worksheets, nor to all cells on one worksheet. As an FYI, I don't have Excel setup to display formulas. The attached file is an example of Excel entering formulas as text instead of taking them as formulas. You might want to enter some formulas to the worksheet so you can see what happens. There is a work-around which corrects the problem. This is to format the cells as "general" and then strike the F2 key. I don't know why this works, but it does. However, you can only do this to 1 cell at a time. This is VERY tedious. You can do this to one cell and then copy the cell. That works. I go through this almost every day. I'd appreciate any help you can give me. Thanks, Wendy p.s. Maybe MicroSoft has a solution. It seems like it should be their issue. As long as you're dealing with ugly Excel problems, I'll add one more....just in case you run across the solution to it while you're researching the other problem. The 2nd problem is also very time-consuming and, for this reason, very frustrating. Especially when you're under tight time constraints. I'm attaching a file which shows the gyrations I go through for some lookups. Two sheets illustrate the problem. They're the first 2 sheets in the file ("WPR_Wksht#1" and "Volume Drivers"). The columns in bright green are the columns used in the lookups. The headings explain the data in the various columns. If it's easier, we could walk through the problem together. Just give me a call at your convenience. (Note: this problem almost always occurs with data that's come from the mainframe. Although, since XP has been installed it also occassionally occurs with data that's been keyed.) These two problems take a lot of my time and it seems like it's always when time is short. I would greatly appreciate it if I didn't have to deal with them any longer!!! Thank you for your help, Wendy 11/3/2006 2:09:22 PM LLAROSA1 downloads from mainframe happens often,. also happens with new documents, but more with these downloaded batch files. The batch files are exported in ..cvs we think. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Worksheet Issues
Lisa, the first problem sounds like the cells are formatted as text, change
the cells to something else BEFORE putting the formulas in -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Lisa AGA" wrote in message ... I am working with a customer who is encountering issues that I cannot fix. She is using 2k and the workbook itself is huge: 16.5 MB's. Does anyone have any idea what the issues might be abd how to fix it? The explanation of what is occurring is below: Wendy says: It's the same old problem... As you know, the equal sign - when it's the first character entered to a cell - should tell Excel that a formula or function will be following it...and to perform the function. However, this often doesn't happen in my Excel workbooks. After entering an equal sign, I will often see the formula itself typed out as TEXT. This doesn't apply to all worksheets, nor to all cells on one worksheet. As an FYI, I don't have Excel setup to display formulas. The attached file is an example of Excel entering formulas as text instead of taking them as formulas. You might want to enter some formulas to the worksheet so you can see what happens. There is a work-around which corrects the problem. This is to format the cells as "general" and then strike the F2 key. I don't know why this works, but it does. However, you can only do this to 1 cell at a time. This is VERY tedious. You can do this to one cell and then copy the cell. That works. I go through this almost every day. I'd appreciate any help you can give me. Thanks, Wendy p.s. Maybe MicroSoft has a solution. It seems like it should be their issue. As long as you're dealing with ugly Excel problems, I'll add one more....just in case you run across the solution to it while you're researching the other problem. The 2nd problem is also very time-consuming and, for this reason, very frustrating. Especially when you're under tight time constraints. I'm attaching a file which shows the gyrations I go through for some lookups. Two sheets illustrate the problem. They're the first 2 sheets in the file ("WPR_Wksht#1" and "Volume Drivers"). The columns in bright green are the columns used in the lookups. The headings explain the data in the various columns. If it's easier, we could walk through the problem together. Just give me a call at your convenience. (Note: this problem almost always occurs with data that's come from the mainframe. Although, since XP has been installed it also occassionally occurs with data that's been keyed.) These two problems take a lot of my time and it seems like it's always when time is short. I would greatly appreciate it if I didn't have to deal with them any longer!!! Thank you for your help, Wendy 11/3/2006 2:09:22 PM LLAROSA1 downloads from mainframe happens often,. also happens with new documents, but more with these downloaded batch files. The batch files are exported in .cvs we think. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Worksheet Issues
First Problem:
it sounds like your template is set to format all cells as text. If this is the case, then would I suggest changing your templat to format all cells as General. Either that or change the format before you enter your formulas. The reason your workaround (format as general and then hit F2 and enter) works is because when you change the format from text to a number, excel does not automatically change the format until you edit the cell. F2 is the shortcut to edit the cell. The following code was provided by Chip Pearson in another post. I haven't tried it but it should work. I think you have to change the format first, select the cells with formulas, and then run the macro. Sub AAA() Dim Rng As Range For Each Rng In Selection.Cells Rng.Formula = Rng.Formula Next Rng End Sub I can't help you with the second problem. "Lisa AGA" wrote: I am working with a customer who is encountering issues that I cannot fix. She is using 2k and the workbook itself is huge: 16.5 MB's. Does anyone have any idea what the issues might be abd how to fix it? The explanation of what is occurring is below: Wendy says: It's the same old problem... As you know, the equal sign - when it's the first character entered to a cell - should tell Excel that a formula or function will be following it...and to perform the function. However, this often doesn't happen in my Excel workbooks. After entering an equal sign, I will often see the formula itself typed out as TEXT. This doesn't apply to all worksheets, nor to all cells on one worksheet. As an FYI, I don't have Excel setup to display formulas. The attached file is an example of Excel entering formulas as text instead of taking them as formulas. You might want to enter some formulas to the worksheet so you can see what happens. There is a work-around which corrects the problem. This is to format the cells as "general" and then strike the F2 key. I don't know why this works, but it does. However, you can only do this to 1 cell at a time. This is VERY tedious. You can do this to one cell and then copy the cell. That works. I go through this almost every day. I'd appreciate any help you can give me. Thanks, Wendy p.s. Maybe MicroSoft has a solution. It seems like it should be their issue. As long as you're dealing with ugly Excel problems, I'll add one more....just in case you run across the solution to it while you're researching the other problem. The 2nd problem is also very time-consuming and, for this reason, very frustrating. Especially when you're under tight time constraints. I'm attaching a file which shows the gyrations I go through for some lookups. Two sheets illustrate the problem. They're the first 2 sheets in the file ("WPR_Wksht#1" and "Volume Drivers"). The columns in bright green are the columns used in the lookups. The headings explain the data in the various columns. If it's easier, we could walk through the problem together. Just give me a call at your convenience. (Note: this problem almost always occurs with data that's come from the mainframe. Although, since XP has been installed it also occassionally occurs with data that's been keyed.) These two problems take a lot of my time and it seems like it's always when time is short. I would greatly appreciate it if I didn't have to deal with them any longer!!! Thank you for your help, Wendy 11/3/2006 2:09:22 PM LLAROSA1 downloads from mainframe happens often,. also happens with new documents, but more with these downloaded batch files. The batch files are exported in .cvs we think. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Worksheet Issues
Ok I am going to have her try this and we'll see what happens. Thank you both
for responding so timely. "Sloth" wrote: First Problem: it sounds like your template is set to format all cells as text. If this is the case, then would I suggest changing your templat to format all cells as General. Either that or change the format before you enter your formulas. The reason your workaround (format as general and then hit F2 and enter) works is because when you change the format from text to a number, excel does not automatically change the format until you edit the cell. F2 is the shortcut to edit the cell. The following code was provided by Chip Pearson in another post. I haven't tried it but it should work. I think you have to change the format first, select the cells with formulas, and then run the macro. Sub AAA() Dim Rng As Range For Each Rng In Selection.Cells Rng.Formula = Rng.Formula Next Rng End Sub I can't help you with the second problem. "Lisa AGA" wrote: I am working with a customer who is encountering issues that I cannot fix. She is using 2k and the workbook itself is huge: 16.5 MB's. Does anyone have any idea what the issues might be abd how to fix it? The explanation of what is occurring is below: Wendy says: It's the same old problem... As you know, the equal sign - when it's the first character entered to a cell - should tell Excel that a formula or function will be following it...and to perform the function. However, this often doesn't happen in my Excel workbooks. After entering an equal sign, I will often see the formula itself typed out as TEXT. This doesn't apply to all worksheets, nor to all cells on one worksheet. As an FYI, I don't have Excel setup to display formulas. The attached file is an example of Excel entering formulas as text instead of taking them as formulas. You might want to enter some formulas to the worksheet so you can see what happens. There is a work-around which corrects the problem. This is to format the cells as "general" and then strike the F2 key. I don't know why this works, but it does. However, you can only do this to 1 cell at a time. This is VERY tedious. You can do this to one cell and then copy the cell. That works. I go through this almost every day. I'd appreciate any help you can give me. Thanks, Wendy p.s. Maybe MicroSoft has a solution. It seems like it should be their issue. As long as you're dealing with ugly Excel problems, I'll add one more....just in case you run across the solution to it while you're researching the other problem. The 2nd problem is also very time-consuming and, for this reason, very frustrating. Especially when you're under tight time constraints. I'm attaching a file which shows the gyrations I go through for some lookups. Two sheets illustrate the problem. They're the first 2 sheets in the file ("WPR_Wksht#1" and "Volume Drivers"). The columns in bright green are the columns used in the lookups. The headings explain the data in the various columns. If it's easier, we could walk through the problem together. Just give me a call at your convenience. (Note: this problem almost always occurs with data that's come from the mainframe. Although, since XP has been installed it also occassionally occurs with data that's been keyed.) These two problems take a lot of my time and it seems like it's always when time is short. I would greatly appreciate it if I didn't have to deal with them any longer!!! Thank you for your help, Wendy 11/3/2006 2:09:22 PM LLAROSA1 downloads from mainframe happens often,. also happens with new documents, but more with these downloaded batch files. The batch files are exported in .cvs we think. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Worksheet | Excel Discussion (Misc queries) | |||
Excel: Relative worksheet references? | Excel Discussion (Misc queries) | |||
RE-submitting of: constructing (complex) variables with worksheet functions | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |