Forum Discussion
Solved
calling a dynamic range by composing the name with text and cell.value
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)
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.
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
=IF(INDIRECT("jaar"&$N43),SUM(INDIRECT("Eigen_"&N43)))it returns FALSE since period-array has no year 2014 included.
Please see in attached.
