Function INDIRECT

E

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
 
B

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

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
 
B

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

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

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)

emilija said:
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 said:
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
Click to expand...
Click to expand...
 
H

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"))
Click to expand...
Click to expand...
....

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