Forum Discussion
How to maintain chart formatting when altering the data range each month?
Hello,
what do you mean with "if not in consecutive cells"? Do you want to plot from a start month, then leave a gap?
When you select the chart, the data is highlighted. You can drag the sizing handles of the highlights to change what data is charted.
Hello,
what do you mean with "if not in consecutive cells"? Do you want to plot from a start month, then leave a gap?
When you select the chart, the data is highlighted. You can drag the sizing handles of the highlights to change what data is charted.
That's perfect - thanks so much!
Just to play with this - bit more dynamic variant using known pattern with OFFSET.
1) Assuming end of the rolling year shifted back from last date in the table on number of months defdined in D6
2) Name this cell as Shift
3) In Name Manager add definitions for our dates (X) axis and series as following
OFFSET defines the range for each of above
- starting from cell $B$7
- on 12 rows back from last date plus Shift
- which column to take (-1 = dates, 0 = Total Encounters, 1= next column # of times, etc.)
- 12 is height of the range (12 months)
- 1 is the width of the range (one column)
4) Select Data for the chart
and edit each of series and axis substituting cells range on defined before names, for example for Total Encounters
Before:
After:
Now chart shifts automatically if you add/remove date and/or change Shift cell value. File is attached
