INDIRECT as part of array

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

That's got it Max! thanks

"Max" wrote:

> > =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
> >

Welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"xrbbaker" <xrbbaker@discussions.microsoft.com> wrote in message
news:46A6D0A1-1933-416C-B097-2C900C3217C5@microsoft.com...
> That's got it Max! thanks