If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
My Excel file is not calculating!
Niek,
Thank you very much for your helps. I have my problems solved. The problem was due to the bad data that I have in the Data worksheet. That is why, the calculation stopped at the first colum with bad data. I was able to solve the problem because of the replacement of = signs. I did one formula at a time, and one of the formula would not let me to pass by. Thanks again. You are a genius. Happy New Year! "Niek Otten" wrote: What (or why) is the purpose of rebuilding the dependency tree? This is an emergency procedure for the very rare case that Excel has messed up its calculation chain. Happens really very, very rarely. Apparently something else is going on with your file. If the fourth argument of the VLOOKUP is FALSE, it can become a real time-consumer. If you happen to use Excel2007 and use VLOOKUP to search a whole column, and the argument can't be found, Excel can seem to get to a halt. In that case, consider searching a smaller range, sort the columns and do two lookups with the fourth argument omitted (or TRUE); one in the key column so you can check that it is there, and a second one (if it is there) to retrieve the result column. This can be up to a few thousand times faster. For lots of information about Excel performance, visit Charles Williams' site: www.decisionmodels.com Don't hesitate to post again in this same thread if you still have problems -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message news Niek, What (or why) is the purpose of rebuilding the dependency tree? Do I have to do the rebuilding every signle time when I use the file? Thanks. "Niek Otten" wrote: Could you please educate why we have to replace the = signs? What do you think the problem is? That is the same as rebuilding the dependency tree. If you wish, you can mail me your workbook. I will not open it if there are any macros in it. I will report anything I might find back to this group. -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message ... Niek, Thank you very much for continuing to help me. As you instructed, I replaced the "=" signs on the Report worksheet, and it seems to work. However, when I press F9, it freezes. So I do the replace ("=" signs) on Data worksheet, and it freezes. Could you please educate why we have to replace the = signs? What do you think the problem is? I don't know this information would make any different. The formula in the Data worksheet are array formula (e.g. {(......)}). In the Report worksheet, there are 3 columns, and all 3 columns are vlookup. The first column is vlookup straight to the data, and the last two columns are vlookup to the array formula in the Data worksheet. For the first column being vlookup straight to the data, after I did the replace on = on the Report worksheet, the first column populates with data. The last two columns do not populate anything. Thanks. "Niek Otten" wrote: Try Find-and-replace, replacing all "=" signs (equal signs) by "=". Yes, replace equals sign by equals sign (for the formula sheet) It wouldn't do any harm to do that for tha data sheet as well -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message ... Niek, By the way, I am using Excel 2003, and I have also tried to use that Excel file in another computer. I had the same problem. Thanks. "Niek Otten" wrote: Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9 -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message ... Good morning all, I have an Excel file with two worksheets: Report and Data. The worksheet Data has data where the data are stored, and the worksheet Report is retrieving the data from Data to prepare a report by entering an ID number. The data are retrieved using the vlookup formula in the Report worksheet. The worksheet Data has 130 columns and 14,000 rows (up to) of data. The way it works is I enter an ID number in the worksheet Report. Then I press F9 to do the calculation and the worksheet Report will start to populate the data. Due to the size of Data worksheet, it takes about 2 minutes to have the Report worksheet fully populated. The problem that I am having is the file freezes after I press F9. On the bottom left corner, it shows Calculating Cells: 0% of Data worksheet, and it would not move forward. I also tried to reduce the number of rows (from 14,000 to 300) to test whether it would help, and it did not help. I have been using the file for months, and I have never had that problem until recently. Recently, I made additions and deletions of columns in Data and rows in Report, but the number of rows and columns stay the same from before. Does anyone have any idea of what causes, and how to resolve it? Thanks. . |
#12
|
|||
|
|||
My Excel file is not calculating!
Thanks for the feedback. I'm really glad you got it solved.
-- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message ... Niek, Thank you very much for your helps. I have my problems solved. The problem was due to the bad data that I have in the Data worksheet. That is why, the calculation stopped at the first colum with bad data. I was able to solve the problem because of the replacement of = signs. I did one formula at a time, and one of the formula would not let me to pass by. Thanks again. You are a genius. Happy New Year! "Niek Otten" wrote: What (or why) is the purpose of rebuilding the dependency tree? This is an emergency procedure for the very rare case that Excel has messed up its calculation chain. Happens really very, very rarely. Apparently something else is going on with your file. If the fourth argument of the VLOOKUP is FALSE, it can become a real time-consumer. If you happen to use Excel2007 and use VLOOKUP to search a whole column, and the argument can't be found, Excel can seem to get to a halt. In that case, consider searching a smaller range, sort the columns and do two lookups with the fourth argument omitted (or TRUE); one in the key column so you can check that it is there, and a second one (if it is there) to retrieve the result column. This can be up to a few thousand times faster. For lots of information about Excel performance, visit Charles Williams' site: www.decisionmodels.com Don't hesitate to post again in this same thread if you still have problems -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message news Niek, What (or why) is the purpose of rebuilding the dependency tree? Do I have to do the rebuilding every signle time when I use the file? Thanks. "Niek Otten" wrote: Could you please educate why we have to replace the = signs? What do you think the problem is? That is the same as rebuilding the dependency tree. If you wish, you can mail me your workbook. I will not open it if there are any macros in it. I will report anything I might find back to this group. -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message ... Niek, Thank you very much for continuing to help me. As you instructed, I replaced the "=" signs on the Report worksheet, and it seems to work. However, when I press F9, it freezes. So I do the replace ("=" signs) on Data worksheet, and it freezes. Could you please educate why we have to replace the = signs? What do you think the problem is? I don't know this information would make any different. The formula in the Data worksheet are array formula (e.g. {(......)}). In the Report worksheet, there are 3 columns, and all 3 columns are vlookup. The first column is vlookup straight to the data, and the last two columns are vlookup to the array formula in the Data worksheet. For the first column being vlookup straight to the data, after I did the replace on = on the Report worksheet, the first column populates with data. The last two columns do not populate anything. Thanks. "Niek Otten" wrote: Try Find-and-replace, replacing all "=" signs (equal signs) by "=". Yes, replace equals sign by equals sign (for the formula sheet) It wouldn't do any harm to do that for tha data sheet as well -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message ... Niek, By the way, I am using Excel 2003, and I have also tried to use that Excel file in another computer. I had the same problem. Thanks. "Niek Otten" wrote: Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9 -- Kind regards, Niek Otten Microsoft MVP - Excel "Accesshelp" wrote in message ... Good morning all, I have an Excel file with two worksheets: Report and Data. The worksheet Data has data where the data are stored, and the worksheet Report is retrieving the data from Data to prepare a report by entering an ID number. The data are retrieved using the vlookup formula in the Report worksheet. The worksheet Data has 130 columns and 14,000 rows (up to) of data. The way it works is I enter an ID number in the worksheet Report. Then I press F9 to do the calculation and the worksheet Report will start to populate the data. Due to the size of Data worksheet, it takes about 2 minutes to have the Report worksheet fully populated. The problem that I am having is the file freezes after I press F9. On the bottom left corner, it shows Calculating Cells: 0% of Data worksheet, and it would not move forward. I also tried to reduce the number of rows (from 14,000 to 300) to test whether it would help, and it did not help. I have been using the file for months, and I have never had that problem until recently. Recently, I made additions and deletions of columns in Data and rows in Report, but the number of rows and columns stay the same from before. Does anyone have any idea of what causes, and how to resolve it? Thanks. . |
#13
|
|||
|
|||
My Excel file is not calculating!
The problem was due to the bad data that I have in the Data worksheet.
That is why, the calculation stopped at the first colum with bad data Can you post a bit more detail on the "bad" data to enrich the newsgroups' experience? Thanks |
#14
|
|||
|
|||
My Excel file is not calculating!
Max,
The bad data that I had were -0's (negative zeros). After I turned all -0's into 0's, it worked perfectly. Thanks. "Max" wrote: The problem was due to the bad data that I have in the Data worksheet. That is why, the calculation stopped at the first colum with bad data Can you post a bit more detail on the "bad" data to enrich the newsgroups' experience? Thanks . |
#15
|
|||
|
|||
My Excel file is not calculating!
The bad data that I had were -0's (negative zeros). After I turned
all -0's into 0's, it worked perfectly Thanks for feeding back. This is new to me. Presume those negative zeros are text numbers? How did you correct these to normal zeros? |
|
Thread Tools | |
Display Modes | |
|
|