Numbered Lists - Take 2

[geoff_durham]

I am relisting this topic in the hope that someone will be able to
help.

Regards,
Geoff.


I have created a spreadsheet that formulates Numbered Lists as a Row
Identifier based on the location of the data within subsequent columns.

This works well, expect for two things:

1. Makes the Document too big. The complete spreadsheet is 16
Worksheets big and has increased in size to 23MB.

2. Too slow. Whenever a component is changed, the calculation time
slows down the process to a crawl.

To see one of the Worksheets see
http://users.cyberone.com.au/gdurham/NumberedLists.xls.

To see the formulae - Unhide Columns A through to R.

Can anyone offer any alternatives or nicer ways to do this.

Thanks,
Geoff.


Reply

From: Otto Moehrbach - view profile
Date: Tues, Mar 14 2006 1:07 am
Email: "Otto Moehrbach" <[email protected]>
Groups: microsoft.public.excel.misc

As a first guess, I would say that your file is that big because Excel
thinks the file is bigger than it actually is. Do this for each sheet
in
the file.
Find the last occupied cell in the sheet. Do this manually by
scrolling to
that last cell.
Note that cell address.
Now do Ctrl-End. That takes you to the cell that Excel thinks is the
last
cell of your data. Note that cell address.
If those 2 cells are widely apart, that could explain why your file is
so
large.
Post back for some remedies if that is the case. HTH Otto

Reply

From: (e-mail address removed) - view profile
Date: Tues, Mar 14 2006 7:41 am
Email: (e-mail address removed)
Groups: microsoft.public.excel.misc

Thanks Otto, but the end of the document as perceived by Excel is only
a few lines below the text. I think the problem more relates to the
fact that because the formula for each line is dependant on the line
above it, if you change a line all calculations in the lines below are
recalculated. I was hoping that someone has done something similar or
a formula guru could have a look at my logic.

Regards,
Geoff.

 

[Biff]

Hi!

Eeek!

Well, right off the top, you're using 1000's of volatile functions,
INDIRECT. And this is only one sheet. If the othe 15 sheets are like this
one calculation time has to be slooooooooow!

Can anyone offer any alternatives or nicer ways to do this.

I didn't do an exhaustive study to try and figure out what you're doing, so,
if you can provide an explanation that'll help!

Biff

 

[geoff_durham]

I am just trying to find a simpler way of providing the automatic
numbered list that is seen in columns S-Z. If you add or delete any of
the entries in columns AA-AH, you will see the result in the Numbered
List.

Geoff.

 

[Biff]

As best as I can calculate, you have a total of 38,845 calls to INDIRECT.

Is there a specific reason you're using INDIRECT(col_ref & ROW()) to define
a cell reference?

Those INDIRECT calls are killing you!

If you add or delete any of the entries in columns AA-AH,
you will see the result in the Numbered List.

I'm afraid to do anything that'll trigger a calculation!

Biff

 

[Peo Sjoblom]

Add some circular references in there somewhere and the fun can begin

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[geoff_durham]

No specific reason - it was the only way I could get the Numbered Lists
to work. If there is another way, I am very open to any alternatives.

Making a change in columns AA-AH does work. The calculations are slow
(prob about 2-5 sec) but you won't be sitting there for minutes on end.

Regards,

Geoff.

 

[Biff]

Well, one thing that's working for you is that at least the formulas are
fairly simple.

It's getting late where I'm at (eastern U.S.) so I'll take a closer look at
it tomorrow.

I'm following your logic and you may be able to do this without all those
helper columns but it'll take some time to figure it out.

Biff

 

[geoff_durham]

Appreciated Biff.

Geoff.

 

[Biff]

Here's your file:

http://s54.yousendit.com/d.aspx?id=08WBQJQ142TTW2338JVIPMNQ75

I rewrote all the formulas in columns A:R eliminating all the calls to
INDIRECT.

I rewrote all the formulas in columns S:Z and reduced the use of INDIRECT to
an absolute minimum.

The file size was reduced by 335 kb. From 1.39 mb to 1.07 mb. Calc time (on
my machine) is un-noticeable.

If we can figure a way to do this and eliminate all those helper columns
that will significantly reduce the file size even further. I'll keep
tinkering and see if I can come up with something even better.

Biff

 

[geoff_durham]

Biff,

Changes look great and calculation times are brilliant. Only prob I
can determine (and now as I recall one of the reasons why i required
the INDIRECT functions) is that users cannot cut/paste in the
categories/items columns.

Thnaks,

Geoff.

 

[Biff]

well, you have a choice.....

Use it as is and be able to cut/paste but have a bloated slow file or don't
cut/paste and have a smaller more efficient file.

Sometimes there's a trade-off !

Biff

 

[geoff_durham]

Appreciate the effort Biff.