In a chart, series are specified like:
=SERIES[ (series_name) , (X-values) , (Y-values) , (plot_order) ].
Range references in a SERIES formula are always absolute and always contain a worksheet name. If you have defined named ranges then the series formula can contain a named range in place of a range.

=SERIES('g2'!$G$36,'g2'!$A$37:$A$71,'g2'!$G$37:$G$71,7)
If I want to write VBA code that knows that my data starts on row 37 or that the Y values are in column G (numerically=7), how do I pull that information?
Is there a way?


http://www.j-walk.com/ss/excel/tips/tip83.htm
is probably a good starting point but honestly I am having a terrible time deciphering it as I am a sporadic VBA user.

Any hints?