Indirect & Arrays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matthew - 07 Feb 2008 19:35 GMT

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

Reply to this Message

Charles Williams - 07 Feb 2008 19:55 GMT

INDIRECT() only works with references, not formulae

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

> Am I being thick...
>
[quoted text clipped - 17 lines]
>
> Matthew

Reply to this Message

Tyro - 07 Feb 2008 21:23 GMT

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

> Am I being thick...
>
[quoted text clipped - 17 lines]
>
> Matthew

Reply to this Message

T. Valko - 07 Feb 2008 21:46 GMT

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

Signature

Biff
Microsoft Excel MVP

> Am I being thick...
>
[quoted text clipped - 17 lines]
>
> Matthew

Reply to this Message

Matthew - 12 Feb 2008 17:55 GMT

> >in B1 is the text contrived from a load of &..& statements
> >'Week 1'!$B$4:$B$102=59,'Week 1'!$C$4:$C$102
[quoted text clipped - 42 lines]
>
> > Matthew

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

Reply to this Message