Calculation problems in large workbook

[Shevlin Ryan]

First, thanks to all in this newsgroup who have helped me get this far,
both directly and indirectly. Thanks to your efforts I now have a huge
workbook (5.03 MB, 45 worksheets) that works great, almost.

I use this workbook to price wedding flowers, generate the quote for the
bride, and generate order forms for flowers and supplies. First, there
are several worksheets of "source data" that contain, essentially, my
suppliers' catalogs (products, prices, etc.). Next is a "people"
worksheet for each of the people in the wedding that gets flowers
(bride, groom, attendants, moms, dads, ad infinitum). These draw from
the source data worksheets. Then there is the "summary" worksheet that
gathers information from all the people worksheets and combines that
information with canned phrases. The summary worksheet feeds the
"quote" worksheet that formats all the information into a bride-readable
quote so she can decide if she wants to pick us or someone else. The
reason for the summary worksheet is several of the concatenation
formulas exceed the 256 character limit. Finally, there are "ordering"
worksheets that I print out for my suppliers when time comes to order
flowers and supplies. There is extensive use of VLOOKUP and
CONCATENATION formulas.

The problem I'm having is that sometimes the formulas quit calculating,
or updating. Let's say I change the price of a rose on the "flowers"
source data worksheet. That change does not ripple through the
workbook. A few of the worksheets will update, but the rest do not.
Sometimes I can edit a formula (i.e. change a cell reference), then
change it back and the cell will update to the new price. Sometimes
that doesn't work. Calculation is set to automatic in Tools > Options >
Calculation. F9 (Calculate Now) does not cause it to update. Saving,
closing, and reopening does not cause it to update (even though
Calculating Cells in the lower left screen corner runs up to 100%).

I would guess this is a memory issue, but the Win 98SE System Monitor
shows no swapfile usage and plenty of unused physical memory, and the
harddrive doesn't churn like it's having to swap stuff around.

This is Excel 97, Windows 98 SE, Pentium 4, 512 Meg RAM.

Any suggestions are greatly appreciated. Thanks in advance.

 

[Arvi Laanemets]

Hi

It's difficult to decide over your design without examining your workbook
mor closely, but I make an effort anyway.

Over 5 MB is a bit too much, I'm sure it's possible to cut this number down
somewhat. And at first I think you can considerably cut down the number of
sheets.

You must have all those Suppliers, Products etc. sheets alias registers
of-course. But I'm not sure how you designed this 'People' part actually.
Have you or work you in some wedding firm? (Probably you didn't have so much
work for some single wedding). What happens with all entries, when the
wedding is over? How much weddings you think you need to keep in your table
at once? What is the average number of guests for a wedding, and what
estimate you for max number? I'm not clear about how much 'People' sheets do
you have - one for every wedding, or are they listed just on one sheet?

I think the best design, unless the table will be too huge, would use a
single sheet, something like
WeddingID, Name, Type, ...
where bridge, groom, all guests etc are entered in - a row for every one.
When the wedding is over, you can delete all rows for it, using autofilter,
or you write an VBA procedure for it.

To create Summary sheet which composed long phrases, or Quote sheets, in
such a form as you depicted here, is too cumbersome. I'd advice to create a
couple of report sheets, where all key info for selected wedding is
collected in - a table on a sheet. Use Word Mail Merge with those tables as
datasource to generate final reports. With Word Mail Merge, you can
selectively or print composed documents, or save them as word documents, or
send them as e-mails/faxes. You can have several Mail Merge 'templates'
based on same table, or you can use same Mail Merge 'template' with several
source table. The main condition is, that all data for a Mail Merge report
has to be columnwise in single table on single row - column headings
function as field names. When you have a single Mail Merge source table in
your workbook, then simply place it as first one - and it will be accessible
for Mail Merge. When you have several of them, then you have to define them
as named ranges (and I'm almost sure you can't use dynamic ranges for it).

When your workbook is slow anyway, and you have some sheets with tables with
a lot of formulas, and you change those data not often, then you can split
them into separate workbook, and get data from there using ODBC query - you
can set the query to be refreshed when you open your weddings workbook This
way you cut down the number of formulas in weddings workbook

 

[Niek Otten]

Many tips on saving memory, calculation problems etc can be found at

www.decisionmodels.com


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Shevlin Ryan]

Thanks for the response, Arvi. All the way from Estonia! Fascinating!

Sorry I took so long responding. Too much to do, too little time.

The reason it's so big is trying to accommodate as many of the possible
variations that might be encountered. This is one reason why I went
with Excel instead of Access.

For example, the "standard" wedding has bride, groom, maid of honor,
best man, 0 - 3 bridesmaids, 0 - 3 groomsmen, 2 fathers, and 2 mothers.
Then you throw in extra fathers and mothers (we've had weddings with 4
each), aunts, uncles, brothers, sisters, officiant, reader(s), guest
book attendants, ring bearer, flower girl(s), greeter(s), ad infinitum.
Then you throw in the possibility (it has happened to us) that
everyone gets different flowers and/or colors.

So, we created a workbook template that had a worksheet for each of the
usual people that could be copied as needed for all the others. Each
sheet has a place for each flower (up to 12), each green (up to 6), and
all the components (hardware) that go into making a bouquet, corsage, or
boutonniere. There are also worksheets for cake flowers, petals,
centerpieces, altar arrangements, aisle decorations, etc.

All my wife has to do is enter on a worksheet, the individual flower
types, color/variety, greens, hardware, and quantities. The formulas on
that sheet pull what they need from the vendors' sheets. Once
everything is done, she saves the template as the bride's name in the
bride's folder. If the bride has changes (they usually do), they are
quick and easy to make and reprint the quote.

On a perfect day, once she has everything entered, all she has to do is
print the quote worksheet, the flower wholesaler's worksheet (which
looks like a fax page), and the hard goods worksheet. She sends the
quote sheet to the bride for approval. When that comes back OK, she
faxes the flower wholesaler's worksheet and takes the hard goods
worksheet to the store for the vases, bouquet holders, etc.

On a less than perfect day she has the problem of formulas not
calculating no matter what she does (F9, Calculate Now, save/exit/get
back in, even rebooting doesn't help). It calculates up to a point and
stops! Hence my post.

Since my post, she's tried it on my computer with a file that she had
many problems with. After about a half hour she gave up. It would not
misbehave, so it's looking like something peculiar about her computer.
She has 512 M RAM, I only have 256 M. Exact same operating systems (Win
98 SE), exact same Excel 97 program.

Any ideas?

Thanks.

 

[Shevlin Ryan]

Thanks for the tip, Niek.

The site didn't say "Here's the answer to your specific problem..." (I
didn't expect it to), but it did spark some ideas of what to look for
next time it happens.

 

[Arvi Laanemets]

Hi

Maybe you can mail me your workbook(s), so I can look at it/them more
closely. I'm a feeling I can simplify it, but I'm afraid it's too
complicated to try it based on this sparse explanation here.

When you decide to post it, use the address from signature - of course you
have to edit it. When you simply use the replay address from posts header, I
never get it
PS. Pack the workbook in zip-format, and be sure summary size of (packed)
attachment(s) isn't exceeding 2MB - otherwise probably it's cut out in some
of relaying servers.