About 10 days ago via this medium I was shown how to count the # of
worksheets using "INDIRECT".
Since Monday I have been struggling with a workbook that seems
self-corrupting.
My workbook has about 100 worksheets; each worksheet contains an
amortization schedule for the debt on a vehicle.
All amortization schedules have an identical structure.
Size of workbook is just under 30MB.
Problem is the workbook will not open properly. Error messages include
"cannot read file" and "damage to the file was so extensive that repairs were
not possible".
In my development of this workbook I progressively saved under different
version names and have been able to surmise that my problem began when I made
a modification to the amortization table while grouping all the worksheets
together (at that point probably <50 worksheets). Normally this should not
have been a problem. My question is, does EXCEL not "work" properly when I
have a combination of very large workbook and INDIRECT? My plan is to start
over limiting workbooks to 10 worksheets only and linking workbooks to
combine results. Any suggestions/wisdom for avoiding this mess in future
would be appreciated.
Many thanks,
Stephen Powell

Stephen POWELL wrote...
....
Since Monday I have been struggling with a workbook that seems
self-corrupting.
My workbook has about 100 worksheets; each worksheet contains an
amortization schedule for the debt on a vehicle.
All amortization schedules have an identical structure.
Size of workbook is just under 30MB.

Amortization tables are generally pretty simple, and a single one
shouldn't take up 300KB (30MB / 100). What's in these worksheets? How
heavily formatted are they? My point in asking is that no matter how
much RAM you have, Excel works poorly with workbooks larger than 16MB.

That said, if you need access to all amortization table values at each
payment for each vehicle, you should be using a proper database rather
than Excel. You may want to use Excel to create the amortization table
values for each vehicle, and merge them into a common database table.
As long as vehicle ID and payment dates are fields along with principal
and interest payment amounts (total payment amount is unnecessary as
it's just the sum of principal and interest payment amounts), you can
generate entire amortization tables for each vehicle as well as
calculate principal, interest and total payments for all vehicles in
any given period.

Harlan:
Thank you for your reply. I appreciate the voice of experience telling me
how large workbooks can be before performance starts to deteriorate.
The reason each amort schedule is so large probably stems from the form in
which the source data is obtained. I copy data from the website of our fleet
manager. These data are in the form of text strings that combine the date
and the dollar amounts. I need to dissect these strings to pluck out dates
and amounts separately. Rather awkward.
I have no knowledge of databases like Access. My approach for now is to
break the population into 10 workbooks of 10 vehicles each and then have an
eleventh workbook that links to these 10 in order to consolidate the results.
Adequate and functioning is better than ideal and non-functioning.
Thanks again,
Stephen Powell

"Harlan Grove" wrote:

Stephen POWELL wrote...
....
Since Monday I have been struggling with a workbook that seems
self-corrupting.
My workbook has about 100 worksheets; each worksheet contains an
amortization schedule for the debt on a vehicle.
All amortization schedules have an identical structure.
Size of workbook is just under 30MB.

Amortization tables are generally pretty simple, and a single one
shouldn't take up 300KB (30MB / 100). What's in these worksheets? How
heavily formatted are they? My point in asking is that no matter how
much RAM you have, Excel works poorly with workbooks larger than 16MB.

That said, if you need access to all amortization table values at each
payment for each vehicle, you should be using a proper database rather
than Excel. You may want to use Excel to create the amortization table
values for each vehicle, and merge them into a common database table.
As long as vehicle ID and payment dates are fields along with principal
and interest payment amounts (total payment amount is unnecessary as
it's just the sum of principal and interest payment amounts), you can
generate entire amortization tables for each vehicle as well as
calculate principal, interest and total payments for all vehicles in
any given period.