Dynamic Charts - Resolved

[Ian Mac]

All,

I've set up a Pivot table on a worksheet that Hides/Unhides items using an item selected from a Combobox as a reference.

I'm not trying to set up a chart that can handle dynamic ranges.
My data is in only two columns, say ColA 'List' ColB 'Values' I'd like a simple bar chart that can handle the data range growing.

The problem I've came across is that that chart Value Series doesn't like Offset or even a Named range using it.

Can this be done?

By the way you all probably know already I'm using XL97 so Pivot charts are out not an option.

Any help would be greatfully received.

Kind Regards

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-15 09:46 ]

[Ian Mac]

I've perused the board and have seen this question come up a few times.

So if any one needs to know how to create a Dynamic Chart here goes:

First create a Dynamic Named range for each of the components you need.

say:

ChartLables, and
ChartValues.

(I'll only use 2)

The bring up the chart wizard, select a chart type you want.
I'll use a column chart.

Click Next

If there is any info in the Data Range box, Remove it (i.e. delete).

Click on the Series tab, Click Add.

Now for the bit that people get confused about. Instead of entering the Named Range directly into the Values Box (=ChartValues)

you need to reference the WORKBOOK also:

=Book1.xls!ChartValues

then in X Axis put:

=Book1.xls!ChartLables

Now you can Click Next and place the chart where you want.

And that's about it. Just thought I'd let people know.

[tealeaf]

On 2002-05-17 01:40, Ian Mac wrote:
I've perused the board and have seen this question come up a few times.

So if any one needs to know how to create a Dynamic Chart here goes:

First create a Dynamic Named range for each of the components you need.

say:

ChartLables, and
ChartValues.

(I'll only use 2)

The bring up the chart wizard, select a chart type you want.
I'll use a column chart.

Click Next

If there is any info in the Data Range box, Remove it (i.e. delete).

Click on the Series tab, Click Add.

Now for the bit that people get confused about. Instead of entering the Named Range directly into the Values Box (=ChartValues)

you need to reference the WORKBOOK also:

=Book1.xls!ChartValues

then in X Axis put:

=Book1.xls!ChartLables

Now you can Click Next and place the chart where you want.

And that's about it. Just thought I'd let people know.

what do u mean by create a Dynamic Named range for each of the components you need. like create these variables referring to my values and names in VB or in excel?

what does Book1 refer to? the name of the file? Book1.xls!ChartValues