Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: How to speed up sluggish worksheet???

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi guys

    To add to all these resources, im much for staying inside Excel and CORRECT was of using Excel, shes fine but when you ask her to carry that extra bag of shopping dinners late ( for all the sinics i say Excel is a girl as she beautiful and cooks better than me!)

    Bt,, If your running a data base follow Chris D lead function are built in to Excel tru click function wizar and select dat base have a play, evem Dsum, or Dget will hyperdrive the cal time.

    BTW no one has said they need the odd cairful planning ie reference cells and who to get then to work, 3 times as long to set up but a million times faster..

    Not one to advertise, but a pal Dave Hawley really cuts the mustard if you need some examples, hes over on http://www.ozgrid.com, and some downloads, but please post your findings and where you failed and suceeded others will find this a wonder resource,

    Just some learning to add to the wonderful advice..
    HTH


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-08 13:39, Big Blue wrote:
    Thanks for effort, but I started with D...
    and wound up with a horrendous spreadhseet
    imagine subtotalling 3 categories of items, by 8 departments, for every week of the year excluding holidays... I had to use arrays
    If possible, restrict the range the array formulas look at dynamically computed item or department ranges.

    Aladin

  3. #13
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin,

    You have stolen my line so im proud you agree withthis post always a battle between the VBA programers and open formula "Guys"

    Arrays are cool but easy i say not to many else Slooow to high light Aladins comments again.

    Look at other ways Aladin say name range.. this is the way to go pure and simple She beautiful and sexy if you name range her.... excel. try and see.. i bet you love her....
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #14
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Big Blue

    I would bet money that your sloooowww Workbook is due to the use of array formulas. I get many clients with the same problem and 99% of the time it's array formulas.

    Array formulas are only a very superficial approach to a problem and are normally suggested by people offering nothing more than quick fix solutions.

    The Database functions in Excel can be used in place of array formulas in MOST cases. They are designed specifically for working with LARGE amounts of data, something array formulas are no good for. They do take longer to set up and plan, but that's the way it is when doing a spreadsheet correctly. If you go here:
    http://www.ozgrid.com/download/default.htm and download: "DFunctionsWithValidation.zip" you will see some uses of these Functions. Normally it requires having hidden rows and/or Columns for your criteria.

    You should also use dynamic named ranges for range referencing in most formulas as this also helps a LOT. http://www.ozgrid.com/Excel/DynamicRanges.htm

    Bottom line Big blue is that the planning of a spreasheet is often more time consuming than setting up, when done efficiently.

    Do yourself a huge favour and use arrays ONLY to pull out some data, then delete them, or better still avoid them altogether. Take the time to learn Pivot Tables, Database function, Subtotals etc

    I do feel for you Big Blue, as I have seen this time and time again. Unfortunately while Q&A forums like this are brilliant, there will always be the odd few that give poor advise.


  5. #15
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to all, especially Dave Hawley. Around here, I'm considered an Excel guru, but I know enough to realize how little I know. I'm taking the advice and exploring Pivot Tables and Dynamic Ranges. Always willing to learn. Thanks again.

  6. #16
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Big Blue

    Hope you master these excellent features.



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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