It's trying to add the headings, which is what gives the #VALUE. If you
offset past the headings, problem bypassed.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
Bob

Definition of ConsolPay1 is =OFFSET(Consol.SalesDate,0,4) ConsolPay2 is =
OFFSET(Consol.SalesDate,0,5) etc



"Bob Phillips" wrote in message
...
What is the definition of Consol.Pay1 and 2 etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
Thanks again Bob

I thought it was that and had changed to your first suggestion, but
that
produced a #Value in another cell that has the following formula (it
returned the correct value when I left the Range Name for
Consol.SalesDate
at =OFFSET(Consol!$B$1,1,0,COUNTA(Consol!$B:$B),1)



=SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Restaurants=1234),( (Consol.Pay1
+Consol.Pay2+Consol.Pay3+Consol.Pay4+Consol.Pay6)) )

As far as I can determine, its because Consol.Pay1 = 0, but not sure
why
it
returns a #value as Zero is the correct value in Consol.Pay1 (I
eliminated
from the formula Consol.Pay1 and I get the correct answer expected)

Your second suggest does work for me Bob

Thanks




"Bob Phillips" wrote in message
...
John,

The reason is that your range names are all defined to start at row 2
(you
offset the row on Consol.SalesDate by 1 from B1, thereby starting at
2).
However. by using the row multiplier in SP, you use the actual row
number,
not that number offset within the defined name range. So row 3 is the
2nd
row in the defined name range, and when you use that value to index
in,
you
go one row too many.

Either adjust Consol.SalesDat to

=OFFSET(Consol!$B$1,0,0,COUNTA(Consol!$B:$B),1)

or use



=IF(SUMPRODUCT(--(Consol.SalesDate=E$2)*(Consol.Restaurants=1234)*( ROW(Conso


l.Comments)-1))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=E$
2)*(Consol.Restaurants=1234)*(ROW(Consol.Comments)-1))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
I have the following formula which returns the value +1 Row from the
cell
I'm expecting the value to be Returned from




=IF(SUMPRODUCT(--(Consol.SalesDate=E$2)*(Consol.Restaurants=1234)*R OW(Consol


..Comments))=0,"",INDEX(Consol.Comments,SUMPRODUCT (--(Consol.SalesDate=E$2)*(
Consol.Restaurants=1234)*ROW(Consol.Comments))))

The "Consol.Comment" I'm expecting to be returned is what resides in
Cell
J2, however it returns what is in J3 and from what I can see, it is
always
out +1 Row for all my expected cells

All my range names are dynamic and feed off "Consol.SalesDate" which
is
ranged as below

=OFFSET(Consol!$B$1,1,0,COUNTA(Consol!$B:$B),1). As an example the
Range
name setting for "Consol.Comments" is =OFFSET(Consol.SalesDate,0,8)

Note all my data starts in Row2, I've Headers in Row1

Am I doing something wrong?


Thanks