trying to use dynamic ranges and names for dumb bar chart

[eholz1]

i surrender,

I am trying to set a bar chart (vertical bars) and define a ranges and
names for the categories axis
and the y axis (temperatures)

I try to define a name (from the insert,name,define) and set it to the
following
weeks =offset(a1,0,0,counta(a:a))
and
temps = offset(b1,0,0,counta(b:b))

the data is like:
(col a) (col b)
week1 55.5
week2 60.3 ...

I can make a chart using the data in the cells (a1 to a15, and b1 to
b15).
the chart is fine as long as I do not try to reset the series using
the named ranges.

I try to replace both the category axis (weeks) and the temperatures
(temps) with the names, and the chart errors out with a formula error.
If i click anywhere in the chart, the data changes, and blows the
chart.

What am I missing here???
I am using Excel 2003, and windows xp pro
ewholz

 

[marcus]

Hi

Firstly I would say to go to this site as Jon P has some excellent
examples of dynamic charts.

http://www.peltiertech.com/Excel/Charts/Dynamics.html#DynoCht

Alternatively a less detailed description

Make 2 dynamic named ranges. Call the named range Weeks. This
assumes you don't have a header row in A1.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$2000),1)

The second named range is called Temps.

=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B$1:$B$2000),1)

Create your bar chart as usual - then right click on the chart -
Source Data - Series - Values should be

=sheet1!Temps

Category X value label should be

=Sheet1!Weeks

Click OK and add data to Col A and B and it should update
automatically.

Good Luck.

Marcus

 

[eholz1]

Hi

Firstly I would say to go to this site as Jon P has some excellent
examples of dynamic charts.

http://www.peltiertech.com/Excel/Charts/Dynamics.html#DynoCht

Alternatively a less detailed description

Make 2 dynamic named ranges. Call the named range Weeks. This
assumes you don't have a header row in A1.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$2000),1)

The second named range is called Temps.

=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B$1:$B$2000),1)

Create your bar chart as usual - then right click on the chart -
Source Data - Series - Values should be

=sheet1!Temps

Category X value label should be

=Sheet1!Weeks

Click OK and add data to Col A and B and it should update
automatically.

Good Luck.

Marcus

Hello marcus,

Thanks for the link, and the info. I was wondering if I should use
absolute addressing, etc. the idea was to use counta to count the
columns with data such that the count would be the number of rows for
the range, etc.

I will also check the link.

thanks,
eholz1