MS Excel question re: changing range on chart from a cell

Factual Questions
1

Is it possible to change the range of a chart from a cell on a sheet? For example, I have the following data:

A	B

1 2/1 413.1
2 2/2 8.5
3 2/3 19.8
4 2/4 99.5

Say the chart was reading =Sheet1!$A$1:$B$3 but later on I wanted to change the range to =Sheet1!$A$2:$B$4 without going into the Chart Options but instead have it done from cell C1 where C1 would have the data for the range for the chart to read from?

2

Anyone?

3

It took me a while to figure out exactly what you’re asking.

One of my coworkers is our excel whiz. I’ll ask him when he comes back from lunch. I can’t think of a way off hand.

4

Anyone can help me with this?

5

You want to be able to change the range of the data in the chart from a cell on the worksheet?

I don’t know of a way offhand that can be done, but I have to confess it never occured to me to try. Some bigger Excel charting whiz than me is going to have to check in.

6

I haven’t got it totally figured out, but what about referencing some intermediate cells instead of the original data?

You may not be able to change the reference of the chart without going into its options. But you might be able to point the chart at a new range of cells that you create. In this range of cells you have references to to original data set. You can change those references as you wish without leaving the spreadsheet. Maybe you want to reference the first 7 rows, or the last 7 rows. To change to either one, you just change the formula in the new cells.

There may be some elegant, ingenious ways of doing that, or you could brute force it. But that way, you never mess with your original data.

7

I tried that too, didn’t work for me, and… I never did get an answer from that coworker…

I’ll ask again tomorrow, kasuo.

8

Have a look here

9

Is it me, or does that seem like an awful lot of trouble when you can just right click and change source data? :wink: I mean unless you’re changing the data range 400 times a day…

10

Its easy. Suppose you want to use cell D1 to show the start of your desired range, and E1 to show the end.

Create the following macro :

**
Sub ChangeRange()
’
’ Macro1 Macro
’ Macro created 25/03/2004 by Peter
’

’
my_new_range = Range(“d1”).Value & “:” & Range(“e1”).Value
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets(“Sheet1”).Range(my_new_range), PlotBy:= _
xlColumns
End Sub
**

Right click on your chart, and select “assign macro.” Select ChangeRange and press OK. Now, to change the range of the chart, you just edit cells d1 & e1, then click on the chart and it will update.

HTH.