Hi
if you like, email me a sample file and I'll have a look at it :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Stephen POWELL" schrieb im
Newsbeitrag ...
Sorry, Frank. I can't get your suggestion to work. The succession
of FALSE
values evaluate to zero and are multiplied together to produce zero.
I even changed your
CombinedPL!$C$6:$O$6,0)+5)
to
CombinedPL!$C$6:$O$6,0)+2)
because JAN is in column C and so there are two columns (A & B) to
the left
of the range that need to be adjusted for when determining the COLUMN
reference.
It had seemed so logical and hopeful!!
Thanks anyway.
Stephen


"Frank Kabel" wrote:

Hi
with text values it would get complicated. To be honest: Better
change
your model once but to deal with too complicated formulas :-).
Saying
that you may try the following (though not tested):

=SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou
v

er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6
:$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500))

--
Regards
Frank Kabel
Frankfurt, Germany

"Stephen POWELL" schrieb
im
Newsbeitrag
...
Thank you, Frank. You're very resourceful.
Unfortunately, the models which are the source workbooks use text
and
not
numerics for the dates (too much work to change every model).
However, I
could probably:
a) modify your approach to identify DEC and subtract this from
the
twelve
month total; or
b) use the existing INDEX approach to do the same thing (12
months
minus DEC).
This would be a fix for now and I could re-consider the design of
the
source
workbooks for next year's version.
Thanks again.
Stephen