Problem with whats Returned

[John]

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)*ROW(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

 

[Bob Phillips]

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]

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]

What is the definition of Consol.Pay1 and 2 etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[John]

Bob

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

 

[Bob Phillips]

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)