Hi,
Been stuck on this all morning
I have a large data table with approximately 36000 cells. The cells have conditional formatting rules based on the following formulas:
The formatting works perfectly, however, excel recalculates the conditional formatting formulas on scrolling the window which causes horrendous lag. I've got calculation set to manual but this doesn't seem to prevent conditional formatting from updating.
Please Login or Register to view this content.
Any ideas?
Hi,
You can turn it off through VBA or via the properties window in the VB Editor by setting the sheet's EnableFormatConditionsCalculation property to False. You might also use a control cell to bypass the VLOOKUP formulas when you don't want them calculated.
Don
Please remember to mark your thread 'Solved' when appropriate.
Hi,Originally Posted by xlnitwit
Thanks for the reply. Unfortunately setting the sheet's EnableFormatConditionsCalculation property to False doesn't appear to work. The screen is still rendering when scrolling. Could you explain your second suggestion please?
I've tried these to no avail too.
This hangs for over 5 minutes before I end the precess in task managerPlease Login or Register to view this content.
This completes (eventually) but wipes all formats, leaving the cells devoid of all formatPlease Login or Register to view this content.
Last edited by radddogg; 12-20-2016 at 10:34 AM.
My second suggestion was to put a control value in a cell and use that to control calculation of the conditional format formula. Thus for example you might enter 1 in cell IV1 and alter your formulas to
=IF($Z$1=1,VLOOKUP($I7&";"&M$3&";"&$J7,Demand!$J:$P,7,0)="In Stock",FALSE)
=IF($Z$1=1,VLOOKUP($I7&";"&M$3&";"&$J7,Demand!$J:$P,7,0)="On Order",FALSE)
=IF($Z$1=1,VLOOKUP($I7&";"&M$3&";"&$J7,Demand!$J:$P,7,0)="Overdue",FALSE)
=IF($Z$1=1,VLOOKUP($I7&";"&M$3&";"&$J7,Demand!$J:$P,7,0)="Query",FALSE)
Then, when you wish to 'disable' the conditional formatting, all you need to do is clear cell Z1. The CF will in fact still be calculating but the calculation should be very quick.
Removing the 1 removes the formats
Some background that might help identify another solution. The cells are filled using an almost identical vlookup but the format must by set by a different cell (two columns over). I could format the original lookup cell but is there a way to pull through the format as well as the value?
Or a way to replace the cell colour with the CF colour?
Of course. You cannot have conditional formatting without running the CF formulas. If you do not require the formats to be dynamic, you might use some code to colour the cells as required and then delete the CF from them.
If I may interject some thoughts here:
It can be difficult to debug/debottleneck something like this without seeing the actual spreadsheet. Can you remove any confidential information and then upload the sheet to the forum so we can look at it?
The first two things I would probably try:
1) The first bottleneck I see in those formulas is you are using an inefficient linear search (4th argument=FALSE/0). Linear searches on large data sets in Excel are slow. I would probably look at the lookup table/database to see if it could be sorted and/or restructured to take advantage of the much faster binary search (4th argument=TRUE/1).
2) Is it absolutely necessary to perform the lookup as part of the conditional formatting formulas? Recognizing that conditional formatting plays by different calculation/recalculation rules than cell formulas, I would try putting the expensive lookup function in an adjacent spreadsheet cell, then have the conditional formatting formula look at that cell (=lookupcell="In stock"). That would put the expensive lookup formula in the spreadsheet where I can control its calculation with manual calculation and/or the calculation fits properly within the dependency tree. The conditional formatting formulas are very simple text comparisons which should allow the conditional formatting part of the calculation to perform much quicker.
Hi MrShorty,
Unfortunately, it would take hours to cleanse the workbook to remove all the confidential information. If we can't solve this then I will recreate a much smaller version but even this will be quite time consuming.
1) The lookup column is already sorted in ascending order but using a binary search returns incorrect results. Perhaps I'm doing something wrong.
2) This might be the answer but I can't think how to implement it. Perhaps if I give more info you can be more explicit with your solution?
Here goes. As you probably guessed I am working on inventory management. Each cell is fed a status or forecast date from the lookup data. These are "Clear" = requirement met, "In Stock", "Query" = no corresponding order, "DD/MM/YYYY" = forecast date. My conditional formatting was originally based on the cell value. Clear = grey, In Stock = green, Query = yellow, date < today() = red, other dates = orange
This all worked fine. The issue is regarding the forecast dates. Not all 'overdue' forecast dates need to be red. There is another date which is the requirement date. This is the date that should drive the red cell status. I use a helper column on the data table to provide the forecast status and use the CF formula looking up in column P to drive the cell colour while the actual forecast date/status comes from column N.
I hope this makes sense. If not I'll have to compile a sample sheet.
Cheers
I am having a hard time visualizing a scenario where, given a sorted list, the exact match linear search returns a different result from the binary search algorithm. I cannot think what would cause that kind of behavior. Probably would need an example to understand what is causing that kind of behavior.1) The lookup column is already sorted in ascending order but using a binary search returns incorrect results. Perhaps I'm doing something wrong.
You indicate that the existing lookup formulas work exactly as expected. I would have thought that moving the =VLOOKUP($I7&";"&M$3&";"&$J7,Demand!$J:$P,7,0) part of the conditional formatting formula into a helper cell would have been adequate, but I am not immediately seeing how this formula fulfills the different requirements your explanation calls for. Assuming that this formula works perfectly as you indicate, then I would expect to just enter this formula into the helper cells. It probably makes more sense in context.2) This might be the answer but I can't think how to implement it.
1) does the lookup value list need to be sorted too? If so that will be the cause
2) ok after sleeping on it, I think I know what I need to do here. Replicate the conditional format table in another sheet then use a simple cell link to this table in the CF formula. Will try just now and report back.
Thanks
It works! Thanks for the help +rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks