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" wrote in message
...
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