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