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
|
|||
|
|||
how do i update a chart area automatically
Hello,
I have an offset formula I use to update a chart plotted by columns. I name the ranges and change the range areas to the offset formula...something like this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes all the rows available for charting minus the row with the label. I use this same technique for two columns...Column A acts as the Category x axis and Column B as the value axis. Works beautifully Now, I would like to come up with a way to plot by rows. My values would grow as new field names would be added across the columns...specifically, Jan, Feb, March and then have April, May and June added to the plot area automatically. C138:C147 would have the names of sales reps and D137:F137 would hold the Month labels and then D138: F147 would list the values. So the list of sales reps is static, but new data comes in for each month and needs to be added to the charts. I have many of these charts on a dash board and do not want to update all of them every month. Any suggestions would be appreciated alathough I want to use VBA as a last resort. Thanks! |
#2
|
|||
|
|||
how do i update a chart area automatically
You use the same idea with the OFFSET formula. The syntax of OFFSET is:
OFFSET(range, offset rows, offset columns, resize rows, resize columns) so your month label range would have a "refers to" definition like =OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$13 7)) This assumes only the dates appear in row 137, and that a date header is not added until data for the date is added. The easiest way to define the value ranges with respect to the month range (MonthRange below) is: =OFFSET(MonthRange,1,0) =OFFSET(MonthRange,2,0) =OFFSET(MonthRange,3,0) etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Kay" wrote in message ... Hello, I have an offset formula I use to update a chart plotted by columns. I name the ranges and change the range areas to the offset formula...something like this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes all the rows available for charting minus the row with the label. I use this same technique for two columns...Column A acts as the Category x axis and Column B as the value axis. Works beautifully Now, I would like to come up with a way to plot by rows. My values would grow as new field names would be added across the columns...specifically, Jan, Feb, March and then have April, May and June added to the plot area automatically. C138:C147 would have the names of sales reps and D137:F137 would hold the Month labels and then D138: F147 would list the values. So the list of sales reps is static, but new data comes in for each month and needs to be added to the charts. I have many of these charts on a dash board and do not want to update all of them every month. Any suggestions would be appreciated alathough I want to use VBA as a last resort. Thanks! |
#3
|
|||
|
|||
how do i update a chart area automatically
Jon,
I am sorry it has taken me so long to respond to your answer, but I have been travelling on buisness. I appreciate your responseD!!! I am not sure I understand your explanation of the MonthRanges. Do I need to create multiple range names for each column of data that hold the values? Jan Feb Mar Salespeople's Names 4589 1234 4589 MORE 2456 4879 12365 MORE 48795 7845 45623 Kay "Jon Peltier" wrote: You use the same idea with the OFFSET formula. The syntax of OFFSET is: OFFSET(range, offset rows, offset columns, resize rows, resize columns) so your month label range would have a "refers to" definition like =OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$13 7)) This assumes only the dates appear in row 137, and that a date header is not added until data for the date is added. The easiest way to define the value ranges with respect to the month range (MonthRange below) is: =OFFSET(MonthRange,1,0) =OFFSET(MonthRange,2,0) =OFFSET(MonthRange,3,0) etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Kay" wrote in message ... Hello, I have an offset formula I use to update a chart plotted by columns. I name the ranges and change the range areas to the offset formula...something like this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes all the rows available for charting minus the row with the label. I use this same technique for two columns...Column A acts as the Category x axis and Column B as the value axis. Works beautifully Now, I would like to come up with a way to plot by rows. My values would grow as new field names would be added across the columns...specifically, Jan, Feb, March and then have April, May and June added to the plot area automatically. C138:C147 would have the names of sales reps and D137:F137 would hold the Month labels and then D138: F147 would list the values. So the list of sales reps is static, but new data comes in for each month and needs to be added to the charts. I have many of these charts on a dash board and do not want to update all of them every month. Any suggestions would be appreciated alathough I want to use VBA as a last resort. Thanks! |
#4
|
|||
|
|||
how do i update a chart area automatically
Yes, you need to create a name for the months (the X values) and for each Y
series. These names are used in the series definition formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Kay" wrote in message ... Jon, I am sorry it has taken me so long to respond to your answer, but I have been travelling on buisness. I appreciate your responseD!!! I am not sure I understand your explanation of the MonthRanges. Do I need to create multiple range names for each column of data that hold the values? Jan Feb Mar Salespeople's Names 4589 1234 4589 MORE 2456 4879 12365 MORE 48795 7845 45623 Kay "Jon Peltier" wrote: You use the same idea with the OFFSET formula. The syntax of OFFSET is: OFFSET(range, offset rows, offset columns, resize rows, resize columns) so your month label range would have a "refers to" definition like =OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$13 7)) This assumes only the dates appear in row 137, and that a date header is not added until data for the date is added. The easiest way to define the value ranges with respect to the month range (MonthRange below) is: =OFFSET(MonthRange,1,0) =OFFSET(MonthRange,2,0) =OFFSET(MonthRange,3,0) etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Kay" wrote in message ... Hello, I have an offset formula I use to update a chart plotted by columns. I name the ranges and change the range areas to the offset formula...something like this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes all the rows available for charting minus the row with the label. I use this same technique for two columns...Column A acts as the Category x axis and Column B as the value axis. Works beautifully Now, I would like to come up with a way to plot by rows. My values would grow as new field names would be added across the columns...specifically, Jan, Feb, March and then have April, May and June added to the plot area automatically. C138:C147 would have the names of sales reps and D137:F137 would hold the Month labels and then D138: F147 would list the values. So the list of sales reps is static, but new data comes in for each month and needs to be added to the charts. I have many of these charts on a dash board and do not want to update all of them every month. Any suggestions would be appreciated alathough I want to use VBA as a last resort. Thanks! |
#5
|
|||
|
|||
how do i update a chart area automatically
Thank you Jon. "Jon Peltier" wrote: Yes, you need to create a name for the months (the X values) and for each Y series. These names are used in the series definition formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Kay" wrote in message ... Jon, I am sorry it has taken me so long to respond to your answer, but I have been travelling on buisness. I appreciate your responseD!!! I am not sure I understand your explanation of the MonthRanges. Do I need to create multiple range names for each column of data that hold the values? Jan Feb Mar Salespeople's Names 4589 1234 4589 MORE 2456 4879 12365 MORE 48795 7845 45623 Kay "Jon Peltier" wrote: You use the same idea with the OFFSET formula. The syntax of OFFSET is: OFFSET(range, offset rows, offset columns, resize rows, resize columns) so your month label range would have a "refers to" definition like =OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$13 7)) This assumes only the dates appear in row 137, and that a date header is not added until data for the date is added. The easiest way to define the value ranges with respect to the month range (MonthRange below) is: =OFFSET(MonthRange,1,0) =OFFSET(MonthRange,2,0) =OFFSET(MonthRange,3,0) etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Kay" wrote in message ... Hello, I have an offset formula I use to update a chart plotted by columns. I name the ranges and change the range areas to the offset formula...something like this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes all the rows available for charting minus the row with the label. I use this same technique for two columns...Column A acts as the Category x axis and Column B as the value axis. Works beautifully Now, I would like to come up with a way to plot by rows. My values would grow as new field names would be added across the columns...specifically, Jan, Feb, March and then have April, May and June added to the plot area automatically. C138:C147 would have the names of sales reps and D137:F137 would hold the Month labels and then D138: F147 would list the values. So the list of sales reps is static, but new data comes in for each month and needs to be added to the charts. I have many of these charts on a dash board and do not want to update all of them every month. Any suggestions would be appreciated alathough I want to use VBA as a last resort. Thanks! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2002 chart does not update when worksheet data changes | proquant | Charts and Charting | 1 | June 15th, 2005 12:23 AM |
Installing cumulative update for OE 6 | Vasilis | Outlook Express | 8 | May 7th, 2005 02:01 AM |
Office XP Update Problem | Bill | General Discussions | 0 | December 1st, 2004 06:11 PM |
Automatically Update Document Styles | Shugwan | General Discussion | 1 | August 7th, 2004 03:09 AM |
Chart menu visible property | Sandy V | Charts and Charting | 8 | May 17th, 2004 01:39 PM |