Function to streamline the use of indirect formula

[LordTim]

Good morning all,
I have a large spreadsheet which uses daily tabs to populate the data. Column A has the lookup reference and the Indirect formula uses the column headers which are dates to reference the correct tab. I would like to use a function to replace the indirect formula due to the number of records and lookups. Here is the indirect formula being used:

=IF(SUMIF(INDIRECT("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!"&"$A:$A"),$A7,INDIRECT("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!"&"$E:$E"))=0,"",SUMIF(INDIRECT("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!"&"$A:$A"),$A7,INDIRECT("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!"&"$E:$E")))

You can imagine how long it takes to calc with over 2k records and up to 31 columns. I will be rewriting the entire spreadsheet in the future but I need to get their old one functional again.

Thanks in advance.

 

[Roger Govier]

Hi

Why not just use File > Options > Advanced > Display options for this worksheet and uncheck "show a zero in cell that have zero value"
That eliminates half of the number of calls to Indirect just to replace a 0 result with a null
Then it becomes
=SUMIF(INDIRECT("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!"$A:$A"),$A7,
INDIRECT("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!$E:$E"))

You could also try to use the old XLM4 macro function EVALUATE() in place of INDIRECT()
I cannot test it without setting up a file in the same manner as yours which I do not have time to do, so test it on a copy file first.

It has the same effect, but is non volatile (unlike Indirect) so should speed things up enormously.

You cannot use in directly in a cell only in a named formula, so place your cursor in cell Q6 and create the named formula as something like
Name result
formula =SUMIF(EVALUATE("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!"$A:$A"),$A7,EVALUATE("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!$E:$E"))

Then replace your existing formula on the sheet with =result
You would need to save your file as a .xlsm or a .xlsb file for this to work.

Since it is just column Q that is varying as you copy across the sheet, by creating the first formula in Q it should adjust accordingly.

 

[LordTim]

Thanks for the reply.

I tried the Evaluate formula and Excel does not like it. I get an invalid function error message.

The zero suppression was put in for my readability and also as a placeholder for error checking. I do like the idea of changing the settings and will definitely see if this will work for the users. I will still need to add some error checking since I know someone will forget to add the new data tab or will type over a formula. Trying not to spend too much time on something that is not designed very well when I know I have to recreate it from the scratch.

The goal is to set the spreadsheet up so the users can add their tabs daily and have it auto populate or notify them when there is data missing but getting around referencing the data with the header dates is the challenge.

 

[salasv]

You could also try to use the old XLM4 macro function EVALUATE() in place of INDIRECT()
...
It has the same effect, but is non volatile (unlike Indirect) so should speed things up enormously.

You cannot use in directly in a cell only in a named formula, so place your cursor in cell Q6 and create the named formula as something like
Name result
formula =SUMIF(EVALUATE("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!"$A:$A"),$A7,EVALUATE("'AXTransferRecap "&TEXT(MONTH(Q$6),0)&"-"&TEXT(DAY(Q$6),0)&"'!$E:$E"))

Then replace your existing formula on the sheet with =result

(I hope this follow-up question is okay a couple months later. If not, I'll start a new thread.)

Thank you Mr. Govier! I was looking for a solution like this and was able to apply it to my file. My situation is similar to the original poster, but in my case, both the columns and rows vary. You were right about being able to copy the formula across the table; but, as you probably expect, up & down doesn't work. Do I need separate named ranges for each row or is there a more efficient way to do this?

The formulas are not all the same either, with some referencing the total row of a table and others referencing specific cells. I'd have somewhere in the vicinity of 50 named ranges to account for all the different references. I'm worried I'd end in the same slow performance situation with so many named ranges. I have a lot now as it is. Appreciate any feedback.