How fix a too long array function

[KalleH]

Hello!

I have a matrix with categories in the columns (from A to AG) and I use
string reference (with INDIRECT) to access it in another sheet (same workbook
though).
It is a quite long array function expression in another sheet:
{=IF( ($A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY()
<=$A4));SUM( IF( ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20))) *
ISERROR(FIND("X2";INDIRECT('Matrix references'!$B$20))) *
ISERROR(FIND("X3";INDIRECT('Matrix references'!$B$20))) *
ISBLANK(INDIRECT('Matrix references'!$B$18)) * ISERROR(FIND('TR
goals'!$G$2;INDIRECT('Matrix references'!$B$8))) * ($A4-7 < INDIRECT('Matrix
references'!$B$9)) * (INDIRECT('Matrix references'!$B$9) <= $A4) *
(NOT(ISERROR(FIND('TR goals'!$C$2;INDIRECT('Matrix references'!$B$8)))) +
NOT(ISERROR(FIND('TR goals'!$C$1;INDIRECT('Matrix references'!$B$8))))); 1;
0) );NA())}

What is does is this: Filter out particular rows and sum the occurances/hits
in a cell. If no hit, put "N/A".

The problem: I can not extend the function, since Excel says is is too long.

How can this be fixed?

br

 

[Bernie Deitrick]

br,

Move all your INDIRECT functions to other cells.

For example, enter this in cell B20 on your current sheet:

=INDIRECT('Matrix references'!$B$20)

and replace INDIRECT('Matrix references'!$B$20) with B20 in your long formula.

Also, you could move this

=$A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4)

to a cell, say, A5, and use

=IF(A5;
as the start of your formula.

HTH,
Bernie
MS Excel MVP

 

[KalleH]

Ok, I will try that.

Do you know if it is possible to include the result from a cell with an
expression like ={ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20)))} in
another cell's array function expression. That too would minimize the
expression length I hope.

br
KalleH

 

[KalleH]

Hello!

I tried your solution, it did not work. No Excel error resulted though, but
incorrect result. I think the nested array function got different (using a
reference to a reference).

Br
kalleH

 

[Bernie Deitrick]

br/KalleH,

You could try putting this into cell B20:

=INDIRECT('Matrix references'!$B$20)

and using

INDIRECT(B20)

on place of

INDIRECT('Matrix references'!$B$20)

That would remove the sheet references at least, making the formula shorter.


The other approach would be to re-write your formula.... if you describe what your formula is
doing, there may be a better formula approach....


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

I'm sorry - one too many INDIRECTS - use

='Matrix references'!$B$20

in B20, and

INDIRECT(B20)

to remove the sheet references...

HTH,
Bernie
MS Excel MVP