Creating a dynamic chart accoding to table

Dynamic Charts are built on named dynamic ranges.

These two articles will help you with both:
http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

What you want to plot on the Y axis is fixed. It is the range A3:A33. The trick is to find out which zone to plot. This is done by finding out how many columns to offset from column A.

On the chart sheet, I have a data validation assigned to cell B1, where you select the zone.

Cell 1 is a bit tricky. It uses the seldom-seen LOOKUP command which works a bit like VLOOKUP except everything is contained in the formula. =LOOKUP(B1,{"ZONA 1","ZONA 2","ZONA 3","ZONA 4"},{1,3,5,7})

This formula looks at the value in B1 and then looks for its position in the list of zones (for example ZONA 2 is in the second position). It then returns the corresponding value from the following list {1,3,5,7}.

Cell 1is used to determine how many columns to go over to find the column to plot for the first piece of data. The second thing we want to plot is one more column to the right.

So this gives us our three Named Ranges to Plot

Plot_1 =OFFSET(Plot_data,0,Chart!$C$1)
Plot_2 =OFFSET(Plot_data,0,Chart!$C$1+1)
Plot_data =Sheet1!$A$3:$A$33

Plot_Data is fixed and the other two are defined by how many columns to the right to go to get the series to plot.