Cell based Series

[sungen99]

Is it possible to rewrite this formula:

Please Login or Register  to view this content.

So that the $A$476 instances are dependent on Cell Z1 on the same workbook as the data. I would like to be able to have cell Z1=476 and then have the $476 part update. Can this be done?

[Andy Pope]

You can not do it within the SERIES formula itself. Instead you need to create a named range that is dynamic and then use that as the reference rather than a static range.

Named range MYDATA :=Data2!$N$350:INDEX(Data2!$N:$N,Data2!Z1)

revised series formula
=SERIES(,Data2!$A$350:$A$476,MYDATA,1)

You will need to do the same thing for the category label range using a different named range.