Hi everyone,

I tried many times to use the formula such as 

=AVERAGE(INDIRECT(A1))
Here, A1 is the name of a dynamic named range: 

=OFFSET(Data!$A$1,0,0,1000,1)
But this always resulted in the #REF!.

Do you have similar experience like this? Apparently the INDIRECT() function cannot be used in dynamic named ranges.

Is there a way to work around it? For my case, the dynamic named range is useful to me.