Condense Formula

[JoeExcelHelp]

Anyway of simplifying this.. More a lesson thn a necessity

=INDEX(Orientation!$B$7:$ZZ$68,MATCH($D$1,Orientation!$A:$A,0)-6,MATCH(AG204,Orientation!$B$1:$ZZ$1,0))+
INDEX(Orientation!$B$7:$ZZ$68,MATCH($D$1,Orientation!$A:$A,0)-6,MATCH(AG212,Orientation!$B$1:$ZZ$1,0))+
INDEX(Orientation!$B$7:$ZZ$68,MATCH($D$1,Orientation!$A:$A,0)-6,MATCH(AG208,Orientation!$B$1:$ZZ$1,0))

[Rudi]

I doubt this can be simplified, as it is doing a rather specific task. Even if one were to use SUMPRODUCT or other methods, it would be just as lengthy and possibly even more complex. But let me just add that Excel have proven me wrong time and time again.

[HansV]

I agree with Rudi, but you could do the following.
Each of the three INDEX formulas uses MATCH($D$1,Orientation!$A:$A,0)-6 as the row offset. You could place the formula

=MATCH($D$1,Orientation!$A:$A,0)-6

or the equivalent

=MATCH($D$1,Orientation!$A$7:$A$68,0)

in a separate cell, say in W1 on the same sheet as the cell with the formula. (You'd use an unoccupied cell for this, obviously). Your formula then becomes

=INDEX(Orientation!$B$7:$ZZ$68,$W$1,MATCH(AG204,Orientation!$B$1:$ZZ$1,0))+INDEX(Orientation!$B$7:$ZZ$68,$W$1,MATCH(AG212,Orientation!$B$1:$ZZ$1,0))+INDEX(Orientation!$B$7:$ZZ$68,$W$1,MATCH(AG208,Orientation!$B$1:$ZZ$1,0))

Something else you could do: name the range Orientation!$B$7:$ZZ$68 as for example MyData, and name Orientation!$B$1:$ZZ$1 as MyRowHeaders
You then get

=INDEX(MyData,$W$1,MATCH(AG204,MyRowHeaders,0))+INDEX(MyData,$W$1,MATCH(AG212,MyRowHeaders,0))+INDEX(MyData,$W$1,MATCH(AG208,MyRowHeaders,0))

[Rudi]

Ah, the named ranges method is a GREAT idea. I didn't think of that!

[snb]

or a UDF:

=F_snb($D$1;AG204;AG208;AG212;Orientation!A1:ZZ68)

Code: Select all

Function F_snb(y, x1, x2, x3, sn)
    sn = sn.Value
    For j = 1 To UBound(sn)
     If sn(j, 1) = y Then exit for
    Next

    For jj = 1 To UBound(sn, 2)
      If x1 = sn(1, jj) Or x2 = sn(1, jj) Or x3 = sn(1, jj) Then F_snb = F_snb + sn(j, jj)
    Next
End Function