Volatile vs. Non-Volatile

[polarisking]

I'm attempting to use WorkbookName.xlsx!NamedRangeName to define a Series for a Dynamic chart and it's failing. Is it because of the INDIRECT statement embedded within the OFFSET statement? It works fine if I remove the INDIRECT and point directly to a cell reference. I'm thinking I'm making Excel traverse too many interpreted steps.

NamedRangeName = OFFSET(INDIRECT("A"&WorksheetName!$J$3),0,0,10,1)

 

[Haseeb A]

Hello Polaris,

Why do you need INDIRECT there? you simply can use,

=OFFSET(WorksheetName!$A$1,WorksheetName!$J$3-1,,10,1)

Or,

=OFFSET(INDEX(WorksheetName!$A:$A,WorksheetName!$J$3),,,10,1)

I always use INDEX, which only recalculate, whenever changes made in A:A or J3; or when opening workbook.

=INDEX(WorksheetName!$A:$A,WorksheetName!$J$3):INDEX(WorksheetName!$A:$A,WorksheetName!$J$3+9)

 

[NARAYANK991]

Hi ,

I am not sure what you are trying to do , and why ; the following link has an answer by Tushar Mehta which you may find informative.

http://www.pcreview.co.uk/forums/do-define-chart-series-indirect-reference-t2132182.html

Narayan

 

[polarisking]

Thanks to both of you for responding. My question may not have been clear, so I'll rephrase: Aside from whether or not INDIRECT is necessary in the OFFSET example I gave, why does Excel this named ranged when it's the underlying element of a Chart Series? Remove the INDIRECT with a direct cell reference (qualified with the workbook name, of course) and Excel likes it.

It's more of a theoretical question than practical.