DKRbella0814
Board Regular
- Joined
- Aug 10, 2008
- Messages
- 155
Can someone help me through the steps of programming excel to have the range of a chart automatically update?
My data is set up as follows:
col D = Date Range (in the format of 12/30/10-12/31/10); begins at D4
col E = Day# (0-260; # of work days during the calendar year); begins E4
col F = Planned vs Actual (Cycles) (in %); Begins F4
col G = Planned vs Actual (Pieces) (in %); Begins G4
Essentially, data is collected on a daily basis and captures production activity over three manufacturing shifts. Since our 3rd shift runs overnight, each day's data will have a date range of the previous day and current day (col D). Columns F & G are the critical pieces of data being graphed.
If possible, I would like to graph cols F & G as Series 1 and Series 2 on a double bar chart graph. As a new piece of data is entered each day (cols F& G), I want the range of the chart to expand to include the most recent entry.
Otherwise, I would have to define the chart range as the first date the data was collected up until Dec 31, 2011; This would make the chart quite large and would show a lot of empty space since no data has been entered that far into the year.
I found some basic tutorials on how to define the range and I have completed the following:
Insert - Name - Define
Cycles: =OFFSET(Graph!$F$4,0,0,COUNTA(Graph!$F:$F)-3)
Parts: =OFFSET(Graph!$G$4,0,0,COUNTA(Graph!$G:$G)-3)
From there, I am confused of how to update the series in the graph wizard.
I created a basic double bar (vertical) graph, with planned vs actual (cycles) = series 1 and planned vs actual (parts) = series 2.
Can someone walk me through the rest of this? I am working with Excel 2003.
Thanks for the help!
My data is set up as follows:
col D = Date Range (in the format of 12/30/10-12/31/10); begins at D4
col E = Day# (0-260; # of work days during the calendar year); begins E4
col F = Planned vs Actual (Cycles) (in %); Begins F4
col G = Planned vs Actual (Pieces) (in %); Begins G4
Essentially, data is collected on a daily basis and captures production activity over three manufacturing shifts. Since our 3rd shift runs overnight, each day's data will have a date range of the previous day and current day (col D). Columns F & G are the critical pieces of data being graphed.
If possible, I would like to graph cols F & G as Series 1 and Series 2 on a double bar chart graph. As a new piece of data is entered each day (cols F& G), I want the range of the chart to expand to include the most recent entry.
Otherwise, I would have to define the chart range as the first date the data was collected up until Dec 31, 2011; This would make the chart quite large and would show a lot of empty space since no data has been entered that far into the year.
I found some basic tutorials on how to define the range and I have completed the following:
Insert - Name - Define
Cycles: =OFFSET(Graph!$F$4,0,0,COUNTA(Graph!$F:$F)-3)
Parts: =OFFSET(Graph!$G$4,0,0,COUNTA(Graph!$G:$G)-3)
From there, I am confused of how to update the series in the graph wizard.
I created a basic double bar (vertical) graph, with planned vs actual (cycles) = series 1 and planned vs actual (parts) = series 2.
Can someone walk me through the rest of this? I am working with Excel 2003.
Thanks for the help!