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


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


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


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

Please see in attached.

@Sergei Baklan

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


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)


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.

@Sergei Baklan 

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