I have created a chart that provides a hisotry of events on a timeline. It relies on named ranges and allows me to change the upper and lower date ranges displayed on the x-axis of the chart by changing the date in cells that define the upper and lower dates. In order to display the events on the timeline I have a table that includes 3 columns... a date colmn, an event description column, and a event height column. To display the information I have used a line chart with data points that uses the "event height" data to determine how high up on the graph to display the event information. I have hidden the line, added a label for each event, and added an error bar that draws a line between each label and the bottom of the chart. By default the labels displayed the "event height" information (as expected), so I selected each label individually and added a formula that causes it to display information from the "event description" column instead of the default value. All of this works perfectly with no issues.
The problem comes when I change the date range displayed in the chart. Currently the chart displays event information for 1/1/2012 - 5/30/2012. I can modify 5/30/2012 to be any date I would like that is greater than 1/1/2012 with no problem. Everything displays correctly. However, if I modify the 1/1/2012 date the labels change to reflect the original value (event height data) instead of what I modified it to (event description). I haven't been able to figure out how to correct this and could use help.
A sample spreadsheet can be found here:
http://sdrv.ms/LPwNkR<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The problem comes when I change the date range displayed in the chart. Currently the chart displays event information for 1/1/2012 - 5/30/2012. I can modify 5/30/2012 to be any date I would like that is greater than 1/1/2012 with no problem. Everything displays correctly. However, if I modify the 1/1/2012 date the labels change to reflect the original value (event height data) instead of what I modified it to (event description). I haven't been able to figure out how to correct this and could use help.
A sample spreadsheet can be found here:
http://sdrv.ms/LPwNkR<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>