Excel 2003 macro that defines chart value ranges defined dynamically

John,
Is there some reason why you cannot use a dynamic named range for your charts? It will update automatically, without need for VBA code.

To create a dynamic named range:
1) Open the Insert...Name...Define menu item
2) In the "Names in workbook" field, enter a name like Weight1
3) In the "Refers to" field, enter a formula like one of:
=$A$2:INDEX($A$1:$A$100,COUNTA($A$1:$A$100))           both count and data drawn from column A
=$B$2:INDEX($B$1:$B$100,COUNTA($A$1:$A$100))           data drawn from column B, but count from column A
=$A$2:INDEX($A$1:$A$100,COUNTA($B$1:$B$100))           data drawn from column A, but count from column B    
The latter two formulas tolerates blanks, such as when you are entering data (second formula). The third formula allows you to prepopulate column A with all the days in the month, some of which are in the future. Please let me know if the "blank" cells at the end of the data contain empty strings ="" as result of formulas--alternative formulas are possible for the dynamic named ranges.
4) Click "OK"

Note that Excel will add the worksheet name to the formula after you click the "Add" or "OK" buttons. Also, the dynamic named range formula will be thrown off track if there are any blanks within the data. The address range parts of the formula may refer to a specific range (as shown) or to an entire column such as COUNTA($A:$A). Finally, don't use relative cell addresses (without the $) because the formula almost certainly won't work as intended.

Another common formula for dynamic named ranges uses the OFFSET function. I don't like them as much because they are volatile (will recalc whenever any cell on workbook changes), thus slowing down recalc time.
=OFFSET($A$1,0,0,COUNTA($A$1:$A$100),1)

To use your dynamic named range in a chart:
1) Right-click on one of the points in your chart
2) Choose "Source data" from the resulting pop-up
3) Go to the Series tab
4) Click in either the X-values or Y-values field, then enter a formula like:
='my workbook.xls'!Weight1           if there is only one Weight1 in your workbook
='my sheet name'!Weight1             if there are more than one named range called Weight1 in your workbook
5) Repeat as necessary

In Excel 2007, the procedure for creating a named range and using it in a chart is slightly different:
You may want to use dynamic named ranges for your chart or formula. These automatically expand and contract as you add or delete data. To create a dynamic named range:
1) Open the Insert...Name...Define menu item
2) In the "Names in workbook" field, enter a name like Weight1
3) In the "Refers to" field, enter a formula like one of:
=$A$2:INDEX($A$1:$A$100,COUNTA($A$1:$A$100))           both count and data drawn from column A
=$B$2:INDEX($B$1:$B$100,COUNTA($A$1:$A$100))           data drawn from column B, but count from column A
=$A$2:INDEX($A$1:$A$100,COUNTA($B$1:$B$100))           data drawn from column A, but count from column B    
The latter two formulas tolerates blanks, such as when you are entering data (second formula). The third formula allows you to prepopulate column A with all the days in the month, some of which are in the future.
4) Click "OK"

To use your dynamic named range in a chart:
1) Right-click on one of the points in your chart
2) Choose "Select Data" from the resulting pop-up
3) Select your series name on the left pane of the resulting dialog, then click the Edit button
4) Enter a formula like:
='my workbook.xls'!Weight1           if there is only one Weight1 in your workbook
='my sheet name'!Weight1             if there are more than one named range called Weight1 in your workbook
5) Repeat steps 3 & 4 as required. For a line chart, click the Edit button on the right pane of the resulting dialog, then enter the formula for your X-axis labels.


If you want to add series (as well as points to existing series) when you add data, then take a look at the VBA solution offered by Aaron Blood at http://www.xl-logic.com      

Here is a sample workbook that shows both dynamic named ranges used in a chart and Aaron Blood's VBA approach: http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=3857



Regards,

byundt