Home |
Search |
Today's Posts |
#1
|
|||
|
|||
data source remain constant
I have line chart that uses values in column C. Periodically I insert a new
column of data which then becomes the new column C. Now the chart is displaying the data in column D but would like it to always chart the values in C. I tried removing the absolute references in the source data but it won't allow that. Other suggestions? -- maryj |
#2
|
|||
|
|||
data source remain constant
You need to make a dynamic chart, which uses named ranges for its series
data. I assume your chart uses row 1 for category labels, and rows below that for values of the series in the chart. Open the Define Names dialog (Insert menu Names Define, or CTRL+F3 shortcut). Enter a name in the Name box, something like XCategories, and in the Refers To box, enter a formula like: =OFFSET($B$1,0,1,1,COUNTA(OFFSET($B$1,0,1,1,255)) This refers to the range 0 rows below and one column to the right of B1, which is one row high and COUNTA(blah) columns wide. Look up OFFSET for more details on the function. The range is known to Excel by the name you entered (XCategories). There's your X values. Here's how to get the Y values. For each series you have, define a named range like this: Name: YValues1 Refers To: = OFFSET(XCategories,0,1) Name: YValues2 Refers To: = OFFSET(XCategories,0,2) These basically mean, move down 1, 2, etc., rows from the XCategories range, and name it YValues1, 2, etc. Now go to the chart. Select a series and look at the SERIES formula in the Formula Bar: =SERIES(Sheet1!$B$2,Sheet1!$C$1:$M$1,Sheet1!$C$2:$ M$2,1) This means the series name is in B2, the X values are in C1:M1, the Y values are in C2:M2, and it's the first series in the chart. Change this formula, using the names you defined: =SERIES(Sheet1!$B$2,Sheet1!XCategories,Sheet1!YVal ues1,1) Don't worry if the sheet name is replaced by the workbook name in front of the names: =SERIES(Sheet1!$B$2,Book1.xls!XCategories,Book1.xl s!YValues1,1) For more information, examples, and links, check out this web page: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ maryj wrote: I have line chart that uses values in column C. Periodically I insert a new column of data which then becomes the new column C. Now the chart is displaying the data in column D but would like it to always chart the values in C. I tried removing the absolute references in the source data but it won't allow that. Other suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I view Pivot Table source data file and field names? | Excel Discussion (Misc queries) | |||
Adding more source data to existing scatter plot | Charts and Charting in Excel | |||
Need Formula to display pivot table source data | Excel Worksheet Functions | |||
pivot table in data source order | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |