Transpose the data, and leave out some columns:

Region Region 2 Source Data
1 Eastern a 3
2 Eastern b 8
3 Eastern c 4
4 Central d 9
5 Central e 2
6 Central f 7
7 Central g 8
8 Western h 5
9 Western i 7
10 Western j 4

Apply an autofilter (Data menu) to this list. To view all (National), don't
filter any data. To view a regional report, filter on the Region 2 column.
To get a top performers report, filter the Data column using the (Top)
option, and set it to the top 5 (or whatever number you want). When you
apply a filter, some rows are hidden, and by default a chart only shows
visible rows.

If you have more extensive data with more fields and need more detailed
filtering, you could make one or more pivot tables based on this data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"BarryL" wrote in message
...
Jon,

I have a series of reports which contain the same graph. The source data
I
want to chart for the National report has all data points for all regions.
However I also want to chart the only the regional data for the regional
reports. The graph itself does not seem to accept a formula to change the
data I want to plot. Therefore, i created a source data line (a - j) that
changes according to the report I am running as follows:
Region: 1 2 3 4 5 6 7 8
9 10

Source data to chart: a b c d e f g h
i j
National Report 3 8 4 9 2 7 8 5
7 4
Eastern Report 3 8 4
Central Report 9 2 7 8
Western Report:
5 7 4
Top Performers: 8 9 7 8
7
a is [=vlookup(report,source table,2,False)]
b is [=vlookup(report,source table,3,False)]
c is [=vlookup(report,source table,3,False)]


The source data line in the graph is b3:k3 for the national report
If I am running the eastern report the data to plot line is: 3 8 4 --
-- -- -- etc
If I am running the top performers report the plot line is: -- 8 --
9 --
7 8 -- 7
I assume the graph's source data line is the same b3:K3 for all reports
Therefore, my regional graph show a series of empty columns.

How do I change the source data line for the chart so that it only shows
the
data points I am charting and so that it ignores the blank data points.

BarryL


"Jon Peltier" wrote:

The point is, you have to change the data you want to chart. The chart
only
plots what you tell it. The data manipulation smarts are in the
worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Del Cotter" wrote in message
...
On Thu, 26 Jul 2007, in microsoft.public.excel.charting,
BarryL said:

I would like to be able to use a formula in an Excel 2003 graph, to
dynamically modify the source and lable data of a graph based on a cell
reference.

Use the IF(), CHOOSE(), or LOOKUP() functions as appropriate. This is
really a spreadsheet question rather than a chart question.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3
instead.