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

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

Data: A1:D12 filled with jan-dec: jan jan jan jan feb feb feb feb mar mar mar mar apr apr apr apr may may may may jun jun jun jun jul jul jul jul aug aug aug aug sep sep sep sep oct oct oct oct nov nov nov nov dec dec dec dec Example A AllText =A1:D12 &"" Adding &"" changes the whole name into one continuous array, reading from left to right and from top to bottom. We can use this in a graph (see Example A). We can not read the elements using the INDEX function like this: =INDEX(AllText;13) The INDEX does not see this as a continuous array. We still could use: =INDEX( AllText ;4;3) But there is nothing special about that. Example B ColumnText1 =...