Thanks it worked like a charm.

" wrote:

Fysh wrote...
....
Once I have the worksheet name I am then trying to use it in a formula
as
follows to be used for a dynamic chart. I posted in the Charts forum
but got
no answer. Is this possible and if so am I on the right track?
this would be used for XValues

=OFFSET((INDIRECT("'"& M2 &"' !$B$4")),0,0,COUNTA(INDIRECT("'"& M2
&"'
!$B:$C"))-1,2)

First off, don't get cute with your quotes. Use ' and " only. And don't
add unnecessary whitespace - the space between the ' and ! throws a
syntax error.

=OFFSET((INDIRECT("'"&M2&"'!$B$4")),0,0,COUNTA(IND IRECT("'"&M2&"'!$B:$C"))-1,2)

As for using this in chart series, you have to use trickery. See

http://groups-beta.google.com/group/...52f0f2d84f9e51