Array formula summing with indirect parameter

[Sil68]

Hi,

I've got several workbooks containing several pages each, one of them being my parameter sheet.

I'd like to get an array formula using an indirect parameter as the actual array working.

What I've tried so far is

Please Login or Register  to view this content.

With Parameter!$G$2 containing either of the following:

• '{124;125;126;127;128;129;130;131;132;133;134;135}
• {124;125;126;127;128;129;130;131;132;133;134;135}
• 124;125;126;127;128;129;130;131;132;133;134;135

Unfortunately always to no avail.

When using the the array in the formula directly (shown below), everything is working perfectly fine.

Please Login or Register  to view this content.

As there are many occurances of such a array formula, I'd rather refrain from having them edit individually whenever the contents of the array changes.

Much better, and my top preference would be just changing the corresponding cell (Parameter!$G$2) accordingly, refreshing my workbook, and that's it.

-- Sil68

[NBVC]

What exactly is in Parameter!E2?

[DonkeyOte]

If the columns to be summed are always contiguous by nature I'd be inclined to store first column reference in one cell (Parameter!G2) and last column in another cell (Parameter!H2) then:

Please Login or Register  to view this content.

There will be / are other methods available...

[NBVC]

I also wondering if this might be possible with just a SUMIF() instead of {SUM(VLOOKUP())} ?

[Sil68]

Parameter!!$E$2 contains the data range I'm skipping through (in one of my workbooks it contains: 'Model!$D$9:$FP$24).

Btw. Parameter!!$E$2 holds a given name (eg. Development) I'm looking for, or to be more precise, for which I want to calculate the sum.

[Sil68]

If the columns to be summed are always contiguous by nature I'd be inclined to store first column reference in one cell (Parameter!G2) and last column in another cell (Parameter!H2) then:

Please Login or Register  to view this content.

There will be / are other methods available...

Cool! This is working really great!