INDIRECT as part of array

[xrbbaker]

My nemesis!

I'm trying to "productionize" some reports so that all the user need do is
enter (into cell E5) which row (on sheet 'Management Summary') is where the
current data is.

c$13 = a particular manager's name
'Management Summary'!$D$2:$DD$2=row where manager's names are populated
e5=40 (the row where the current data to be summed is)

=SUM(IF('Management Summary'!$D$2:$DD$2=C$13,INDIRECT("'Management
Summary'"&"!$A"&E5&":$DD"&E5,0)))

I know I'm close. This works:
=INDIRECT("'Management Summary'"&"!$A"&E5)
so my basic understanding of INDIRECT and concatanation is pretty good.
However when I try the full formula using INDIRECT in the middle of the
array, I get a #REF.

Does XL maybe have a cow if you use INDIRECT in an array like this? More
likely my syntax is no good. I'd appreciate another pair of eyes.

Thanks for the help.

Russ

 

[xrbbaker]

That's got it Max! thanks

Try this revision which uses the simpler SUMIF (normal ENTER will do):
=SUMIF('Management Summary'!$D$2:$DD$2,C$13,INDIRECT("'Management
Summary"&"'!$D"&E5&":$DD"&E5))

 

[Max]

Welcome ..