Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to use dynamic ranges and names for dumb bar chart
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to use dynamic ranges and names for dumb bar chart
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/Cha...s.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 On Feb 1, 11:32 am, "eholz1" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
trying to use dynamic ranges and names for dumb bar chart
On Jan 31, 5:36 pm, "marcus" wrote:
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/Cha...s.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 On Feb 1, 11:32 am, "eholz1" wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic chart ranges | Excel Discussion (Misc queries) | |||
Dynamic chart ranges (slight variation) | Charts and Charting in Excel | |||
dynamic XY chart names | Excel Discussion (Misc queries) | |||
Dynamic chart: Changing Ranges | Charts and Charting in Excel | |||
Comparing 2 dynamic ranges for matching names | Excel Programming |