How fix a too long array function
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" wrote:
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
|