software

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
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: quot;Otto Moehrbachquot; gt;
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: - view profile
Date: Tues, Mar 14 2006 7:41 am
Email:
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.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!

gt; 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

gt; wrote in message ups.com...
gt;I am relisting this topic in the hope that someone will be able to
gt; help.
gt;
gt; Regards,
gt; Geoff.
gt;
gt;
gt; I have created a spreadsheet that formulates Numbered Lists as a Row
gt; Identifier based on the location of the data within subsequent columns.
gt;
gt; This works well, expect for two things:
gt;
gt; 1. Makes the Document too big. The complete spreadsheet is 16
gt; Worksheets big and has increased in size to 23MB.
gt;
gt; 2. Too slow. Whenever a component is changed, the calculation time
gt; slows down the process to a crawl.
gt;
gt; To see one of the Worksheets see
gt; users.cyberone.com.au/gdurham/NumberedLists.xls.
gt;
gt; To see the formulae - Unhide Columns A through to R.
gt;
gt; Can anyone offer any alternatives or nicer ways to do this.
gt;
gt; Thanks,
gt; Geoff.
gt;
gt;
gt; Reply
gt;
gt; From: Otto Moehrbach - view profile
gt; Date: Tues, Mar 14 2006 1:07 am
gt; Email: quot;Otto Moehrbachquot; gt;
gt; Groups: microsoft.public.excel.misc
gt;
gt; As a first guess, I would say that your file is that big because Excel
gt; thinks the file is bigger than it actually is. Do this for each sheet
gt; in
gt; the file.
gt; Find the last occupied cell in the sheet. Do this manually by
gt; scrolling to
gt; that last cell.
gt; Note that cell address.
gt; Now do Ctrl-End. That takes you to the cell that Excel thinks is the
gt; last
gt; cell of your data. Note that cell address.
gt; If those 2 cells are widely apart, that could explain why your file is
gt; so
gt; large.
gt; Post back for some remedies if that is the case. HTH Otto
gt;
gt; Reply
gt;
gt; From: - view profile
gt; Date: Tues, Mar 14 2006 7:41 am
gt; Email:
gt; Groups: microsoft.public.excel.misc
gt;
gt; Thanks Otto, but the end of the document as perceived by Excel is only
gt; a few lines below the text. I think the problem more relates to the
gt; fact that because the formula for each line is dependant on the line
gt; above it, if you change a line all calculations in the lines below are
gt; recalculated. I was hoping that someone has done something similar or
gt; a formula guru could have a look at my logic.
gt;
gt; Regards,
gt; Geoff.
gt;
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.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 amp; ROW()) to define
a cell reference?

Those INDIRECT calls are killing you!

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

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

Biff

gt; wrote in message ups.com...
gt;I am just trying to find a simpler way of providing the automatic
gt; numbered list that is seen in columns S-Z. If you add or delete any of
gt; the entries in columns AA-AH, you will see the result in the Numbered
gt; List.
gt;
gt; Geoff.
gt;
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

quot;Biffquot; gt; wrote in message
...
gt; As best as I can calculate, you have a total of 38,845 calls to INDIRECT.
gt;
gt; Is there a specific reason you're using INDIRECT(col_ref amp; ROW()) to
gt; define a cell reference?
gt;
gt; Those INDIRECT calls are killing you!
gt;
gt;gt;If you add or delete any of the entries in columns AA-AH,
gt;gt;you will see the result in the Numbered List.
gt;
gt; I'm afraid to do anything that'll trigger a calculation!
gt;
gt; Biff
gt;
gt; gt; wrote in message
gt; ups.com...
gt;gt;I am just trying to find a simpler way of providing the automatic
gt;gt; numbered list that is seen in columns S-Z. If you add or delete any of
gt;gt; the entries in columns AA-AH, you will see the result in the Numbered
gt;gt; List.
gt;gt;
gt;gt; Geoff.
gt;gt;
gt;
gt;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.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

gt; wrote in message oups.com...
gt; No specific reason - it was the only way I could get the Numbered Lists
gt; to work. If there is another way, I am very open to any alternatives.
gt;
gt; Making a change in columns AA-AH does work. The calculations are slow
gt; (prob about 2-5 sec) but you won't be sitting there for minutes on end.
gt;
gt; Regards,
gt;
gt; Geoff.
gt;
Appreciated Biff.

Geoff.Here's your file:

s54.yousendit.com/d.aspx?id=0...W2338JVIPMNQ75

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

gt; wrote in message ups.com...
gt; Appreciated Biff.
gt;
gt; Geoff.
gt;
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.