vrijdag 31 oktober 2014

Excel: Changing Chart Data Bases On A List Using Indirect

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, Pears 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 trying to change the names Apples, Pears and Bananas into dynamic names, it is not working anymore. At least I could not get it working. Maybe someone has got the right idea.

According to this post, it is simply impossible: http://forum.chandoo.org/threads/indirect-using-dynamic-named-range.12282/

You can download the example Excelchartindirect.xlsx through:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Een reactie plaatsen