Array formula summing with indirect parameter

[Legacy 113902]

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

{=SUM(VLOOKUP(Parameter!$C$24;INDIRECT(Parameter!$E$2);INDIRECT(Parameter!$G$2);FALSE))}

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.

{=SUM(VLOOKUP(Parameter!$C$24;INDIRECT(Parameter!$E$2);{124;125;126;127;128;129;130;131;132;133;134;135};FALSE))}

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

 

[Andrew Poulsom]

Two possibilites:

1. Insert a name, eg Array, that refers to =EVALUATE(Parameter!$G$2), where G2 contains:

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

Then try the formula:

=SUM(VLOOKUP(Parameter!$C$24;INDIRECT(Parameter!$E$2);Array;FALSE))

2. Use 2 cells for the lower and upper bounds of the array:

=SUM(VLOOKUP(Parameter!$C$24;INDIRECT(Parameter!$E$2);ROW(INDIRECT(Parameter!$G$2&":"&Parameter!$H$2));FALSE))

 

[Legacy 113902]

Cool!

Approach #2 is pretty easy & straight forward!

What would be the "EVALUATE" formula? In Excel (2007) help I cannot find any reference to this! How would I use "EVALUATE"?

Thanks a lot!

 

[Andrew Poulsom]

EVALUATE is an Excel 4.0 Macro Function. It evaluates a formula or expression that is in the form of text and returns the result. It can be used when defining a name, but it can't be used on a worksheet.

 

[Legacy 113902]

EVALUATE is an Excel 4.0 Macro Function. It evaluates a formula or expression that is in the form of text and returns the result. It can be used when defining a name, but it can't be used on a worksheet.

Cool again!

Things can really be easy, if you'd only know how to tackle them properly!