I have about 12 graphs each with 4 data sets that I have to change the
data set range on each month to display the next months data as it is
accumulated through the month. This is a very time consuming task.
Is there a way to set the data range to key off of a formula (using
current month and a vlookup for example) or off of a range supplied
from key input cells?

Ultimately I would like to be able to specify a data range (from jan1
through Dec 31) and have the graph update the data series for the
range and display. But for now I'll settle on a faster method to
change the data range each month.

Any ideas?

Duey

Three possible things to think about:
1) In the y-values cells for yet-to-arrive data enter =NA(). This displays
as #N/A but is ignored by the chart engine
2) If you are using Excel 2003, make the data range a Excel List; Now the
chart will automatically update. In Excel 2007 the List has become a Table.
3) Make a dynamic chart using a named formula with OFFSET. See
http://peltiertech.com/Excel/Charts/...hartLinks.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Duey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have about 12 graphs each with 4 data sets that I have to change the
> data set range on each month to display the next months data as it is
> accumulated through the month. This is a very time consuming task.
> Is there a way to set the data range to key off of a formula (using
> current month and a vlookup for example) or off of a range supplied
> from key input cells?
>
> Ultimately I would like to be able to specify a data range (from jan1
> through Dec 31) and have the graph update the data series for the
> range and display. But for now I'll settle on a faster method to
> change the data range each month.
>
> Any ideas?
>
> Duey
>

Here are a few ways to define ranges that change with your data.
All of this stuff has appeared in the newsgroups many times. I thought it
might be
nice to summarise in one place.

1) Just highlight a whole column range. eg range
"A:J" will select all data in columns A through J. Name it Test_r.
You can use this in VBA like this,

Range("Test_r").Select

Or set a range object, and then use it. Like this,

Set tst_rng = Range("Test_r")
tst_rng.Select

2) Use the UsedRange property of the sheet.

ActiveWorkbook.Names.Add
Name:="Test_r", RefersToR1C1:=Sheet1.UsedRange

3) Use End(xlDown) or End(xlToRight) to maintain the range with a little
VBA.

ActiveWorkbook.Names.Add Name:="Test_r", RefersToR1C1:= _
Sheet1.Range(Range("A1"), Range("C1").End(xlDown))

A more complex example of this is shown in the 'Chart Selector' samples
at http://www.edferrero.com/ExcelCharts...2/Default.aspx

4) Use a dynamic range. eg Suppose your data is in the range "A1:G19", then
define
a new range name called AcData with the following formula;

=Sheet1!$A$1:INDEX(Sheet1!$G:$G, COUNTA(Sheet1!$A:$A))

This assumes that column headings are in row 1, and that column A
contains a value for
every row in the data range ie no null values or blanks.

The dynamic range works because COUNTA(Sheet1!$A:$A) gives the total
number of
rows, and INDEX(Reference,RowNo) points to the cell in 'Reference' given
by 'RowNo'. In our case 'Reference' is all of column G, so if there are
values in A1 to A19, the INDEX function would point to cell G19.

To add a named range, use the menu items Insert-Name-Define... enter the
name for the range and the formula, then click Ok.

With this method you do not need VBA to maintain the range.

5) Similar to (4) you can use the OFFSET function to define a dynamic range.

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

This is nice because you can easily make the range dynamic in both
dimensions.

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


Ed Ferrero
www.edferrero.com