|
SUM range from another sheet
Hi All..........
I am trying to make a formula that will return the SUM of the range O:R of the row in which is found the value in column A.......ie =SUM("O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) The below formula correctly returns text of the range of interest...... ="O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) but when I try to get the SUM of that range, all I get is #VALUE! Any help would be appreciated........ Vaya con Dios, Chuck, CABGx3 |
SUM range from another sheet
Chuck,
Surely you need to INDIRECT it? =SUM(INDIRECT("O"&MATCH(A12,'All WO''s'!$B:B,0)&":R"&MATCH(A12,'All WO''s'!$B:B,0))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CLR" wrote in message ... Hi All.......... I am trying to make a formula that will return the SUM of the range O:R of the row in which is found the value in column A.......ie =SUM("O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) The below formula correctly returns text of the range of interest...... ="O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) but when I try to get the SUM of that range, all I get is #VALUE! Any help would be appreciated........ Vaya con Dios, Chuck, CABGx3 |
SUM range from another sheet
Thanks Bob........I had tried that also but only got 0 as a return............
This below works, but I was shooting for something smoother....... =IF(A12="","",VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,14,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,15,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,16,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,17,FALSE)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Chuck, Surely you need to INDIRECT it? =SUM(INDIRECT("O"&MATCH(A12,'All WO''s'!$B:B,0)&":R"&MATCH(A12,'All WO''s'!$B:B,0))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CLR" wrote in message ... Hi All.......... I am trying to make a formula that will return the SUM of the range O:R of the row in which is found the value in column A.......ie =SUM("O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) The below formula correctly returns text of the range of interest...... ="O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) but when I try to get the SUM of that range, all I get is #VALUE! Any help would be appreciated........ Vaya con Dios, Chuck, CABGx3 |
SUM range from another sheet
Chuck, how about this
=SUM(N(OFFSET(B8,MATCH(A12,'All WO''s'!$B$8:$B$65000,0)-1,{13,14,15,16}))) as it is OFFSET, all the columns are reduced by 1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CLR" wrote in message ... Thanks Bob........I had tried that also but only got 0 as a return............ This below works, but I was shooting for something smoother....... =IF(A12="","",VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,14,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,15,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,16,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,17,FALSE)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Chuck, Surely you need to INDIRECT it? =SUM(INDIRECT("O"&MATCH(A12,'All WO''s'!$B:B,0)&":R"&MATCH(A12,'All WO''s'!$B:B,0))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CLR" wrote in message ... Hi All.......... I am trying to make a formula that will return the SUM of the range O:R of the row in which is found the value in column A.......ie =SUM("O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) The below formula correctly returns text of the range of interest...... ="O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) but when I try to get the SUM of that range, all I get is #VALUE! Any help would be appreciated........ Vaya con Dios, Chuck, CABGx3 |
SUM range from another sheet
Nope, same thing....returns only 0...........Besides, copying it down would
only step down column B.........I tried modifying it but to no avail. Thanks anyway, Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Chuck, how about this =SUM(N(OFFSET(B8,MATCH(A12,'All WO''s'!$B$8:$B$65000,0)-1,{13,14,15,16}))) as it is OFFSET, all the columns are reduced by 1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CLR" wrote in message ... Thanks Bob........I had tried that also but only got 0 as a return............ This below works, but I was shooting for something smoother....... =IF(A12="","",VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,14,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,15,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,16,FALSE)+VLOOKUP(A12,'All WO''s'!$B$8:$R$65000,17,FALSE)) Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: Chuck, Surely you need to INDIRECT it? =SUM(INDIRECT("O"&MATCH(A12,'All WO''s'!$B:B,0)&":R"&MATCH(A12,'All WO''s'!$B:B,0))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CLR" wrote in message ... Hi All.......... I am trying to make a formula that will return the SUM of the range O:R of the row in which is found the value in column A.......ie =SUM("O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) The below formula correctly returns text of the range of interest...... ="O"&MATCH(A12,'All WO''s'!$B:B)&":R"&MATCH(A12,'All WO''s'!$B:B)) but when I try to get the SUM of that range, all I get is #VALUE! Any help would be appreciated........ Vaya con Dios, Chuck, CABGx3 |
| All times are GMT +1. The time now is 05:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com