Using formula to create multiple versions of a Graphs

[BarryL]

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.

For Example:
When I enter the number 1 into cell "a1" I would like to have the graph
automatically change to show the map points related to report 1, when I enter
2 inot "A1", I would like the source data to change to show points related to
report 2.

How can this be done?

 

[Del Cotter]

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.

 

[Jon Peltier]

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

 

[BarryL]

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]

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
_______

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

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
_______

 

[BarryL]

Jon,

This approch works well.

I have established a vertical list of regions with an adjacent column
containing the category axis data. The data in the category axis column
changes depending on the report I am running. In the national report all
regions have data. If running a regional report, only the region of choice
has data and the rest have 0. By filtering the data in the category axis for
values greater than 0 I can modify my graph for regional reports.

However once I have filtered the data, that filter remains in place until I
filter again. Therefore, when I run a new regional report and the category
axis values change, I need to manually filter the category axis to show all
in order to display all regions then filter again to just display the desired
region.

Is there a way to remove and then reapply the filter each time the report
changes.

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
_______

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

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
_______


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
(e-mail address removed),
which goes to a spam folder-- please send your email to del3
instead.

 

[Jon Peltier]

Barry -

Is the report updated by a VBA procedure? If so, you could have the
procedure also update the filter.

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

Jon,

This approch works well.

I have established a vertical list of regions with an adjacent column
containing the category axis data. The data in the category axis column
changes depending on the report I am running. In the national report all
regions have data. If running a regional report, only the region of
choice
has data and the rest have 0. By filtering the data in the category axis
for
values greater than 0 I can modify my graph for regional reports.

However once I have filtered the data, that filter remains in place until
I
filter again. Therefore, when I run a new regional report and the
category
axis values change, I need to manually filter the category axis to show
all
in order to display all regions then filter again to just display the
desired
region.

Is there a way to remove and then reapply the filter each time the report
changes.

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
_______

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


:

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
_______


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
(e-mail address removed),
which goes to a spam folder-- please send your email to del3
instead.