Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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" 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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 wrote in message ups.com... 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" 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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 wrote in message ups.com... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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 "Biff" wrote in message ... 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 wrote in message ups.com... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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 wrote in message oups.com... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
Appreciated Biff.
Geoff. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
Here's your file:
http://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 wrote in message ups.com... Appreciated Biff. Geoff. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
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 wrote in message oups.com... 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbered Lists - Take 2
Appreciate the effort Biff.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbered Lists | Excel Discussion (Misc queries) | |||
Capitalize Text if 2 lists are equal | New Users to Excel | |||
Excel - need a function to compare lists | Excel Worksheet Functions | |||
Numbered Lists! | Excel Discussion (Misc queries) |