Chart with scrollbar that offsets the period. Can it have event as well?

**c.vaibhav** · 11-09-2011, 06:27 AM

Hi friends,

I have data that ranges from year 2000 to 2011. It cannot be shown in one display. Hence with help of Andy Pope's chart website I am able to put a scroll bar with help of which user can scroll through the period.

My requirement: There are events associated with some of the periods. Can it also be displayed while the user is scrolling through the data. For example: there is an event in 2005. While user is scrolling through 2003 to 2004 to 2005, the excel chart should display the necessary events.

Can it be done without the use of VBA macros?

Thanks,
Vaibhav

**Andy Pope** · 11-09-2011, 11:09 AM

Can you not add another series? Use the same approach but with more named ranges.

**c.vaibhav** · 11-09-2011, 02:39 PM

Thanks for your reply.

I have added the data series. But I am not able to get the text on the Chart. The events are basically a text description of 6-7 words. When I try to put that on the chart it comes as 0. Basically not able to format the data series. Will try to do it on own, I think I am very close now. Though if you have a quick fix, please do share.

Thanks,
Vaibhav

**Andy Pope** · 11-10-2011, 06:07 AM

Can you post what you have so far?

**c.vaibhav** · 11-10-2011, 03:10 PM

I have attached the excel File. It has another Column "Event". I have filled up blank data with 0.2 (Dummy figure) so that you can see the line on the chart. For events, it shows 0 because excel charts cannot read text.

I have added another namedrange - SCROLL_EVENTDATA

Thanks very much for your help.

Regards,
Vaibhav

**Andy Pope** · 11-11-2011, 07:23 AM

Add a column for the event description. Use the next column to provide a data value of 1 or NA(). For each valid data point show the data label with category text.

Use 2 named ranges for the event labels and data values.

I also added dropdown lines to the event series.

**c.vaibhav** · 11-11-2011, 10:30 AM

Superb. Thank you very very much.

I was trying to achieve in the same manner. The mistake I was doing was, before adding secondary axis, I was trying to change the Data Labels (Horizontal axis labels). So it was getting changed for both the series. Dumb, I know.

Thank you very much.