Using Indirect Cell References in a Chart

I am trying to create a 'self-sizing' chart, but don't
know if I can use indirect cell references in a chart.

Here's the scenario: I have a tab with data(DataTable!
A2:A20) and a tab with Graphs (Graphs). I use a Max
formula to determine the last row of data entered, and
I've labled that formulas as 'DataTable!LastRow'.

In my 'Graphs' tab, I have a cell called 'Graphs!
XAxisLabel' with the formula ='Datatable!A3:A'&(DataTable!
LastRow)' that displays the rows of data to be used in the
chart. I want to use a formula (=Graphs!XAxisLabel) to
pull the specifics for the chart. I'd use a similar
formula for other Series ranges.

Can this be done within Excel, or do I have to do this via
a Visual Basic route?

Thanks for the advice, and let me know if I haven't been
clear.

Rick

rretzko wrote:
> I am trying to create a 'self-sizing' chart...

> Can this be done within Excel, or do I have to do this via
> a Visual Basic route?

I'm not sure I follow your question entirely, but have you looked at the
dynamic charts on www.tushar-mehta.com? I suspect that will do what you
want.

Dave
dvt at psu dot edu

Rick -

You need to define a named range, not just a single cell, for
XAxisLabel. Press Ctrl-F3 to open the Define Names dialog, type
XAxisLabel in the Name box, and in Refers To, enter this:

=OFFSET(DataTable!$A$3,0,0,LastRow-2,1)

For Y1Values, assuming it's in column B, you have two Refers To options:

=OFFSET(DataTable!$B$3,0,0,LastRow-2,1)

or

=OFFSET(XAxisLabel,0,1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

rretzko wrote:
> I am trying to create a 'self-sizing' chart, but don't
> know if I can use indirect cell references in a chart.
>
> Here's the scenario: I have a tab with data(DataTable!
> A2:A20) and a tab with Graphs (Graphs). I use a Max
> formula to determine the last row of data entered, and
> I've labled that formulas as 'DataTable!LastRow'.
>
> In my 'Graphs' tab, I have a cell called 'Graphs!
> XAxisLabel' with the formula ='Datatable!A3:A'&(DataTable!
> LastRow)' that displays the rows of data to be used in the
> chart. I want to use a formula (=Graphs!XAxisLabel) to
> pull the specifics for the chart. I'd use a similar
> formula for other Series ranges.
>
> Can this be done within Excel, or do I have to do this via
> a Visual Basic route?
>
> Thanks for the advice, and let me know if I haven't been
> clear.
>
> Rick

Jon - THANKS! The process took a bit of working, but this
was exactly the information I needed to get on the right
track!

Rick
>-----Original Message-----
>Rick -
>
>You need to define a named range, not just a single cell,
for
>XAxisLabel. Press Ctrl-F3 to open the Define Names
dialog, type
>XAxisLabel in the Name box, and in Refers To, enter this:
>
>=OFFSET(DataTable!$A$3,0,0,LastRow-2,1)
>
>For Y1Values, assuming it's in column B, you have two
Refers To options:
>
>=OFFSET(DataTable!$B$3,0,0,LastRow-2,1)
>
>or
>
>=OFFSET(XAxisLabel,0,1)
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>http://www.geocities.com/jonpeltier/Excel/index.html
>_______
>
>rretzko wrote:
>> I am trying to create a 'self-sizing' chart, but don't
>> know if I can use indirect cell references in a chart.
>>
>> Here's the scenario: I have a tab with data(DataTable!
>> A2:A20) and a tab with Graphs (Graphs). I use a Max
>> formula to determine the last row of data entered, and
>> I've labled that formulas as 'DataTable!LastRow'.
>>
>> In my 'Graphs' tab, I have a cell called 'Graphs!
>> XAxisLabel' with the formula ='Datatable!A3:A'&
(DataTable!
>> LastRow)' that displays the rows of data to be used in
the
>> chart. I want to use a formula (=Graphs!XAxisLabel) to
>> pull the specifics for the chart. I'd use a similar
>> formula for other Series ranges.
>>
>> Can this be done within Excel, or do I have to do this
via
>> a Visual Basic route?
>>
>> Thanks for the advice, and let me know if I haven't
been
>> clear.
>>
>> Rick
>
>.
>