Function INDIRECT

[emilija]

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

 

[Bob Phillips]

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]

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]

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]

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]

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)

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"))

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)

nested
in

 

[Harlan Grove]

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")) ....

=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))=$B24);
INDIRECT("'[CC_RP1_06.xls]"&B11&"'!"&
MID(CELL("Address",($A$1,J$378:J$410)),6,20)))