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. |
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) |