Remove old month from chart add new/no manual reference change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

jan feb mar apr
10 20 30 40

I want to graph the latest 4 months without having to go in a change
refrences in the chart each month.

Next month
feb mar apr may
20 30 40 50

If I drag the last months across to pick up the formulas for the next month
I get 5 months on the graph instead of 4
 
Hi Kameel
Its possible only.

Example:
First Row months (A1: Jan, B1: Feb,…..)
Second Row values (A2: 10, B2: 20,…..)
Put any column (eg. K10) this function:
=ADDRESS(1,COUNTA(A1:AA1)-3)&":"&ADDRESS(2,COUNTA(A1:AA1))
You will get the cell address of new four months.
Click Insert–>Name–>Define
Enter “CName†in ‘Names in workbook’ field
Refers to area: =INDIRECT(Sheet1!$K$10)
Then click OK.
Open Visual Basic Editor (Alt+F11)
Go particular sheet object area
Paste below mentioned code

‘VB Code
Dim tmpBC As String
Private Sub Worksheet_Activate()
tmpBC = Range("K10").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If tmpBC <> Range("K10").Value Then
Call Refesh_Chart
End If
End Sub


Private Sub Refesh_Chart()
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 4").Activate 'put your chart name
ActiveChart.ChartArea.Select
Dim rng As Range
Set rng = Range("Cname")
ActiveChart.SetSourceData Source:=rng, PlotBy:= _
xlRows
Range("A1").Select
Application.ScreenUpdating = True
End Sub

If you are not able to understand please mail to (e-mail address removed) with
sample file

Thanks and regards
Muhammed Rafeek M
 
jan feb mar apr
10 20 30 40

I want to graph the latest 4 months without having to go in a change
refrences in the chart each month.

Next month
feb mar apr may
20 30 40 50

If I drag the last months across to pick up the formulas for the next month
I get 5 months on the graph instead of 4
Click to expand...

See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
particularly, example 2.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top