|
Function INDIRECT
Can I use INDIRECT function nested with other function, for example with
SUMPRODUCT, like : SUMPRODUCT(INDIRECT(..);INDIRECT(..)) I have tried but it doesn't work for me, am I wrong in syntax, or.. please write me an example which I can copy in workbook and see how it works TX Emilija |
Function INDIRECT
Indeed you can
Simple example =SUMPRODUCT(--(INDIRECT(A1)="x")) where A1 holds B1:B20 for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... Can I use INDIRECT function nested with other function, for example with SUMPRODUCT, like : SUMPRODUCT(INDIRECT(..);INDIRECT(..)) I have tried but it doesn't work for me, am I wrong in syntax, or.. please write me an example which I can copy in workbook and see how it works TX Emilija |
Function INDIRECT
It follows my example of usage of SUMPRODUCT,
=SUMPRODUCT(--(FC!$C$278:$C$334='P&L_C'!$C121);--(FC!R$278:R$334)) what I would like is, area1 name (source) (FC!$C$278:$C$334) to be nested in INDERECT function, and if it is possible source to be some sheet in a different workbook TX Emilija "Bob Phillips" wrote in message ... Indeed you can Simple example =SUMPRODUCT(--(INDIRECT(A1)="x")) where A1 holds B1:B20 for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... Can I use INDIRECT function nested with other function, for example with SUMPRODUCT, like : SUMPRODUCT(INDIRECT(..);INDIRECT(..)) I have tried but it doesn't work for me, am I wrong in syntax, or.. please write me an example which I can copy in workbook and see how it works TX Emilija |
Function INDIRECT
What I shoed you is exactly that. Your problem may be trying to use INDIRECT
on a closed workbook. That just doesn't work. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... It follows my example of usage of SUMPRODUCT, =SUMPRODUCT(--(FC!$C$278:$C$334='P&L_C'!$C121);--(FC!R$278:R$334)) what I would like is, area1 name (source) (FC!$C$278:$C$334) to be nested in INDERECT function, and if it is possible source to be some sheet in a different workbook TX Emilija "Bob Phillips" wrote in message ... Indeed you can Simple example =SUMPRODUCT(--(INDIRECT(A1)="x")) where A1 holds B1:B20 for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... Can I use INDIRECT function nested with other function, for example with SUMPRODUCT, like : SUMPRODUCT(INDIRECT(..);INDIRECT(..)) I have tried but it doesn't work for me, am I wrong in syntax, or.. please write me an example which I can copy in workbook and see how it works TX Emilija |
Function INDIRECT
Again, it doesn't work, and the workbook IS open
it follows the whole formula hope you uncertain it, is it something wrong with the syntax or with the combination of the functions =SUMPRODUCT(--(INDIRECT("'[CC_RP1_06.xls]"&B11&"'!$A$378:$A$410=$B24"));INDIRECT("'[CC_RP1_06.xls]"&B11&"'!J$378:J$410")) "Bob Phillips" wrote in message ... What I shoed you is exactly that. Your problem may be trying to use INDIRECT on a closed workbook. That just doesn't work. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... It follows my example of usage of SUMPRODUCT, =SUMPRODUCT(--(FC!$C$278:$C$334='P&L_C'!$C121);--(FC!R$278:R$334)) what I would like is, area1 name (source) (FC!$C$278:$C$334) to be nested in INDERECT function, and if it is possible source to be some sheet in a different workbook TX Emilija "Bob Phillips" wrote in message ... Indeed you can Simple example =SUMPRODUCT(--(INDIRECT(A1)="x")) where A1 holds B1:B20 for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... Can I use INDIRECT function nested with other function, for example with SUMPRODUCT, like : SUMPRODUCT(INDIRECT(..);INDIRECT(..)) I have tried but it doesn't work for me, am I wrong in syntax, or.. please write me an example which I can copy in workbook and see how it works TX Emilija |
Function INDIRECT
It certainly is wrong
=SUMPRODUCT(--(INDIRECT("'[CC_RP1_06.xls]"&B11&"'!$A$378:$A$410")=$B24),INDI RECT("'[CC_RP1_06.xls]"&B11&"'!J$378:J$410")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... Again, it doesn't work, and the workbook IS open it follows the whole formula hope you uncertain it, is it something wrong with the syntax or with the combination of the functions =SUMPRODUCT(--(INDIRECT("'[CC_RP1_06.xls]"&B11&"'!$A$378:$A$410=$B24"));INDI RECT("'[CC_RP1_06.xls]"&B11&"'!J$378:J$410")) "Bob Phillips" wrote in message ... What I shoed you is exactly that. Your problem may be trying to use INDIRECT on a closed workbook. That just doesn't work. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... It follows my example of usage of SUMPRODUCT, =SUMPRODUCT(--(FC!$C$278:$C$334='P&L_C'!$C121);--(FC!R$278:R$334)) what I would like is, area1 name (source) (FC!$C$278:$C$334) to be nested in INDERECT function, and if it is possible source to be some sheet in a different workbook TX Emilija "Bob Phillips" wrote in message ... Indeed you can Simple example =SUMPRODUCT(--(INDIRECT(A1)="x")) where A1 holds B1:B20 for example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "emilija" wrote in message ... Can I use INDIRECT function nested with other function, for example with SUMPRODUCT, like : SUMPRODUCT(INDIRECT(..);INDIRECT(..)) I have tried but it doesn't work for me, am I wrong in syntax, or.. please write me an example which I can copy in workbook and see how it works TX Emilija |
Function INDIRECT
Bob Phillips wrote...
It certainly is wrong =SUMPRODUCT(--(INDIRECT("'[CC_RP1_06.xls]"&B11&"'!$A$378:$A$410")=$B24), INDIRECT("'[CC_RP1_06.xls]"&B11&"'!J$378:J$410")) .... "emilija" wrote in message .... =SUMPRODUCT(--(INDIRECT("'[CC_RP1_06.xls]"&B11&"'!$A$378:$A$410=$B24")); INDIRECT("'[CC_RP1_06.xls]"&B11&"'!J$378:J$410")) .... Two things. First, there's no need to use absolute addressing in textrefs. They won't change if copied/filled into other cells. Second, the OP is showing semicolon as argument separator rather than comma. So the formula could be rewritten as =SUMPRODUCT(--(INDIRECT("'[CC_RP1_06.xls]"&B11&"'!A378:A410")=$B24); INDIRECT("'[CC_RP1_06.xls]"&B11&"'!J378:J410")) However, I'd guess the full absolute and partial absolute references are there on purpose, so the formula may need to adapt when copied/filled. If so, =SUMPRODUCT(--(INDIRECT("'[CC_RP1_06.xls]"&B11&"'!"& MID(CELL("Address",($A$1,$A$378:$A$410)),6,20))=$B 24); INDIRECT("'[CC_RP1_06.xls]"&B11&"'!"& MID(CELL("Address",($A$1,J$378:J$410)),6,20))) |
| All times are GMT +1. The time now is 07:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com