finding error in xls file

[stef]

Excel 2002 SP3
Win XP Pro SP2

Hi,

Is there a utility or a way for me to find errors in a workbook (xls
file) that could cause errors such as too many resources needed, etc.?

I have a file that seems to have bloated in size all of a sudden, and is
giving me at times an error message as above (and then causes Excel to
crash).

I have used a macro to look at each link and beyond a label in a
spreadsheet, it all looks fine.

I am talking about a "structural" error (as opposed to a calculation
error) perhaps....

 

[Bill Ridgeway]

Excel 2002 SP3
Win XP Pro SP2

Hi,

Is there a utility or a way for me to find errors in a workbook (xls file)
that could cause errors such as too many resources needed, etc.?

I have a file that seems to have bloated in size all of a sudden, and is
giving me at times an error message as above (and then causes Excel to
crash).

I have used a macro to look at each link and beyond a label in a
spreadsheet, it all looks fine.

I am talking about a "structural" error (as opposed to a calculation
error) perhaps....

You think you have 'structural errors' without specifying what they may be
and, of course you don't know what they are or you'd have fixed them. It's
a circular argument (no pun intended).

You could try looking at links.

<Insert>Name><Label><Paste><Paste List> (in a clear area of a workbook) will
give a list of all internal and external links. Those referring to an error
can be deleted <Insert><Name><Define>(highlight)<Delete> which will clear
them away. You could then check each of those that remain to make sure they
refer to where you intended.

<Edit Links> will help with external links

<Tools><Formula auditing><Trace Precedents> and
<Tools><Formula auditing><Trace Dependents>
may help

<Tools><Error Checking> although I must admit I've never used it.

I would suggest you name cells which are referred to twice or more in a
discrete workbook (to bring them all together). In that way when the target
cell is moved all internal references to that cell are moved with it and so
avoiding picking up incorrect information.

I hope these ideas help to resolve your problem.

Bill Ridgeway

 

[stef]

Bill,
Thanks for your feedback.
I've already done all this.

The only "problem" was a link to a "label" in a chart--I really cant'
understand that error--and frankly, it could possibly be causing the
problem but I really don't know.

I will look one more time at the links.

I do try to name ranges of cells--that are used repeatedly.

 

[stef]

I am getting this:

Chart Y Datalabel [could not access the formula] Point 1 of Series 6
Chart Y Datalabel [could not access the formula] Point 47 of Series 5
Chart Y Datalabel [could not access the formula] Point 47 of Series 4
Chart Y Datalabel [could not access the formula] Point 1 of Series 3
Chart Q Datalabel [could not access the formula] Point 1 of Series 6
Chart Q Datalabel [could not access the formula] Point 1 of Series 3
Chart Q Datalabel [could not access the formula] Point 1 of Series 2
Chart M Datalabel [could not access the formula] Point 1 of Series 6
Chart M Datalabel [could not access the formula] Point 1 of Series 3
Chart M Datalabel [could not access the formula] Point 1 of Series 2
Chart M (Alloc) Free Floating Datalabel [could not access the formula]
Point 1 of Series 6
Chart M (Alloc) Free Floating Datalabel [could not access the formula]
Point 1 of Series 3
Chart M (Alloc) Free Floating Datalabel [could not access the formula]
Point 1 of Series 2

Don't really know if this could cause trouble or not....

 

[Bill Ridgeway]

Stef,

There comes a time when it is better to resolve the issue by starting again
rather than spending valuable time working out what causes a problem then
resolving the issue. The latter is more satisfying and provides information
should it raise its ugly head again but I sometime just not economical of
time.

It may be worth keeping a screen dump of all the settings of all the charts
so you have something to work from should every go horribly wrong (like
loosing the whole lot).

Bill Ridgeway
Computer Solutions

I am getting this:

Chart Y Datalabel [could not access the formula] Point 1 of Series 6
Chart Y Datalabel [could not access the formula] Point 47 of Series 5
Chart Y Datalabel [could not access the formula] Point 47 of Series 4
Chart Y Datalabel [could not access the formula] Point 1 of Series 3
Chart Q Datalabel [could not access the formula] Point 1 of Series 6
Chart Q Datalabel [could not access the formula] Point 1 of Series 3
Chart Q Datalabel [could not access the formula] Point 1 of Series 2
Chart M Datalabel [could not access the formula] Point 1 of Series 6
Chart M Datalabel [could not access the formula] Point 1 of Series 3
Chart M Datalabel [could not access the formula] Point 1 of Series 2
Chart M (Alloc) Free Floating Datalabel [could not access the formula]
Point 1 of Series 6
Chart M (Alloc) Free Floating Datalabel [could not access the formula]
Point 1 of Series 3
Chart M (Alloc) Free Floating Datalabel [could not access the formula]
Point 1 of Series 2

Don't really know if this could cause trouble or not....

You think you have 'structural errors' without specifying what they may
be and, of course you don't know what they are or you'd have fixed them.
It's a circular argument (no pun intended).

You could try looking at links.

<Insert>Name><Label><Paste><Paste List> (in a clear area of a workbook)
will give a list of all internal and external links. Those referring to
an error can be deleted <Insert><Name><Define>(highlight)<Delete> which
will clear them away. You could then check each of those that remain to
make sure they refer to where you intended.

<Edit Links> will help with external links

<Tools><Formula auditing><Trace Precedents> and
<Tools><Formula auditing><Trace Dependents>
may help

<Tools><Error Checking> although I must admit I've never used it.

I would suggest you name cells which are referred to twice or more in a
discrete workbook (to bring them all together). In that way when the
target cell is moved all internal references to that cell are moved with
it and so avoiding picking up incorrect information.

I hope these ideas help to resolve your problem.

Bill Ridgeway

 

[stef]

Stef,

There comes a time when it is better to resolve the issue by starting again
rather than spending valuable time working out what causes a problem then
resolving the issue. The latter is more satisfying and provides information
should it raise its ugly head again but I sometime just not economical of
time.

The spreadsheet is rather complex and I am under time constraints to
deliver. So starting form scratch is not really a pleasant thought....

It may be worth keeping a screen dump of all the settings of all the charts
so you have something to work from should every go horribly wrong (like
loosing the whole lot).

How do you actually do a screen dump of the chart settings?

 

[Bill Ridgeway]

The spreadsheet is rather complex and I am under time constraints to
deliver. So starting form scratch is not really a pleasant thought....

How do you actually do a screen dump of the chart settings?

Go into all the areas of the chart (both axis and the plot area) and, at
each place you can change the configuration, click on <Print Screen>. This
will dump the image into the clip board. This image can then be pasted
into, say, Word and printed off. It may be a bit tedious but could
certainly save the day you are forced to start again. It is very difficult,
when the chips are down, to think logically and recoup all the subtleties of
how the heck you set it up in the first place and amended over time.

This can (?should) be done for the same reasons and in the same way to keep
a copy of, say Excel, Word <Tools><Options> and other software.

Bill Ridgeway
Computer Solutions

 

[stef]

Bill,
Thanks for feedback.
I am aware of copy/paste as an image--I didn't realize that's what you
called a "screen dump".
This does not work for me as I need to correct the errors as opposed to
use another document, etc.