Chart Data Range (2003)

Topic

New Reply

About the only thing I know about spread sheets is where they’re located on my hard drive. Having said that, is there a way for a chart to “dynamically” grow as the data range has additional data added to it (the range expands)? In the attached spreadsheet, every time I enter new data in the first sheet (i.e. B176) I have to change the data range in the chart (“Fuel Pricing Chart”). Any help gratefully appreciated (as opposed to ungratefully appreciated).

Reply | Quote

Replies

There are many examples in the links at Dynamic Chart Links

Steve

Reply | Quote

Steve:

Thanks for the great batch of links; good stuff!!

Reply | Quote

You can define so-called dynamic ranges and use them as source range for the chart.
– Activate the Fuel Pricing Data sheet.
– Select Insert | Name | Define…
– Type ChartLabels in the Names in workbook box.
– Type the following formula in the Refers to box:

=OFFSET('Fuel Pricing Data'!$A$59,0,0,COUNT('Fuel Pricing Data'!$B$59:$B$65536),1)

– Click Add.
– Type ChartData in the Names in workbook box.
– Type the following formula in the Refers to box:

=OFFSET('Fuel Pricing Data'!$B$59,0,0,COUNT('Fuel Pricing Data'!$B$59:$B$65536),1)

– Click Add.
– Click OK
– Activate the Fuel Pricing Chart sheet.
– Select Chart | Source Data…
– Enter =’Fuel Pricing Data’!ChartData in the Values box.
– Enter =’Fuel Pricing Data’!ChartLabels in the Category Labels box.
– Click OK.
See attached version.

Reply | Quote

Wow! Thanks Hans (and especially for doing the work for me). I think I get it; at least I was able to do the same thing in the other chart. I’m dim, but persistent.

Reply | Quote