Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for adjusting graphs every day
I have a number of reporting graphs linked into a spreadsheet which I update
on a daily basis. I then need to adjust where the graphs read to include the days data. This is tedious and time consuming with 16 graphs to adjust. Is there a macro or something I could write that would adjust the data range automatically in excel97?Adjustment could be based on a date easily enough. -- Jimmy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for adjusting graphs every day
Did you try to do it with dynamic named ranges?
which adjust themselves according to todays date? then you can tell excel, that the source data is not in a fixed range but in your named range. hth Carlo On Dec 3, 12:53 pm, Jimmy D wrote: I have a number of reporting graphs linked into a spreadsheet which I update on a daily basis. I then need to adjust where the graphs read to include the days data. This is tedious and time consuming with 16 graphs to adjust. Is there a macro or something I could write that would adjust the data range automatically in excel97?Adjustment could be based on a date easily enough. -- Jimmy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for adjusting graphs every day
Here are some examples of the technique Carlo is suggesting:
http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "carlo" wrote in message ... Did you try to do it with dynamic named ranges? which adjust themselves according to todays date? then you can tell excel, that the source data is not in a fixed range but in your named range. hth Carlo On Dec 3, 12:53 pm, Jimmy D wrote: I have a number of reporting graphs linked into a spreadsheet which I update on a daily basis. I then need to adjust where the graphs read to include the days data. This is tedious and time consuming with 16 graphs to adjust. Is there a macro or something I could write that would adjust the data range automatically in excel97?Adjustment could be based on a date easily enough. -- Jimmy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for adjusting graphs every day
had a look at the info suggested all looks good however I am still having
problems as I have more than 1 data series on the graph and all examples presume no data in the cells following on from the last entry. This is not the case for me. Some insight: I am using a copy/paste from MS project to produce 3 graph lines from the same data. 1 line is planned work. Same line also produces Work carried out (this is the one I want to adjust daily) third is forecast work (based on an amount of work predicted to arise from known work and known work still to carry out). First and third data sets are locked by pasting to separate worksheets to the second data set. -- Jimmy "Jon Peltier" wrote: Here are some examples of the technique Carlo is suggesting: http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "carlo" wrote in message ... Did you try to do it with dynamic named ranges? which adjust themselves according to todays date? then you can tell excel, that the source data is not in a fixed range but in your named range. hth Carlo On Dec 3, 12:53 pm, Jimmy D wrote: I have a number of reporting graphs linked into a spreadsheet which I update on a daily basis. I then need to adjust where the graphs read to include the days data. This is tedious and time consuming with 16 graphs to adjust. Is there a macro or something I could write that would adjust the data range automatically in excel97?Adjustment could be based on a date easily enough. -- Jimmy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for adjusting graphs every day
What I frequently suggest is that users put a copy of the data required for
charting into another range or another sheet. Use copy/paste link so the data links to the original. Then you can reconfigure the chart source data by dragging cells around until you have the arrangement you need. Worksheets are very cheap, cells are very cheap, your time trying to avoid using sheets and cells to your advantage is very costly and you will also avoid the results you desire. Spending five minutes with the data will prevent five hours of frustration. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jimmy D" wrote in message ... had a look at the info suggested all looks good however I am still having problems as I have more than 1 data series on the graph and all examples presume no data in the cells following on from the last entry. This is not the case for me. Some insight: I am using a copy/paste from MS project to produce 3 graph lines from the same data. 1 line is planned work. Same line also produces Work carried out (this is the one I want to adjust daily) third is forecast work (based on an amount of work predicted to arise from known work and known work still to carry out). First and third data sets are locked by pasting to separate worksheets to the second data set. -- Jimmy "Jon Peltier" wrote: Here are some examples of the technique Carlo is suggesting: http://peltiertech.com/Excel/Charts/...umnChart1.html http://peltiertech.com/Excel/Charts/Dynamics.html http://peltiertech.com/Excel/Charts/...hartLinks.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "carlo" wrote in message ... Did you try to do it with dynamic named ranges? which adjust themselves according to todays date? then you can tell excel, that the source data is not in a fixed range but in your named range. hth Carlo On Dec 3, 12:53 pm, Jimmy D wrote: I have a number of reporting graphs linked into a spreadsheet which I update on a daily basis. I then need to adjust where the graphs read to include the days data. This is tedious and time consuming with 16 graphs to adjust. Is there a macro or something I could write that would adjust the data range automatically in excel97?Adjustment could be based on a date easily enough. -- Jimmy |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for adjusting graphs every day
New fellow from work wrote a macro or 3 to do the job.
Sub Chart_Serie_Change() Dim shChartSheet As Worksheet Dim shDataSheet As Worksheet Dim chChart As Object Dim sSheetDataName As String Dim i As Integer Dim j As Integer Set shChartSheet = Sheet6 Set shDataSheet = Sheet7 Set chChart = shChartSheet.ChartObjects j = iColumn(shDataSheet) sSheetDataName = shDataSheet.Name If j = 1000 Then Exit Sub 'To change the chart For Each chChart In shChartSheet.ChartObjects i = iRow(shDataSheet, chChart) If i = 1000 Then Exit Sub shChartSheet.Activate chChart.Activate chChart.Chart.SeriesCollection(2).Values = "='" & sSheetDataName & "'!R" & i & "C3:R" & i & "C" & j Next chChart MsgBox ("Charts have been updated") End Sub 'To find the column the data needs to extend to Function iColumn(shSheet As Worksheet) As Integer iColumn = 3 With shSheet Do While Year(.Range("A1").Cells(1, iColumn).Value) < Year(Now()) _ Or Month(.Range("A1").Cells(1, iColumn).Value) < Month(Now()) _ Or Day(.Range("A1").Cells(1, iColumn).Value) < Day(Now()) iColumn = iColumn + 1 If iColumn 250 Then MsgBox ("Please, Check date in data source sheet") iColumn = 1000 Exit Function End If Loop End With iColumn = iColumn - 1 End Function 'To find the row the chart refers to Function iRow(shSheet As Worksheet, chChart_Obj As Object) As Integer 'I needed to start at row 6 iRow = 6 Dim sName As String sName = chChart_Obj.Name With shSheet Do While .Range("B" & iRow).Value < sName iRow = iRow + 7 If iRow 120 Then MsgBox ("Please, check chart names in data source sheet") iRow = 1000 Exit Function End If Loop End With End Function It seems to work fairly well. -- Jimmy "Jimmy D" wrote: I have a number of reporting graphs linked into a spreadsheet which I update on a daily basis. I then need to adjust where the graphs read to include the days data. This is tedious and time consuming with 16 graphs to adjust. Is there a macro or something I could write that would adjust the data range automatically in excel97?Adjustment could be based on a date easily enough. -- Jimmy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Graphs & Macros | Excel Discussion (Misc queries) | |||
Adjusting series | Charts and Charting in Excel | |||
Adjusting the y axes on 2-axes line graphs | Charts and Charting in Excel | |||
Automatic Graphs/ Dynamic Graphs | Charts and Charting in Excel | |||
Excel Charts(Graphs) & Macros | Excel Discussion (Misc queries) |