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.

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.

Niek,

Thanks for your helps. As you instructed, I tried CTRL+ALT+SHIFT+F9 and
waited for 7 minutes. It's still not working. I also tried CTRL+ALT+F9 (and
waited 10 minutes), and it's still not working.

Do you have any other suggestions? 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.

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.

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.

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.

<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.

Niek,

As much as I would like to send the file to you, unfortunately, I can't.
Could you help me without the file?

It looks like the problem is on the array formula in the Data worksheet.
When I do the replace on = sign, I can see that the formula start to
calculate and populate the value. Then it stops.

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.



.

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.



.

<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
...
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.



.

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
...
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.



.

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
...
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.



.

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

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


.

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?