File I'm modifying works as long as I don't close it; Saved copies crash and burn when opened

[Bruce Cooley]

I have an Excel problem that arises from a complicated chain of events.

Every time I get past a certain point in my current Excel template
construction, any file saved after that will not open without crashing and
destroying itself, even though the file I am working in appears to be just
fine and I can continue modifying it, loading data and running macros and
what-not, and saving as new filenames...as long as I don't close it. Once I
close it, it too will not open, and I have to revert to the last saved
version before that point in the construction of the file just to get the
file to open. I have figured out exactly where the problem starts, but I
have no idea why it's there. Here is what's happening:

I am remodeling a data-processing template to allow it to compare earlier
and later data sets from two partially overlapping groups of respondents
(some will have taken the survey twice).

The data set for the prior survey has a column of calculated averages called
total knowledge. I named this range "sKnow_Tot_Prior". The range name
refers to a formula that locates a dynamic range:
=OFFSET(PriorCleanData!$A$21,0,PriorCleanData!$CO$1,CountPrior,1)

The prior data set also has a column indicating whether the respondent is
one of those who took the survey twice. I named this column
"sRepeat_Prior". Again, this range name refers to a formula to locate a
dynamic range:
=OFFSET(PriorCleanData!$A$21,0,PriorCleanData!$FA$1,CountPrior,1)

To calculate the average total knowledge in the prior data set of those
people who repeated the survey, I use an array formula:

=ROUND(AVERAGE(IF(sRepeat_Prior="Yes",sKnow_Tot_Prior)),0)

To be efficient, I want to use this result elsewhere as well, so I name the
cell "AvgKnowTot_Repeat_Prior".

I go elsewhere and enter the formula "=AvgKnowTot_Repeat_Prior". At exactly
that point, every file saved from this template will crash instead of
opening. It doesn't matter what I name the cell or where I refer to it. As
soon as both a name and reference exist, the latent state "crash-on-open" is
in the file.

Actually, the problem is not quite that simple. I'm pretty sure this
happens only when the reference to the range name is created in a template
that has NO DATA in it. In other words, if I create that range name and
then create the reference to it in a template that already has some sample
data in it , I can save the file and it opens again.

I need an empty template to use on an ongoing basis, but I can't just empty
the data out of the file I am working on because my macros make irreversible
changes to the layout of the data. Therefore I need to recreate every
development step in a file that has no data. It is at this stage that I
can't proceed, because what I am building crashes without data.

Something in my sequence of
"reference-to-a-range-containing-an-array-formula-that-uses-portions-of-dyna
mic-ranges" seems to be going awry when there is no data. As long as I
continue working with the file that is open, everything appears to be
working perfectly. There is no indication that the file is doomed.

I can work around this by being redundant and using the formulas again
instead of referring to the results, but does anyone have any ideas why this
is happening?

Thanks,

Bruce Cooley

 

[Dave Peterson]

I don't have an idea. But when things crash, I turn to questions like:

If you build a test workbook with just enough to test stuff, does that test
workbook crash?

If no, then maybe the crashing workbook is corrupted (or getting corrupted).

Maybe it's time to rebuild the workbook.

But maybe opening it in another program (xl2002+ or OpenOffice), then saving it
there would clean up some of the corruption. (I've used xl2002 to open files
that crashed in xl2k, but I still had to recreate the workbook.)

http://www.openoffice.org, a 60-65 meg download or a CD

And if things go really bad, there are commercial recovery services. I've
never used it, but you might want to check into:
http://www.officerecovery.com

 

[Bruce Cooley]

Thanks for the ideas, Dave. I keep a frequent trail of files with different
names as I build, so I don't need to recover anything. My brother also
suggested trying to recreate the problem by building it into a new workbook
on a tiny scale. I may get around to that. That may be the only way to
find out if there is something inherent in the complex combination of things
I am doing that makes it collapse after that final link is made if there is
no data for the formulas to work with. In the meantime I will work around
the problem.

Bruce



