| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with whats Returned
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,SU MPRODUCT(--(Consol.SalesDate=E$2)*(Consol.Restaurants=1234)*R OW(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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with whats Returned
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with whats Returned
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+C onsol.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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with whats Returned
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with whats Returned
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problem with whats Returned
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem With Reference Update | Excel Worksheet Functions | |||
| Waiting for data to be returned from Microsoft Query | Excel Discussion (Misc queries) | |||
| Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
| problem office assistant | Excel Discussion (Misc queries) | |||
| Excel SQL.Request restricts data returned to array | Excel Worksheet Functions | |||
Linear Mode
