Reference a cell to get worksheet name

[Guest]

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

 

[hrlngrv]

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(INDIRECT("'"&M2&"'!$B:$C"))-1,2)

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

http://groups-beta.google.com/group/microsoft.public.excel/msg/8052f0f2d84f9e51

 

[Guest]

Thanks it worked like a charm.

Fysh wrote...
....

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(INDIRECT("'"&M2&"'!$B:$C"))-1,2)

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

http://groups-beta.google.com/group/microsoft.public.excel/msg/8052f0f2d84f9e51