Re: INDIRECT as part of array

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"xrbbaker" wrote:
> 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
>