# Array formula summing with indirect parameter

L

#### Legacy 113902

##### Guest
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

Code:
``{=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.

Code:
``{=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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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))

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!

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.

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!

Replies
1
Views
130
Replies
2
Views
538
Replies
18
Views
1K
Replies
10
Views
1K
Replies
6
Views
471

1,203,739
Messages
6,057,081
Members
444,904
Latest member
SelamT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back