There must be an easier way... Indirect formulas are killing me!!!

[Barkworth]

Hello Everyone!

I hope you are all safe during this scary time.
Luckily I am still able to work entirely from home keeping me busy which I am very grateful for. What I am not grateful for is this nightmare of a task which I have taken on recently!

The Task
Create an excel based tracker where the appropriate executives and champions can input data on a regular basis for their respective areas of responsibility.

My Solution
I have created a tracker where users will input data on a template. They use a different tab for each week of the year in the format e.g. W19, W20 W21 ETC.
All the data they input is then extracted from each tab and chucked into a large table for `storage` on a separate tab. There are various bits of crucial information that is needed, so I have used various formula such as SUMIF, Rank.Eq, AVERAGE, MAXIF... Then, finally there is a dashboard page which pulls through the figures (from the data storage tab) depending on the week chosen by the user in a drop down menu. This allows the dashboard to be ‘active’ and let the executives go back and view the numbers from previous weeks.

The Problem
Everything works perfectly fine and smoothly, and I’m super happy with the outcome... the problem is the performance is absolutely atrocious. I have had to use around 700 indirect formulas which is what is chasing the performance issues. We can’t really overlook this issue as this is used daily and people can expect to be entering up to 30 rows of data (20 columns wide), so the small lag between entering data in each cell is a nightmare.

There must be a solution to this which will remove the need for the INDIRECTS.... or at least remove the need for all 700 of them!!

Examples of where I am using INDIRECT:

Data ‘Storage’ Tab:
=SUMIF(INDIRECT)”’”&$B5&”’!$A:$A”),D4,INDIRECT(“‘“&$B5&”’!$Z:$Z”)).
This formula adds all the number in Column Z (of the sheet definite by cell B5) if the value of Column A is equal the cell D4. - This is copied across the next 20 columns. This formula is also replicated but using other formulas like COUNTIF and MAXIF instead of the SUMIF. The Indirect here is taking the string of text in cell B5 and using this to find the sheet where the data is pulled from.

Dashboard Tab:
=INDEX(INDIRECT($L$3),1,MATCH(Y13,INDEX(INDIRECT($L$3),3,0),0))
In this example, this is an Index & Match where the formula is pulling through the value that corresponds to that in cell Y13, but the data range is specified by the text in cell L3 (I have used named ranges here). Cell L3 contains a drop down menu. Again, this formula is replicated to pull through different values in each occasion.

I have a feeling there is no way to bypass the INDIRECTS on the dashboard sheet, which should be fine as there is only around 50 on this sheet.

The INDIRECTS on the Data Storage Tab are only really there as I’m lazy and this seems the easiest way to do all the formulas. Of course, there are 52 weeks in a year and I am measuring ~25 different peoples performances across 3 different metrics...
So using INDIRECT rather than manually selecting the tab which holds the relevant data really isn’t an option, I don’t think anyone has the time for this!

I really appreciate any help anyone can get me here, to be honest I am i perfectly happy with this workbook, it looks clean and concise and does everything that asks, just runs a bit slow. This is fine for me (as I understand the hours put into it)... but when you’re an important director, you don’t have the time to wait for these things...

Thanks Again!
Barkworth

 

[jasonb75]

Perhaps you could consider setting your formulas to a more realistic range?

A1:A100 instead of A:A will make a difference.

If the data to match in the dashboard formula is sorted then using approx match instead of exact will help as well.

 

[offthelip]

What you could do to avoid the indirects is create an additional sheet which contains all of the calculations for all of the sheets, in separate columns, e.g. if you have 52 sheets ( one for each week)
Column A would be the sumif for week1 , Column B the sumif for week2 , etc. , in each columnm you can put the other calculations below the sumifs.
Then in the top row you have the sheet names for each week, Then on the original sheet you can do a straight index match to find the correct column to pick the values. This would eliminate all of the volatile functions and so the recalculation should be fast

 

[Barkworth]

What you could do to avoid the indirects is create an additional sheet which contains all of the calculations for all of the sheets, in separate columns, e.g. if you have 52 sheets ( one for each week)
Column A would be the sumif for week1 , Column B the sumif for week2 , etc. , in each columnm you can put the other calculations below the sumifs.
Then in the top row you have the sheet names for each week, Then on the original sheet you can do a straight index match to find the correct column to pick the values. This would eliminate all of the volatile functions and so the recalculation should be fast

Thanks for your feedback! Actually it so happens this is already what I’m doing. The different variables are labelled and in the Y-axis and the different measured metrics on the X-axis. My problem is that I would have to manually go in and select the range for each of the Index/Match formulas which pull the data from the relevant sheet. There appears to be about 7,000 of these...

 

[jasonb75]

There appears to be about 7,000 of these...

Minimising the size of the range that a formula refers to is the only way to improve performance on such things unless you can do away with the indirect references to reduce volatility, which given what you've said already, doesn't appear to be an option.

As an alternative, how about setting calculation to manual for data entry purposes?

 

[offthelip]

My problem is that I would have to manually go in and select the range for each of the Index/Match formulas which pull the data from the relevant sheet. There appears to be about 7,000 of these...

The way to do that is to write some VBA to WRITE the equations. I have certainly done that, since it is VBA that is only run once , it easy to develop because you keep going until you get it correct, and it doesn't matter how slow it is.