Reference a cell to get worksheet name
 

I am trying to reference a cell with worksheet names which is populated from
another formula.
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)

Thanks for any assistance

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

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