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

[jscherp]

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)

[#2]

@jscherp 

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

[#3]

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

[#4]

@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.

[#5]

@Sergei Baklan

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

[#6]

@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.

[#7]

Windows11 Pro 64 bit
Excel Professional Plus 2019

[#8 · Accepted 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.

[#9]

@Sergei Baklan 

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