Improve performance

Hi guys,

I have an excel with 12 sheets and I have some characteristic:

-I have a sheet for filters, where I am using
-IFERROR
-INDEX
-MATCH
-COUNTIF
-NAME MANAGER
this is an example: ='SheetName'!$A$2:INDEX('SheetName'!$A$2:$A$100000,COUNTA('SheetName'!$A$2:$A$100000))

-I am using data validation as a drop-down list

-I have some formulas to show values like : =IF(OR(ISBLANK($M$5),$M$5="ALL"),'Main1'!B2,IF(OR(ISBLANK($S$5),$S$5="ALL"),'Main2'!B2,'Main3'!B2))

-I have formulas to blank cells because I don't know until cell the user will put values. I found some tricks and this is not recommended but I don't know how to do this dynamically

=IFERROR(INDEX($B$2:$B$1000,$E374,COLUMNS($F$1:F374)),"")

For example, at this moment is not necessary that the formulas go until row number 5000 because my data go until row number 61, how can I do this dynamically?

So please, help me, when I do some filters my excel sheet spends a lot of time to calculate the formulas and show the result.

What can do I? minimize the quantity sheets? change the formulas?

thank you.

Hi Twister, this following formula should return the row number of the last non-blank cell in a column. Maybe you could use this to replace your 5000 etc.

Please Login or Register  to view this content.

It's difficult to make specific suggestions without understanding exactly how it all works together. A couple of common things to look at:

1) In your list of functions that you are using, the main "slow" function is MATCH(). A filtering operation is going to require many lookup type functions, so you will want to make sure your lookup steps are well designed.
1a) Spend some time with your database and your lookup function and see if it can be structured to use one of the approximate match options rather than the exact match option. The binary search algorithms used in the approximate match functions are much faster than the linear search algorithm in the exact match algorithm.
1b) Watch out for duplicated effort. Most of the time, we nest the MATCH() function inside of the INDEX() function, which often leads to performing the exact same slow lookup multiple times (find where "John" is in "names" column). We have seen significant performance improvements by moving the MATCH() functions into their own helper column(s) so that we minimize the number of lookups that are required.
2) Watch out for duplicated effort with your counting functions. For example, every copy of this function ='SheetName'!$A$2:INDEX('SheetName'!$A$2:$A$100000,COUNTA('SheetName'!$A$2:$A$100000))
is counting how many entries there are in column A of SheetName. Again, move the counting function into a helper cell so that Excel need only count the number of entries once.

The formula in post #5, =IF(ISNUMBER(SEARCH(Main!$G$5,A2:Last_row_Table_1)),C2,""), would seem to indicate that a value is being searched for in a column of an Excel table. If that is the case then the formula should adjust as the table is expanded vertically. Would seem the formula could read: =IF(ISNUMBER(SEARCH(Main!$G$5,A2:A61)),C2,"") and then when the table is expanded to include A62 the formula should adjust accordingly.
As MrShorty states, it would be easier to help if we could see how this works. A good way to do that is to utilize the instructions in the banner at the top of the page to upload a sample workbook.
Let us know if you have any questions.