SOLVED

calling a dynamic range by composing the name with text and cell.value

Copper Contributor

Hi there,

office Professional Plus 2019

I'am trying to call a defined dynamic range bij composing its name from text and cell.value

something like : = sum(indirect("Eigen_"& N43). 

 

The dynamic range I want to call is Eigen_2014. This range is defined by

 =Offset(INDIRECT("verbruik!A" & MATCH(2014;verbruik!$A:$A;0));0;5;12;1)

 

The formula = sum(indirect("Eigen_"& N43) causes an error #VERW! (Dutch)

 

 

8 Replies

@jscherp 

Could you please specify more concretely in which sheet and cell formula doesn't work

when I place this formula =ALS(INDIRECT("jaar"&$N43);SOM(INDIRECT("Eigen_"&N43))) in cell V43 on sheet Verbruik! I get FALSE as response

@jscherp 

Sorry, I see no one formula in V43 of mentioned sheet in your file. If to add in English locale

=IF(INDIRECT("jaar"&$N43),SUM(INDIRECT("Eigen_"&N43)))

it returns FALSE since period-array has no year 2014 included.

Please see in attached.

@SergeiBaklan

that's correct. Just click the 2014 box on the Grafieken sheet 

@jscherp 

Okay. An error is returned by INDIRECT() which doesn't support an array as the input. Workaround depend on which Excel platform/version you are.

Windows11 Pro 64 bit
Excel Professional Plus 2019
best response confirmed by jscherp (Copper Contributor)
Solution

@jscherp 

Okay, you have no dynamic arrays. If with VBA you may try something like using INDIRECT function with dynamic array formula syntax (Excel O365) - Stack Overflow, didn't test it. Or create helper ranges with OFFSET(), name them and use in INDIRECT.

@SergeiBaklan 

Ok I will try that. For now it's weekend :)

1 best response

Accepted Solutions
best response confirmed by jscherp (Copper Contributor)
Solution

@jscherp 

Okay, you have no dynamic arrays. If with VBA you may try something like using INDIRECT function with dynamic array formula syntax (Excel O365) - Stack Overflow, didn't test it. Or create helper ranges with OFFSET(), name them and use in INDIRECT.

View solution in original post