Using Next/Back buttons to change Chart data source

Hello everyone,

Long time lurker, finally posting. This forum is great. Everyone gives excellent advice, so I'm hoping for more of the same.

Background:
I need to graph three different years of data on the same chart so all three years can be compared simultaneously. Example is below:
chart.jpg

Desired outcome:
I would like to be able to hit “Next” and “Back” buttons to navigate between the different “Event Types”. The chart will update automatically to show the “Event Type” data for all three years.
chart_buttons.jpg

In the example below, the current event type being displayed for all three years is "Concerts", when I hit NEXT I would like the data for "Fairs" for all three years to populate in the chart.
chartdata.jpg

Problems:
I have tried using OFFSET, but I cannot get it to increase or decrease incrementally with each “Next” or “Back” click.

I have tried recording a macro for the “Next” and “Back” buttons, but once again, moving forward or backwards incrementally has been difficult. Example: If the current event is “Concerts”, I hit NEXT and the “Fairs” data loads, but if I hit NEXT again(to load the “Special Events” data), nothing happens.

Dynamic ranges. I would use this approach if I only had 3 Event Types to graph, but in reality I have a little under 200 event types to graph. I only included 3 in the example.

Any help would be greatly appreciated.

Thanks in advance

Enjoy the weekend everyone!

maybe validation list in a cell with events?

Originally Posted by tim201110

maybe validation list in a cell with events?

tim201110 you are a genius!

I used the INDEX function for each of the three separate series(Events, Fairs, etc.), but made each one reference their own dataset. Each INDEX function pointed to the same "switch" cell.

So whenever I changed the switch from "1" to "2" to "3", etc. all three data sets updated automatically.

The graph part was easy, I simply made the graph reference the area where I put the INDEX formula, so each time the switch changed, the graph would use the updated dataset.

For the “forward” and “back” buttons I just made a macro that increases or decreases the value of the “switch” cell by one.

This is exactly what I was looking for. Nice work!