+ Reply to Thread
Results 1 to 1 of 1

Optimizing Formulas - taking too long to calculate large worksheet

  1. #1
    Registered User
    Join Date
    06-04-2009
    Location
    Australia
    MS-Off Ver
    Excel 2002
    Posts
    14

    Optimizing Formulas - taking too long to calculate large worksheet

    Hi,
    I have created a spreadsheet which uses quite a few formulas and conditional formatting rules to validate data that a user enters.
    The idea is that all cells are locked except the green 'new id' coulmn, which the user can change.

    The ID number that they add/remove/change in the new id column is used in a vlookup on another sheet and fills several columns

    The SiteID TradeCode and New Call Centre ID are combined in the Duplicate? column.
    When there are more than one of the same combination the duplicates column will have a 1 in it which will trigger some conditional formatting to show the error.

    Similar story if the user adds a preference in #3 but misses #2 - using the Pref Gap coulmn. (Pref = preference order, so pref 1 is the first preferred)

    If they put the same supplier in for the same trade on the same site it is identified using the duplicate? column.
    If there are dulicates then a 1 is put in the duplicates

    If the supplier is inactive then it is struck through.
    If the ID is not found then an error appears.
    There are some other conditional formats to draw a line between sites and trades.

    Ultimately the sample data I have provided is a SMALL amount of data and every change you make takes several seconds to calculate. Doubling the data makes it very slow.
    Thing is - I proabaly want 15x the data in there! I'd really appreciate help on trying to optimise this.


    It doesn't seem to be the conditional formatting, really seems to be the formulas that are hurting it.

    I have hidden columns that are not relevant to the technical aspect of the project, a lot of the visible columns would not be visible to the user.
    Attached Files Attached Files

+ 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