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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|