Hessel de Walle's Blog

What to show? Thirty six charts or just one dynamic one? I created I chart which shows the choice of two parameters: cumulatives AVERAGE, MIN or MAX and months JAN through DEC. So, in this case we have thirty six sets of data we can choose from. Take your pick! I needed just one complex name: MONTH: =OFFSET(temp1!$B$2;0;MATCH(temp!$Q$2;monthnames;0)-1;temp!XER1-1705;1) The rest you can easily figure out yourself. You can download  ExcelMoving.xlsx  via: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

I used Data Validation to limit the input of cell F1 . Now I want the choice I make in cell F1 to determine which range of data is shown in my chart. First I created a name based on the range B1:D1 and called it List . Then I created three names Apples , P ears and Bananas based on the columns underneath these lables. So Apples refers to B2:B13 and so on. Then I created another name Chart based on the formula: =INDIRECT($F$1) letting it refer to the outcome of the choice I make in cell F1 , and of course indirectly to the data to which the picked name refers. Next I created a column chart based on B1:B13 . In this column chart I made a couple of changes. First I made the Series name refer to F1 . Secondly I made the Series values refer to the name Chart . And finally the Axis label range should of course refer to A2:A13 . Now you can simply choose the column you want show in your chart by takinig you pick in cell F1 ! There is one flaw. A soon as you are try...