1. ## Graphing

Hi,

I have created a XY chart in Excel that contains 7 columns of Y data and 1 column of X data. My chart therefore has 7 series. Everytime I add more data to the existing columns I find myself going through the Chart Series and changing the range.

This is tedious and repetitive. Is there a way to automate the proceess? What about a way to display the series ranges in some cells and link them to the chart, so the series in the chart will change dynamicly?

Hanan.

2. ## Re: Graphing

You can use a similar approach as <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=35566&page=0& view=expanded&sb=5&vc=1> Here</A> by using a named range, e.g. ChartData. Insert, Name, Define and in the refers to box enter

=OFFSET(Sheet1!\$A\$1,0,0,COUNTA(Sheet1!\$A:\$A),8)

Then set the Source Data for the chart to ChartData. the above formula assumes the data starts in A1 and is contained in 8 columns.

If you add an additional row of data the chart should automatically include it.

Andrew C

3. ## Re: Graphing

Hi Andrew,

I have used the OFFSET function in the Insert-Name-Define as follows:

XX1 =OFFSET(Temperature!\$A\$5,0,0,COUNTA(Temperature!\$A :\$A),1)

YY1 =OFFSET(Temperature!\$B\$5,0,0,COUNTA(Temperature!\$B :\$[img]/forums/images/smilies/cool.gif[/img],1)

YY2 =OFFSET(Temperature!\$B\$5,0,1,COUNTA(Temperature!\$B :\$[img]/forums/images/smilies/cool.gif[/img],1)

YY3 =OFFSET(Temperature!\$B\$5,0,2,COUNTA(Temperature!\$B :\$[img]/forums/images/smilies/cool.gif[/img],1)

YY4 =OFFSET(Temperature!\$B\$5,0,3,COUNTA(Temperature!\$B :\$[img]/forums/images/smilies/cool.gif[/img],1)

YY5 =OFFSET(Temperature!\$B\$5,0,4,COUNTA(Temperature!\$B :\$[img]/forums/images/smilies/cool.gif[/img],1)

I then inserted in my chart XX1 and YY1 references in the X_values and Y_values spaces, respectively.

The Chart seems to work as I want. However, everytime I do any operation in Excel, such as copy/paste, I get this error:
"The reference is not valid. References for titles, values, or sizes must be a single cell, row, or column."

Could you please tell me how I can stop this from reoccuring?

Thanks, Hanan.

4. ## Re: Graphing

Hanan,

It is not clear why you are getting an error, so maybe if you could post a sample file with dummy data, i could take a look. In the meantime, there is another possible, (actually probably better) approach using the auto filter feature. As Excel does not by default plot hidden values in a chart, the auto filter will help exploit that fact. The idea entails another column adjacent to but not forming part of the data to be plotted. In that column an IF formula could return one of two values depending on the contents of the column to be plotted, lest say Y for Data or N for Empty. You could fill that formula down as far as you think your data might extend at it's maximum. Then set the source data for your chart to the adjacent range of columns, extending the rows down as above. When the data is entered set the auto filter for the indicator column to A and the graph should just plot the visible rows. When you add data all you have to is set the filter to show all and then to show A. I attach a file with a simple column chart to demonstrate what I mean.

If that does not suit, try pasting the sample and I will see what I can do.

Andrew

5. ## Re: Graphing

Andrew, that's great. However, the graph that you have provided for me in a Column graph. I require and XY chart with one X column and five Y columns. Will your attached workbook handle that? If so, what do I need to modify in order to get this going?

Thanks, Hanan.

6. ## Re: Graphing

Hanan,

Please post a sample of the type of graph you want - no need for real data. I will see what I can set up. I am not particularly familiar with XY charts and if I were to set one up I might miss some important element.

I think , if all new data is entered in a new row, the same principle should apply.

Andrew

7. ## Re: Graphing

Hi Andrew,

Here is a sample workbook. Could you please take a look at it?

If you have a better suggestion of how to implement my chart, like the AutoFilter, I would be glad to hear it. Remember, that I will keep adding data to the columns.

Hanan.

8. ## Re: Graphing

Hi Hanan,

Attache sheet. I removed the OFFSET ranges and applied the autofilter as described before. The ranges now go from 5 to 500, e.g XX1 = A5:A500. You can change these if you want. Dont forget to extend column G at the same time.

Please let me know if it is ok. I will see why the offset method is causing problems, and if I can fix it I will let you know. It should work.

Hope this works the way you want.

Andrew

9. ## Re: Graphing

Hi,

Thanks for the changes that you have implemented. I like this method better. How can I copy this AutoFilter to my own workbook, ie, what are the steps to setup the same Drop Down list as you have done?

Thanks, Hanan.

10. ## Re: Graphing

If you are using column G, enter the IF formula, fill down as many rows ay need, and then highligt from G5: down to the last row. Go to Data, Filter, and select Autofilter. That should place a dropdown button in G5, and it should be in place.

Good luck, Andrew

11. ## Re: Graphing

Thanks for all your help Andrew. [img]/forums/images/smilies/smile.gif[/img]

12. ## Re: Graphing

If the chart is in a separate sheet, choose Location and put it onto the same sheet as the data.
Click in the chart; the data will have boxes around them just like happens when you click in the formula bar to edit a formula. You can then drag the selection down to the end. This is fairly quick even if not automatic.
Then Locate the chart back to a separate sheet as desired.
Ruth

13. ## Re: Graphing

Hi Ruth,

Could you be more specific regarding your last remark? What do you mean by dragging the selection down to the end?

Hanan.

14. ## Re: Graphing

OK, this is what I did. I put headings into B2:F2 (X, Y1, Y2, Y3, Y4 respectively). Then in B3:F24 I put the formula =randbetween(0,100), copied and pasted AsValues, then sorted on the X values. (ie data in B3:F24).

Then I selected B2:E9 (subset of the data) and chose Insert| Chart| XY | the kind with points&lines | Finish. You can put in all the other options, but I didn't bother. This puts the chart beside the data on the worksheet, rather than in another separate chart-sheet.

There are 3 lines drawn around the data. One is C2:E2 - the titles. One is B3:B9 - the X range. One is C3:E9 - the Y ranges. In the bottom right corner of each box is a little heavy square. If you move the cursor over the square that is at the bottom of the X range (ie at B9), it changes shape to a + sign. Click the + sign, hold and drag down to B12. The adjoining box that covered the data (C2:E9) automatically extends down to the end of E12 as well. When you release the cursor, the chart automatically displays those extra points.

Now move the cursor over the end of the Y ranges (now E12) and when it turns into the + sign, click and drag it to the right (to F12). The box surrounding the titles also automatically extends and when you release the cursor, the extra series is plotted.
Click anywhere outside the data on the sheet, so that the chart is no longer selected. The boxes all disappear and you have your chart as usual. Click once in the chart and the boxes come back again. You have to click on the outside border of the chart so you get the "Chart Area" selected in the "GoTo" Box. The chart then also shows the squares in the corners for re-sizing the chart.

When the chart is selected you can choose Chart | Location to put it into a separate chart sheet if you want. I usually store my charts in chart sheets rather than on the work sheet, but for things like this I temporarily re-locate them back to the worksheet.

Does this make sense?
Ruth

15. ## Re: Graphing

Thanks Ruth, your explanation makes sense [img]/forums/images/smilies/smile.gif[/img]

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•