Results 1 to 15 of 15
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 105 MG file - Please Help (Office 2003)

    Welcome to Woody's Lounge!

    From your description, I'd say that it might be more efficient to store your data in a database (such as Access) and use queries to combine data from different tables.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 105 MG file - Please Help (Office 2003)

    Hi There

    I have looked through the Lounge and I have found this <post:=501,067>post 501,067</post:> which has some great links and ideas on how to reduce the size of excel workbooks. Never tried these myself so it may be a good idea to use on backed up files.....Otherwise I reckon a database is the way forward.
    Jerry

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Location
    New York, New York, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    Thanks very much Hans and Jerry! The issue is simply that I am not familiar enough with Access or VB to use either very extensively for this model. Unfortunately, I've only got a week or two to finish this up, and the learning curve for both are these is a bit too steep for me. Additionally, I need to keep this in an application that is widely used and understood amongst future users of this model, and Excel seemed to be the "winner". I realize that, when all is said and done, I might just have to break this file up and have even more workbooks than I already plan to maintain, but thought I'd just give it one last try in Woody's Lounge.

    Lindsay

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 105 MG file - Please Help (Office 2003)

    I don't know what you are looking up, but many times this type of structure can be mimicked with a pivot table which should have a lot less overhead than 630,000 formulas...


    Steve

  5. #5
    New Lounger
    Join Date
    Jun 2006
    Location
    New York, New York, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    Hi Jan,

    Amazing! The file size has dropped like a stone: from 105 MG down to 22.8! I have never used a named formula before, because I was under the impression that they took up more space instead of less - perhaps that is the case for smaller files. In this instance, though, this is exactly the right approach! Thank you so much, and thank you to everyone who responded to this post! With a file this large, every little bit counts, so I truly appreciate all of your suggestions. I am officially a Woody's Lounge fan for life!

    Warm Regards,
    Lindsay

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    I love it when a plan comes together!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    WOW!!! Lost 82.2 milligrams. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    That is just a little bit. Or a tiny byte.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    A tiny byte here, a tiny byte there, and pretty soon you are talking real megs.
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    This is starting to sound like a commercial ie

  11. #11
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    Another tip is to keep the number of worksheets in a workbook down. Often data stored across multiple worksheets is more efficiently stored in one worksheet.

    When I'm creating a model, I tend to adopt a database approach - by that, I mean that I'll store the data in one or two worksheets (which I keep hidden from the user) and have a front-end worksheet to act as a user interface. I then use VBA and/or lookups to display the relevant data in the front-end sheet. That's usually more space-efficient than having lots of formatting etc. duplicated across several worksheets. If your model is essentially read-only, you'll be fine doing this; otherwise you'll need to learn some VBA to store the values in the hidden sheets.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    Thanks for sharing that information Jan, I've already tried it and saved quite a bit on file size.

    I have one question about how it works. I have several worksheets that are all the same except they are for different divisions. The division all roll up to a company wide worksheet. I used your copy method successfully to replace where I had added up the divisions. In my spreadsheets there are many calculations that involve other pieces of data on the worksheet. Your method worked fine on the rollup worksheet, but when I tried to copy it to one of the division worksheets, it returned the results from the rollup worksheet rather than the division. An example is ^=IF(ISERROR(+N25/N26),"",N25/N26) I use the same formula on each division page as well as the rollup page and copy it across for each month of each year.

    I would presume I would have to create a new name for each division to get the correct results, or is there a way to get the copy to recognize eahc worksheet?

    Thanks

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 105 MG file - Please Help (Office 2003)

    Unfortunately, you do need to repeat this for each worksheet. To avoid clashes of names, prepend the name of your name with the name of the worksheet is is meant for:

    Name: Sheet1!NamedFormula1
    Refersto: =WhateverFormulaYouHave

    I would advise to always use a local defined name for this pupropse, to avoid the confusion you just wrote about whan trying to use a similar (or even identical) formula on multiple worksheets: make them all "Local". This is done easiest by using my name manager ( <img src=/S/free.gif border=0 alt=free width=30 height=15> download from my site), because you get a clear overview of all your names with that tool.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    New Lounger
    Join Date
    Jun 2006
    Location
    New York, New York, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    105 MB file - Please Help (Office 2003)

    I am developing an Excel (2003) model to be used by other groups globally, so I need to keep it as flexible and dynamic as possible while still avoiding complicated VB/looping code and limiting the number of workbooks used (to minimize linking/renaming issues). As this point, I think I will probably still end up with about 15 separate files that will have to be linked in some way, so breaking up the data further is truly a last resort.

    One of the files in this set (which is representative of the others) has reached 105 MB, and I have optimized the formulas within as much as I know how to. In one sheet, I am using INDEX and MATCH formulas to pull relevant data from 2 other static reference sheets in the same workbook (although, I would prefer that these reference sheets actually be linked to other Primary workbooks so that any changes to lists, etc. will update automatically, even without a simple copy and paste macro). This sheet has 6000 rows (future data runs could be fewer rows and could be more) and 105 columns, so my INDEX formulas have been repeated 630,000 times! Each INDEX formula references 3 different column numbers and 2 different row numbers (via MATCH to other reference sheets in the workbook). I have already separated the MATCH formulas from the INDEX formulas, which reduced my file size by a few Megs.

    However, I have been unable to get this file size below 105 MB. I have spoken with a number of Excel experts and have searched the internet/MVP sites for help, but if anyone has any other ideas about how to get this file size down, I would very much appreciate any suggestions you may have.

    Kind Regards,
    Lindsay

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use named formulas to reduce workbook size (all)

    Are these long formulas? And are they all "copies" of one another? If so, you could try assigning a named formula and use the name instead of the formulas.

    How?
    - Make sure you have a backup copy of your file (always a good idea)
    - Select the top-left cell of the range, hit F2, select entire formula and hit control-c to copy it to the clipboard.
    - Choose Insert, name, define from the menu
    - enter a meaningful name for your formula
    - hit tab to select the "Refers To" box and hit control-v to paste your formula in.

    Now select all cells that have a copy of this particular formula and type:
    =TheNameYouJustEntered
    and hit control-enter to commit this change to all selected cells.

    Save and close the file. Compare size with the backup copy.

    I did this with a single sheet workbook with 52480 cells filled with this relatively short formula:
    =SUM(Sheet2!A1:E13)

    after using the named formula trick, filesize dropped from 2,135 Kb to 1,618 Kb.

    An extra advantage of this technique is that you can change the formula in one place and don't need to update all other cells.

    ###############EDITED on June 21, Adding the additional remarks below####################

    Some caution is called for however:

    - Placing a long formula in a name might be troublesome (note that the limit on a formula in a name is 243 characters, so very long formulas have to be split into nested sections of defined names)
    - This only works notably on files with many repeating formulas
    - Note also, that although on the surface you may ease editing the workbook because formulas are now in one place, auditing the workbook may become more difficult (except maybe for the one who created the thing). This is due to the extra abstraction layer these defined formulas introduce. Especially if you're nesting names in the formulas as well and even more so if there are many named formulas to maintain. Using my Name Manager of course helps a lot for this kind of workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •