INDIRECT / OFFSET in arrays?

[yytsunamiyy]

This thread has trigegred a question regarding INDIRECT / OFFSET with me.

The OP is summing a non-continous range:

'[Master rota Term 6 2011.xls]Shift Requirements'!$C$5+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$8+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$11+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$14+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$17+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$20+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$23+'[Master rota Term 6 2011.xls]Shift Requirements'!$C$29:$C$30

I was wondering if it is possible to build an array for summing that non-continous range without having to repeat '[Master rota Term 6 2011.xls]Shift Requirements'! for each cell in the range.

I was thinking along the lines of

=SUM (or sumprodukt) (INDIRECT("'[Master rota Term 6 2011.xls]Shift Requirements'!$C$"&{ROW(A5),ROW(A8),ROW(A11),ROW(A14),ROW(A17),ROW(A20),ROW(A23),ROW(A29),ROW(A30)})

or alternativly:

=SUM (or sumprodukt) (OFFSET('[Master rota Term 6 2011.xls]Shift Requirements'!$C$5,{0,3,6,9,12,15,18,24,25},{0,0,0,0,0,0,0,0,0},{1,1,1,1,1,1,1,1,1},{1,1,1,1,1,1,1,1,1})

Neither of which i got to work - not even as an array (CSE) formula. Why not and is there a way to do this?

 

[Aladin Akyurek]

This thread has trigegred a question regarding INDIRECT / OFFSET with me.

The OP is summing a non-continous range:

'[Master rota Term 6 2011.xls]Shift Requirements'!$C$5+
'[Master rota Term 6 2011.xls]Shift Requirements'!$C$8+
'[Master rota Term 6 2011.xls]Shift Requirements'!$C$11+
'[Master rota Term 6 2011.xls]Shift Requirements'!$C$14+
'[Master rota Term 6 2011.xls]Shift Requirements'!$C$17+
'[Master rota Term 6 2011.xls]Shift Requirements'!$C$20+
'[Master rota Term 6 2011.xls]Shift Requirements'!$C$23+
'[Master rota Term 6 2011.xls]Shift Requirements'!$C$29:$C$30

I was wondering if it is possible to build an array for summing that non-continous range without having to repeat '[Master rota Term 6 2011.xls]Shift Requirements'! for each cell in the range.

I was thinking along the lines of

=SUM (or sumprodukt) 
(INDIRECT("'[Master rota Term 6 2011.xls]Shift Requirements'!$C$"&
{ROW(A5),ROW(A8),ROW(A11),ROW(A14),ROW(A17),ROW(A20),
ROW(A23),ROW(A29),ROW(A30)})

or alternativly:

=SUM (or sumprodukt) 
(OFFSET('[Master rota Term 6 2011.xls]Shift Requirements'!$C$5,{0,3,6,9,12,15,18,24,25},
{0,0,0,0,0,0,0,0,0},
{1,1,1,1,1,1,1,1,1},{1,1,1,1,1,1,1,1,1})

Neither of which i got to work - not even as an array (CSE) formula. Why not and is there a way to do this?

How about?...

=SUM(
  SUMPRODUCT(--(MOD(ROW(X!$C$5:$C$23)-ROW(X!$C$5),3)=0,X!$C$5:$C$23),
   X!$C$29:$C$30)

where X should replaced with:

'[Master rota Term 6 2011.xls]Shift Requirements'

 

[yytsunamiyy]

Interesting solution Aladin. I take it that INDIRECT / OFFSET don't accept arrays then?

 

[Aladin Akyurek]

Interesting solution Aladin. I take it that INDIRECT / OFFSET don't accept arrays then?

They would be a pain with closed books. Moreover, specifying a set of cells for INDIRECT does not follow a generic pattern. For example,

=SUM(N(INDIRECT({"A2","C5","F2"})))

requires us to spell out in hard-code what is needed.

 

[yytsunamiyy]

I see. So, apart from the closed wb problem of INDIRECT,

=SUM(N(INDIRECT({"X!A2","X!C5","X!F2"})))

would work, but would

=SUM(N(INDIRECT("X!"&{"A2","C5","F2"})))

work too?

(can't test myself at the mo, only open office here )

 

[Aladin Akyurek]

I see. So, apart from the closed wb problem of INDIRECT,

=SUM(N(INDIRECT({"X!A2","X!C5","X!F2"})))

would work, but would

=SUM(N(INDIRECT("X!"&{"A2","C5","F2"})))

work too?

Yes. No doubt about it.

(can't test myself at the mo, only open office here )

That's too bad.

 

[yytsunamiyy]

Thanks aladin.

 

[Aladin Akyurek]

Thanks aladin.

You are welcome. I appreciate the feedback.