Forum Discussion
jscherp
Jan 21, 2023Copper Contributor
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...
- Jan 21, 2023
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.
jscherp
Jan 21, 2023Copper Contributor
when I place this formula =ALS(INDIRECT("jaar"&$N43);SOM(INDIRECT("Eigen_"&N43))) in cell V43 on sheet Verbruik! I get FALSE as response
SergeiBaklan
Jan 21, 2023MVP
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.
- jscherpJan 21, 2023Copper Contributor
that's correct. Just click the 2014 box on the Grafieken sheet
- SergeiBaklanJan 21, 2023MVP
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.
- jscherpJan 21, 2023Copper ContributorWindows11 Pro 64 bit
Excel Professional Plus 2019