: I don't have an idea. But when things crash, I turn to questions like:
:
: If you build a test workbook with just enough to test stuff, does that
test
: workbook crash?
:
: If no, then maybe the crashing workbook is corrupted (or getting
corrupted).
:
: Maybe it's time to rebuild the workbook.
:
: But maybe opening it in another program (xl2002+ or OpenOffice), then
saving it
: there would clean up some of the corruption. (I've used xl2002 to open
files
: that crashed in xl2k, but I still had to recreate the workbook.)
:
: http://www.openoffice.org, a 60-65 meg download or a CD
:
: And if things go really bad, there are commercial recovery services. I've
: never used it, but you might want to check into:
: http://www.officerecovery.com
:
: Bruce Cooley wrote:
: >
: > I have an Excel problem that arises from a complicated chain of events.
: >
: > Every time I get past a certain point in my current Excel template
: > construction, any file saved after that will not open without crashing
and
: > destroying itself, even though the file I am working in appears to be
just
: > fine and I can continue modifying it, loading data and running macros
and
: > what-not, and saving as new filenames...as long as I don't close it.
Once I
: > close it, it too will not open, and I have to revert to the last saved
: > version before that point in the construction of the file just to get
the
: > file to open. I have figured out exactly where the problem starts, but
I
: > have no idea why it's there. Here is what's happening:
: >
: > I am remodeling a data-processing template to allow it to compare
earlier
: > and later data sets from two partially overlapping groups of respondents
: > (some will have taken the survey twice).
: >
: > The data set for the prior survey has a column of calculated averages
called
: > total knowledge. I named this range "sKnow_Tot_Prior". The range name
: > refers to a formula that locates a dynamic range:
: > =OFFSET(PriorCleanData!$A$21,0,PriorCleanData!$CO$1,CountPrior,1)
: >
: > The prior data set also has a column indicating whether the respondent
is
: > one of those who took the survey twice. I named this column
: > "sRepeat_Prior". Again, this range name refers to a formula to locate a
: > dynamic range:
: > =OFFSET(PriorCleanData!$A$21,0,PriorCleanData!$FA$1,CountPrior,1)
: >
: > To calculate the average total knowledge in the prior data set of those
: > people who repeated the survey, I use an array formula:
: >
: > =ROUND(AVERAGE(IF(sRepeat_Prior="Yes",sKnow_Tot_Prior)),0)
: >
: > To be efficient, I want to use this result elsewhere as well, so I name
the
: > cell "AvgKnowTot_Repeat_Prior".
: >
: > I go elsewhere and enter the formula "=AvgKnowTot_Repeat_Prior". At
exactly
: > that point, every file saved from this template will crash instead of
: > opening. It doesn't matter what I name the cell or where I refer to it.
As
: > soon as both a name and reference exist, the latent state
"crash-on-open" is
: > in the file.
: >
: > Actually, the problem is not quite that simple. I'm pretty sure this
: > happens only when the reference to the range name is created in a
template
: > that has NO DATA in it. In other words, if I create that range name and
: > then create the reference to it in a template that already has some
sample
: > data in it , I can save the file and it opens again.
: >
: > I need an empty template to use on an ongoing basis, but I can't just
empty
: > the data out of the file I am working on because my macros make
irreversible
: > changes to the layout of the data. Therefore I need to recreate every
: > development step in a file that has no data. It is at this stage that I
: > can't proceed, because what I am building crashes without data.
: >
: > Something in my sequence of
: >
"reference-to-a-range-containing-an-array-formula-that-uses-portions-of-dyna
: > mic-ranges" seems to be going awry when there is no data. As long as I
: > continue working with the file that is open, everything appears to be
: > working perfectly. There is no indication that the file is doomed.
: >
: > I can work around this by being redundant and using the formulas again
: > instead of referring to the results, but does anyone have any ideas why
this
: > is happening?
: >
: > Thanks,
: >
: > Bruce Cooley
:
: --
:
: Dave Peterson
: (e-mail address removed)