Using formula to create multiple versions of a Graphs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BarryL - 27 Jul 2007 06:26 GMT

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?

Reply to this Message

Del Cotter - 27 Jul 2007 07:59 GMT

>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.

Signature

Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
which goes to a spam folder-- please send your email to del3 instead.

Reply to this Message

Jon Peltier - 27 Jul 2007 14:06 GMT

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.

Reply to this Message

BarryL - 28 Jul 2007 05:08 GMT

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.
[quoted text clipped - 12 lines]
> > Use the IF(), CHOOSE(), or LOOKUP() functions as appropriate. This is
> > really a spreadsheet question rather than a chart question.

Reply to this Message

Jon Peltier - 28 Jul 2007 14:34 GMT

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,
>
[quoted text clipped - 55 lines]
>> > Use the IF(), CHOOSE(), or LOOKUP() functions as appropriate. This is
>> > really a spreadsheet question rather than a chart question.

Reply to this Message

BarryL - 29 Jul 2007 11:52 GMT

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:
>
[quoted text clipped - 86 lines]
> >> > Use the IF(), CHOOSE(), or LOOKUP() functions as appropriate. This is
> >> > really a spreadsheet question rather than a chart question.

Reply to this Message

Jon Peltier - 29 Jul 2007 15:48 GMT

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,
>
[quoted text clipped - 125 lines]
>> >> > is
>> >> > really a spreadsheet question rather than a chart question.

Reply to this Message