+ Reply to Thread
Results 1 to 8 of 8

Too many formulae slowing down workbook

  1. #1
    Registered User
    Join Date
    05-27-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    72

    Too many formulae slowing down workbook

    Anyone have any suggestion on how to trim down the amount of work my formulae are doing? Any macro triggered or cell change made in my workbook is causing it to process for about 30 seconds.

    The culprit is a vlookup array and I have no idea how to trim it down and still accomplish the same task. The formula is in 324 cells (9 columns 36 rows) and references over half a million cells. Here is the formula:

    Please Login or Register  to view this content.
    So essentially, based on a serial number in cell D3 it references entries in the ActionLog sheet. Column A in the ActionLog sheet houses the serial number for each entry and Columns B:J house the info I'm listing based on a varying number of entries of that serial number, up to 36.

    Is there a more efficient way to list every entry of a serial number and all of its associated data (columns B:J) simply by typing in a serial number is cell D3?

    I have 1 logical solution but not sure how to implement it, hopefully someone can figure this out for me. If I limit the range of my vlookup array to only be 36 rows (instead of the entire column), it will easily process that data. To make this a viable alternative I would need a way for my workbook to sort my ActionLog sheet based on the serial number inserted into cell D3. If by simply inserting it I can sort the data to put all my rows that serial number at the top of my ActionLog sheet, my vlookup array can work.

    Possible? If so, will this sorting process take up similar memory and cause the same problem anyway?
    Last edited by nohero; 06-28-2011 at 12:37 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Too many formulae slowing down workbook

    A PivotTable might be better. Array formulas are intensive on calculation time.

    You can switch off calculation at the start of the code.

    Please Login or Register  to view this content.
    Last edited by royUK; 06-16-2011 at 02:48 PM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    05-27-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Too many formulae slowing down workbook

    Well here's the problem with a pivot table. The end user of this workbook will likely be an absolute beginner and have no clue how to navigate an excel file. My experience with pivot tables is that they require some knowledge to manipulate data. My goal for this project is to create a very user-friendly and even idiot-proof main page that easily displays and prints the information needed.

    I'd like the end user's responsibility to end at typing in the serial number and hitting the print button. The format on the printed page is very easy to read and organized in appropriately titled columns.

    I will upload a dummy version of my current work a little later and perhaps someone can give me some advice based on what they see.

  4. #4
    Registered User
    Join Date
    05-27-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Too many formulae slowing down workbook

    Ok here's my work so far. Any tab other than "Main" will be hidden and all info after column "O" will be hidden. Every cell in the workbook will be locked except D3 on the "Main" sheet.

    A lot of my code is messy I warn you now, I'm quite new to this. Not yet functional sections shouldn't be a problem, just haven't put in the time yet.

    Right now my vlookup array (beginning cell X1) only covers 16 rows so everything runs smoothly, this needs to change as thousands of lines of info will be added to the ActionLog tab.

    Any suggestion on how to make this work or will I be in need of a complete overhaul?

    Example.xlsm

  5. #5
    Registered User
    Join Date
    05-27-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Too many formulae slowing down workbook

    I wonder if I set my vlookup array to search 36 rows and maybe add one more button on my main page (near the print and delete) that updates all the info in the history section. This "update" button could trigger a macro that sorts my rows on the ActionLog sheet to make sure any instances of the current serial shown in D3 are actually at the top of the ActionLog sheet and my vlookups won't have to go further to see them.

    Does this sound like a logical solution or is there something better I'm not thinking of?

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Too many formulae slowing down workbook

    1000s of array formulas? I'm sure that the array formulas are the main problem, I had trouble with the code, I'm sure it was because of the calculating of the arrays.

    I've had a look at the code & streamlined it a bit, also added code to switch off calculation & scren updating, then restore it
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-27-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Too many formulae slowing down workbook

    Ok, I do see lots of changes to the code here. Some of my buttons aren't quite behaving as before though. The view/hide button used to hide/unhide columns A:K, now it hides them but won't unhide them when clicked again. Also, my other buttons for print and delete behave a little differently now, they hide and unhide when any of the buttons are interacted with whereas before they would only appear when A:K were visible.

    As far as the code to switch screen updating on and off, how/when is that being triggered?

    The version of the sheet I gave you has 48 rows x 9 columns of vlookup arrays (X1:AF48), these formulae are only referencing 16 rows of data on my ActionLog sheet in their current form... my problem was that to make them do what I need them to do I would have to have them reference all 65k rows of my ActionLog sheet. That`s where the problem would come in but the sheet as I uploaded it doesn`t have that problem per se... which is why I was looking at an alternative way to pull info from the ActionLog sheet to the Main sheet in columns A:K beginning with row 22.

  8. #8
    Registered User
    Join Date
    05-27-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Too many formulae slowing down workbook

    Other changes to my work made this thread irrelevant. Thanks for trying to help Roy.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1