Forum Discussion

Copper Contributor

Jan 21, 2023

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

Energieverbruik V2 (4).xlsm452 KB

excel

Formulas and Functions

SergeiBaklan
Jan 21, 2023
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

Diamond Contributor

Jan 21, 2023

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.

Energieverbruik V2 (4) (1).xlsm454 KB

jscherp

Copper Contributor

Jan 21, 2023

SergeiBaklan

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

SergeiBaklan
Diamond Contributor
Jan 21, 2023
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.
- jscherp
  Copper Contributor
  Jan 21, 2023
  Windows11 Pro 64 bit
  Excel Professional Plus 2019
  - SergeiBaklan
    Diamond Contributor
    Jan 21, 2023
    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.