Indirect & Arrays

[Matthew]

Am I being thick...

I can not get an array formula to work with an indirect command.

I may have made a typo but I am now starting to convince my self it
doesn't work.

E.g.
in B1 is the text contrived from a load of &..& statements

'Week 1'!$B$4:$B$102=59,'Week 1'!$C$4:$C$102

In C1 is

{=SUM(IF(INDIRECT(B1),0))}

The simple outcome was if the b4:b104 = 59 then sum c4:c104

Help please

Matthew

 

[Charles Williams]

INDIRECT() only works with references, not formulae

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Tyro]

B1 has to contain a valid address. It does not.
For example in B1 I have the value C1. In Cell C1 there is a 6. in D1 I put:
IF(INDIRECT(B1)<7,1,2) The formula returns 1.

Tyro

 

[T. Valko]

in B1 is the text contrived from a load of &..& statements

'Week 1'!$B$4:$B$102=59,'Week 1'!$C$4:$C$102

Depending on how the above is constructed, it'll work *if* you split it up
into 2 cells:

B1: 'Week 1'!$B$4:$B$102
C1: 'Week 1'!$C$4:$C$102

Then:

=SUMIF(INDIRECT(B1),59,INDIRECT(C1)

Or:

B1: 'Week 1'!$B$4:$B$102
C1: 'Week 1'!$C$4:$C$102
D1: 59

=SUMIF(INDIRECT(B1),D1,INDIRECT(C1)

 

[Matthew]

Depending on how the above is constructed, it'll work *if* you split it up
into 2 cells:

B1: 'Week 1'!$B$4:$B$102
C1: 'Week 1'!$C$4:$C$102

Then:

=SUMIF(INDIRECT(B1),59,INDIRECT(C1)

Or:

B1: 'Week 1'!$B$4:$B$102
C1: 'Week 1'!$C$4:$C$102
D1: 59

=SUMIF(INDIRECT(B1),D1,INDIRECT(C1)

Thank you to all I knew I was having a bad day!