A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using formula to create multiple versions of a Graphs



 
 
Thread Tools Display Modes
  #1  
Old July 27th, 2007, 06:26 AM posted to microsoft.public.excel.charting
BarryL
external usenet poster
 
Posts: 18
Default Using formula to create multiple versions of a Graphs

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?
  #2  
Old July 27th, 2007, 07:59 AM posted to microsoft.public.excel.charting
Del Cotter
external usenet poster
 
Posts: 553
Default Using formula to create multiple versions of a Graphs

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.
  #3  
Old July 27th, 2007, 02:06 PM posted to microsoft.public.excel.charting
Jon Peltier
external usenet poster
 
Posts: 5,018
Default Using formula to create multiple versions of a Graphs

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.



  #4  
Old July 28th, 2007, 05:08 AM posted to microsoft.public.excel.charting
BarryL
external usenet poster
 
Posts: 18
Default Using formula to create multiple versions of a Graphs

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.




  #5  
Old July 28th, 2007, 02:34 PM posted to microsoft.public.excel.charting
Jon Peltier
external usenet poster
 
Posts: 5,018
Default Using formula to create multiple versions of a Graphs

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.






  #6  
Old July 29th, 2007, 11:52 AM posted to microsoft.public.excel.charting
BarryL
external usenet poster
 
Posts: 18
Default Using formula to create multiple versions of a Graphs

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.

"Jon Peltier" wrote:

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.






  #7  
Old July 29th, 2007, 03:48 PM posted to microsoft.public.excel.charting
Jon Peltier
external usenet poster
 
Posts: 5,018
Default Using formula to create multiple versions of a Graphs

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
_______


"BarryL" wrote in message
news
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.

"Jon Peltier" wrote:

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.








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.