Issue with dates on vert axis w/ multiple time frames mapped in same chart

I have built a chart that shows data for three time periods: last 7 days, last 30 days and last 8 quarters. I use a list-box to allow users to select which date range. The cell value returned by the list box triggers some logic that pulls in the correct data to my source data cells.

The problem I'm having is with the date ranges that are displayed along the vertical axis. The dates are correct for the last 7 days and last 30 days but for the last 8 quarters it shows a tick mark for every month even though there are only 8 dates available in the date row of my source data.

Example, in my source data, the dates listed (in consecutive cells) are 1/1/05, 4/1/05, 7/1/05, etc. But when they get displayed in the chart, Excel is adding tick marks for all the intervening months (2/1/05, 3/1/05, 5/1/05, 6/1/05, etc).

It's not a big deal but this makes the chart area look unnecessarily cluttered and harder to read; it also makes it look like there are 24 data points (8 quarters X 3 mos. per) when in fact there are only the 8 quarterly data points.

As for Format Axis, everything is set to auto. I cannot use fixed values because sometimes I need days and sometimes I need months. Any ideas on how I can force Excel to not add the tick marks for the intervening months?

Hi bradams

You can use the worksheet Change Event to monitor the value of the cell with the dropdown. It can set the MajorUnit of the Y-Axis to Auto in the case of the 7 and 30 days but change it to a fixed value (for ex. 3) in the case of Quarters.

HTH
lecxe

I read your reply as soon as it was posted but didn't get around to posting a proper thank you. So thank you for taking the time to provide me the answer. Very much appreciated. Maybe one day I'll be in position to return the favor.