Using Indirect Cell References in a Chart

[rretzko]

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

 

[dvt]

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

 

[Jon Peltier]

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

 

[rretzko]

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

Rick