J
JCWeaver in Raleigh
I have a couple of MS Excel 2003 files consisting of multiple worksheets that
are heavily formulated. I'm running into some issues with Excel just hanging
up when it's calculating.
For instance, when re-calculation is occurring, the "Calculating Cells: XX%"
is visible in the lower left corner of the window. Sometimes the XX values
will slowly reach 100, indicating completion of the calculation cycle. But
more and more, it's not completing and just hangs (for example, reaching
25%). I open up the Task Manager window and it will say the Excel is "Not
Responding".
Specific questions...
(1) How can I troubleshoot this?
(2) Is there way to summarize the number of formulas and compare with
thresholds above which performance problems may arise?
(3) Certain types of formulas that are definite troublemakers?
The most common formula I'm using has worksheets looking up values from
other worksheets (see example below). I will say there's a quite a number of
these formulas in my file doing a lot of cross referencing among worksheets.
But how can one know when you've gotten too large or "too many somethings" in
a given file.
=IF(COUNTIF('Table 1 & 2 combined DBF'!$G$2:$G$394,$H7) > 0, OFFSET('Table 1
& 2 combined DBF'!$G$2,MATCH($H7,'Table 1 & 2 combined DBF'!$G$2:$G$394) - 1,
8), "")
This formula looks to see if a station number exists in a range within
another worksheet...if so, then find the row number, then move 8 columns over
to the corresponding value or site attribute.
I need some help here learning what or how I can troubleshoot these
situations. I would like to avoid splitting the Excel file into smaller
portions, because I'm not certain that's the sole problem.
This is becoming a real matter of frustration here...any guidance
appreciated...thanks.
JCWeaver
are heavily formulated. I'm running into some issues with Excel just hanging
up when it's calculating.
For instance, when re-calculation is occurring, the "Calculating Cells: XX%"
is visible in the lower left corner of the window. Sometimes the XX values
will slowly reach 100, indicating completion of the calculation cycle. But
more and more, it's not completing and just hangs (for example, reaching
25%). I open up the Task Manager window and it will say the Excel is "Not
Responding".
Specific questions...
(1) How can I troubleshoot this?
(2) Is there way to summarize the number of formulas and compare with
thresholds above which performance problems may arise?
(3) Certain types of formulas that are definite troublemakers?
The most common formula I'm using has worksheets looking up values from
other worksheets (see example below). I will say there's a quite a number of
these formulas in my file doing a lot of cross referencing among worksheets.
But how can one know when you've gotten too large or "too many somethings" in
a given file.
=IF(COUNTIF('Table 1 & 2 combined DBF'!$G$2:$G$394,$H7) > 0, OFFSET('Table 1
& 2 combined DBF'!$G$2,MATCH($H7,'Table 1 & 2 combined DBF'!$G$2:$G$394) - 1,
8), "")
This formula looks to see if a station number exists in a range within
another worksheet...if so, then find the row number, then move 8 columns over
to the corresponding value or site attribute.
I need some help here learning what or how I can troubleshoot these
situations. I would like to avoid splitting the Excel file into smaller
portions, because I'm not certain that's the sole problem.
This is becoming a real matter of frustration here...any guidance
appreciated...thanks.
JCWeaver