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
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
Reacties