Hessel de Walle's Blog

The question is how can somebody just pick the graph he/she wants to see without seeing the other graphs? Nice to have for your Excel dashboard. Something like this: How did I do this? I created one sheet called graphs with three different graphs. Then I created three names One , Two and Three referring to a range containing the graphs One , Two and Three respectively. Like this: one = graphs!$G$1:$O$16 two = graphs!$G$17:$O$33 three = graphs!$G$33:$O$48 On a second sheet called whichgraphtoshow  in cell A2 I then created a picking list containing one , two and three . Yet another name refers to this cell: graphLookup =INDIRECT(whichgraphtoshow!$A$2) On the same sheet I then inserted a picture. The picture refers to the name graphLookup .  And it is all working! You can download  ExcelWhichGraphToShow.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...

In Excel you can validate a cell through Data  =>  Data Validation . We then will get this window: At  Allow  we then can choose List and then at  Source  we pick the range where the data are . Want we want to do here is select a continent first and then automatically restrict the choice in the next cel to countries from the specific continent. In the example we see the data (in Dutch) which we are going to use in our example, A1:E6 : afrika amerika australië azië europa nigeria brazilië nieuw zeeland china nederland boeroendi argentinië australië japan belgië liberia equador nieuw guinea thailand polen sierra leone peru pakistan duitsland mauretanië bolivia india engeland We will use names here to make the stepped choices work. We select  A1:E6 . We then choose the tab  Formulas  and  then Create From Selection . We'll get: Here we check the upper box only. Then we click OK . We now created five name...

In Excel kun je een cel valideren via de tab Gegevens => gegevensvalidatie . We krijgen dan dit dialoogvenster: Bij Toestaan kunnen we dan kiezen voor Lijst en vervolgens bij Bron voor het bereik waarin de data staan. Bij een getrapte keuze kiezen we bijvoorbeeld in een eerste cel het werelddeel en in de cel ernaast willen we dan alleen landen zien die bij het betreffende werelddeel horen. In het onderstaande voorbeeld geven we de data die we in ons voorbeeld gaan gebruiken, van A1:E6 : afrika amerika australië azië europa nigeria brazilië nieuw zeeland china nederland boeroendi argentinië australië japan belgië liberia equador nieuw guinea thailand polen sierra leone peru pakistan duitsland mauretanië bolivia india engeland We gaan hier namen gebruiken om de getrapte keuze te laten werken. We selecteren A1:E6 . We kiezen dan de tab Formules en ve...